How to update rows in my database with rows from dataGridView C#

Question

How to update rows in my database with rows from dataGridView1?

It shows 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,...)

this is the code that I have so far:

        private void button2_Click(object sender, EventArgs e)
    {
        foreach ( DataGridViewRow dr in dataGridView1.Rows)
        {
            string constring = "Data Source = localhost; port = 3306; username = root; password = 0159";
            string Query = " Update TopShineDB.Table1 (Time, CarColorNumber, Interior, Exterior, CPlastic, MPlastic, SPlastic, PlasticB, WashExt, WashEng, WashTrunk, WashSeats, SeatsRmv, SeatsFit, Notes) VALUES ('" + dr.Cells[0] + "','" + dr.Cells[1] + "','" + dr.Cells[2] + "','" + dr.Cells[3] + "','" + dr.Cells[4] + "','" + dr.Cells[5] + "','" + dr.Cells[6] + "','" + dr.Cells[7] + "','" + dr.Cells[8] + "','" + dr.Cells[9] + "','" + dr.Cells[10] + "','" + dr.Cells[11] + "','" + dr.Cells[12] + "','" + dr.Cells[13] + "','" + dr.Cells[14] + "')";
            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);
            }
        }
    }

thanks for the help.

What error exactly? can you add the error to the question please? — scartag
– scartag, Commented Jun 19, 2016 at 13:26
Consider learning how data binding works. If you are plucking data out of a UI element you are doing too much work. If you data bound correctly and had a data adapter, you could just call Update() instead of the complex and injection prone way you are getting your data. — Crowcoder
– Crowcoder, Commented Jun 19, 2016 at 14:20

Erick Kamamba · Accepted Answer · 2016-06-19 13:47:35Z

3

string Query = " Update TopShineDB.Table1 (Time, CarColorNumber, Interior, Exterior, CPlastic, MPlastic, SPlastic, PlasticB, WashExt, WashEng, WashTrunk, WashSeats, SeatsRmv, SeatsFit, Notes) VALUES ('" + dr.Cells[0] + "','" + dr.Cells[1] + "','" + dr.Cells[2] + "','" + dr.Cells[3] + "','" + dr.Cells[4] + "','" + dr.Cells[5] + "','" + dr.Cells[6] + "','" + dr.Cells[7] + "','" + dr.Cells[8] + "','" + dr.Cells[9] + "','" + dr.Cells[10] + "','" + dr.Cells[11] + "','" + dr.Cells[12] + "','" + dr.Cells[13] + "','" + dr.Cells[14] + "')";

The SQL above is not a valid update statement.. The correct UPDATE syntax should be:-

UPDATE TopShineDB.Table1 
SET Time = '" + dr.Cells[0] + "', 
Notes = '"+ dr.Cells[14] + "' 
WHERE Table1ID = ID from the grid

Let me know is that doesn't work

answered Jun 19, 2016 at 13:47

Erick Kamamba

2681 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tarek-Dev Over a year ago

may I know what you mean by "ID from the grid" ?

Tarek-Dev Over a year ago

and just to clarify, it should be written like this? "Update TopShineDB.Table1 SET Time = '" + dr.Cells[0] + "', ........... , Notes = '"+ dr.Cells[14] + "' WHERE Table1ID = ID from the grid "

Collectives™ on Stack Overflow

How to update rows in my database with rows from dataGridView C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related