Inserting a new text under a column appears as 0 but appears correctly in the database browser. (SQL-CodePudding

I have a windowsform application where the user can store Names, Phone Numbers and Grades of the student. The Names column only stores strings, however the two columns Phone Numbers and Grades only store ints.

I have added a button called "Add student". It's meant to be used when the user has finished filling the three textboxes "Name, Phone Number and Grade" and it will store them in a SQLite database and the data would appear in a datagridview.

My issue is that when the button is pressed, all of the columns are displayed correctly except for the column "Name". In the datagridview, it displays all Names that have been inputted by the user as "0", however when viewed in the database using DB Browser for SQLite, the names are displayed correctly.

My code:

private void RefreshData()
        {
            Datagridview1.Rows.Clear();
            using SQLiteConnection SQLConnection = new(DBpath);
            using var cmd = new SQLiteCommand(DBpath, SQLConnection);
            SQLConnection.Open();
            cmd.CommandText = "SELECT * FROM Students";
            using (SQLiteDataReader read = cmd.ExecuteReader())
            {
                while (read.Read())
                { 
                     Datagridview1.Rows.Add(new object[] {
                     read.GetValue(0),
                     read.GetValue(1),
                     read.GetValue(2)
                     });
                }
                read.Close();
            }
            SQLConnection.Close();
        }


 public void WriteToTable()
            {
                string? SName = TXT_Name.Text;
                int? SPhone = Convert.ToInt32(TXT_Phone.Text);
                string SGrade = COMBO_Grade.Text;
                using SQLiteConnection SQLConnection = new(DBpath);
                using var cmd = new SQLiteCommand(DBpath, SQLConnection);
                SQLConnection.Open();
                cmd.CommandText = @"INSERT INTO Students(Name, Phone, Grade) VALUES ('"   SName   "', "   SPhone   ", '"   SGrade   "')";
                cmd.ExecuteNonQuery();
                SQLConnection.Close();
            }


 private void BTN_Add_Click(object sender, EventArgs e)
        {
            WriteToTable();
            RefreshData();
        }

Gif to further demonstrate

I've tried checking the datagridview properties but they were all correct and I made sure no unnecessary SQLite commands were being ran.

CodePudding user response：

Your RefreshData method does not care for any data types. GetValue() returns an int. What you want is a string. Since you didn't tell it the actual type of the object and it originally is a string, it's being treated as 0 (default int value).

What you want to do:

...
while (read.Read()) {
  // Use the appropriate method to retrieve each value
  string name  = read.GetString(0);
  int    phone = read.GetInt32(1); // Consider also making this a string
  int    grade = read.GetInt32(2);

  Datagridview1.Rows.Add(new object[]{name, phone, grade});
}
...

I've also noticed your table is lacking a proper ID. You should make sure to include at least one unique identifier. (A phone number might not be sufficient)