Updating database table from DataGridView in C#

Question

I am trying to update my database rows from the DataGridView using this code:

private void button2_Click(object sender, EventArgs e)
 {
     foreach (GridViewRow dr in dataGridView1.Rows)
     {
         string constring = "Data Source = localhost; port = 3306; username = root; password = 0159";
         string Query = "Update TopShineDB.Table1 set Time = '" + dr.Cells[0].Text + "', CarColorNumber = '" + dr.Cells[1].Text + "', Interior = '" + dr.Cells[2].Text + "', Exterior = '" + dr.Cells[3].Text + "', CPlastic = '" + dr.Cells[4].Text + "', MPlastic = '" + dr.Cells[5].Text + "', SPlastic = '" + dr.Cells[6].Text + "', PlasticB = '" + dr.Cells[7].Text + "', WashExt = '" + dr.Cells[8].Text + "', WashEng = '" + dr.Cells[9].Text + "', WashTrunk = '" + dr.Cells[10].Text + "', WashSeats = '" + dr.Cells[11].Text + "', SeatsRmv = '" + dr.Cells[12].Text + "', SeatsFit = '" + dr.Cells[13].Text + "', Notes = '" + dr.Cells[14].Text + "', where Time = '" + dr.Cells[0].Text + "' ;";  
         MySqlConnection conn = new MySqlConnection(constring);
         MySqlCommand command = new MySqlCommand(Query, conn);
         MySqlDataReader myReader;

         try
         {
             conn.Open();
             myReader = command.ExecuteReader();
             MessageBox.Show("Table Successfully Updated");
             while (myReader.Read())
             {

             }
         }
         catch (Exception ex)
         {
              MessageBox.Show(ex.Message);
         }
     }
 }

But I ended up getting this error:

{"Unable to cast object of type 'System.Windows.Forms.DataGridViewRow' to type 'System.Web.UI.WebControls.GridViewRow'."}

maybe this helps...replace the foreach line with this: `foreach(System.Web.UI.WebControls.GridViewRow dr in dataGridView1.Rows)` — Raktim Biswas, Jun 19 '16 at 16:23
A `DataGridView` doesnt contain `GridViewRows`. The error message tells you that you are mixing object from 2 totally different namespaces — Ňɏssa Pøngjǣrdenlarp, Jun 19 '16 at 16:40
@Plutonix I only did this to try and get the text from the dataGridView table. but I understand now my error. also do you have any idea how to get the text from the dataGridView ? — Tarek-Dev, Jun 19 '16 at 16:46
How did the data get into the DGV? Did you populate it manually? One of the best aspects of the DGV is that when bound you can use it to show what is in a DataTable. Updates etc are then handled thru a DataTable (where the data really is). Edits to the DGV will flow to the DT automatically. Also, never ever concat string to make SQL - use Parameters. There are tens of thousands of posts here on all of this. — Ňɏssa Pøngjǣrdenlarp, Jun 19 '16 at 16:53

DoLoop · Answer 1 · 2016-06-19T16:23:30.897

You are doing DataReader and trying to update. To update you need to executeNonQuery. So instead of:

myReader = command.ExecuteReader();

You need

cmd.ExecuteNonQuery();

Here is how your code under foreach loop need to looks like

string constring = "Data Source = localhost; port = 3306; username = root; password = 0159";
string Query = "Update TopShineDB.Table1 set Time = '" + dr.Cells[0].Text + "', CarColorNumber = '" + dr.Cells[1].Text + "', Interior = '" + dr.Cells[2].Text + "', Exterior = '" + dr.Cells[3].Text + "', CPlastic = '" + dr.Cells[4].Text + "', MPlastic = '" + dr.Cells[5].Text + "', SPlastic = '" + dr.Cells[6].Text + "', PlasticB = '" + dr.Cells[7].Text + "', WashExt = '" + dr.Cells[8].Text + "', WashEng = '" + dr.Cells[9].Text + "', WashTrunk = '" + dr.Cells[10].Text + "', WashSeats = '" + dr.Cells[11].Text + "', SeatsRmv = '" + dr.Cells[12].Text + "', SeatsFit = '" + dr.Cells[13].Text + "', Notes = '" + dr.Cells[14].Text + "', where Time = '" + dr.Cells[0].Text + "' ;";
MySqlConnection conn = new MySqlConnection(constring);
MySqlCommand command = new MySqlCommand(Query, conn);
                                    try
                        {
                            con.Open();
                            command.ExecuteNonQuery();
                            con.Close();
                        }

                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.ToString());
                        }

I still got the same error. In fact it shows me the error in the gridViewRow dr part. — Tarek-Dev, Jun 19 '16 at 16:11
{"Unable to cast object of type 'System.Windows.Forms.DataGridViewRow' to type 'System.Web.UI.WebControls.GridViewRow'."} — Tarek-Dev, Jun 19 '16 at 16:22
yeah I made sure its the same but I got the same error : An unhandled exception of type 'System.InvalidCastException' occurred in Top Shine.exe — Tarek-Dev, Jun 19 '16 at 16:37
try in foreach loop to define System.Web.UI.WebControls.GridViewRow dr in dataGridView1.Rows — DoLoop, Jun 19 '16 at 16:50

meJustAndrew · Accepted Answer · 2016-06-19T21:44:25.253

0

It's foreach(DataGridViewRow... not foreach(GridViewRow... As in msdn documentation .Rows property :

Gets an array of DataGridViewRow objects.

In order for code to compile with this change, you will need to use .Value instead of .Text when you access cells values

edited Jun 19 '16 at 21:44

answered Jun 19 '16 at 20:58

meJustAndrew

6,011
8
50
76

Yeah I tried that, but the problem is I can't seem to get the text from the cell, like I can't type dataGridView1.Rows.Cells.Text but I can get the text from the gridViewRow. Can you please explain it code? And thank you for the answer. – Tarek-Dev Jun 19 '16 at 21:26
Well, in he code you have written, you will have a compilation error at foreach. But if you replace the datatype from GridViewRow in DataGridViewRow , the code should compile welll. – meJustAndrew Jun 19 '16 at 21:30
I get what you mean, but when I change it to dataGridViewRow it shows an error under .Text how can I fix that? I tried changing it into .value but it didn't work. – Tarek-Dev Jun 19 '16 at 21:32
Use .Value instead of .Text – meJustAndrew Jun 19 '16 at 21:37
Yeah when I do that I get this error: you have an error in your sql syntax check the manual that corresponds to your mysql server version for the right syntax to use near '(Time, CarColorNumber, Interior, Exterior, CPlastic,...) – Tarek-Dev Jun 19 '16 at 22:09
Is this at runtime? – meJustAndrew Jun 19 '16 at 22:12
Well I can see you have used the code I suggested, and I guess my answer is the right one for your question, this looks to me as a database problem, from now on, so please don't forget to mark this as answer. Cheers! – meJustAndrew Jun 19 '16 at 22:26
Sorry I just got a bit distracted, thank you for the help :) – Tarek-Dev Jun 19 '16 at 22:28
You are welcome, and thank you too! I am sorry I am still not so good on databases so I could help you again.. – meJustAndrew Jun 19 '16 at 22:30

Updating database table from DataGridView in C#

2 Answers2