Updating Database with DataGrid C#

Question

I have a datagrid setup in my windows form in Visual Studio. The datagrid is updated from the textboxes but I can't get it to edit the values held in the database.

This is the code I am using:

private void btnUpdate_Click(object sender, EventArgs e)
    {
        string constring = "datasource=localhost;port=3306;username=root;password=admin";
        string Query = "UPDATE database.taxi SET PickupLocation='" + txtPickupLocation.Text + "',PickupArea='" + comboBxPickupArea.Text + "',PickupTime='" + dateTimePickup.Text + "',DestinationLocation'" + txtDestinationLocation.Text + "',DestinationArea='" + comboBxDestinationArea.Text + "',Name'" + txtCustomerName.Text + "',Address='" + txtCustomerAddress.Text + "',Tour='" + comboBxTour.Text + "',VehicleRegistration='" + txtvehicleregistration.Text + "' ;";
        MySqlConnection conDataBase = new MySqlConnection(constring);
        MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);
        MySqlDataReader myReader;
        try
        {
            conDataBase.Open();
            myReader = cmdDataBase.ExecuteReader();
            MessageBox.Show("Entry has been updated");
            while (myReader.Read())
            {



            }


        }

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

But I get the error:

"You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near '"DestinationLocation'"......... "

Any help would be appreciated.

Soner Gönül · Accepted Answer · 2013-12-08 19:27:10Z

1

You forget to use = after your DestinationLocation and Name

Change your

DestinationLocation'" + txtDestinationLocation.Text

and

Name'" + txtCustomerName.Text + "'

to

DestinationLocation = '" + txtDestinationLocation.Text

and

Name = '" + txtCustomerName.Text + "'

But please don't use string concatenation in your sql queries. Use parameterized queries instead. This kind of string concatenations are open for SQL Injection attacks.

Also you don't need to use ExecuteReader since your query doesn't return anything. Use ExecuteNonQuery instead.

As a full code;

string Query = "UPDATE database.taxi SET PickupLocation=@PickupLocation, PickupArea=@PickupArea, PickupTime=@PickupTime, DestinationLocation=@DestinationLocation,
                DestinationArea=@DestinationArea, Name=@Name, Address@Address, Tour=@Tour, VehicleRegistration=@VehicleRegistration";
MySqlConnection conDataBase = new MySqlConnection(constring);
MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);
cmdDataBase.Parameters.AddWithValue("@PickupLocation", txtPickupLocation.Text);
cmdDataBase.Parameters.AddWithValue("@PickupArea", comboBxPickupArea.Text);
....
....
cmdDataBase.ExecuteNonQuery();

edited Dec 8, 2013 at 19:27

answered Dec 8, 2013 at 19:15

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

2 Comments

user3080562 Over a year ago

That is excellent, thank you so much for your help. Just one more thing, it is updating all the entries in the database now. How can I make it so it only updates the one row?

Soner Gönül Over a year ago

@user3080562 You are quite welcome. Please consider to accept as an answer. meta.stackexchange.com/questions/5234/… If you only update one row, you should consider to use WHERE statement in your UPDATE query. For example, ...WHERE Name = "blabla" , updates your row only where your row Name is blabla

Ann L. · Accepted Answer · 2013-12-08 19:15:46Z

1

You need an equals sign after DestinationLocation in your SQL.

Incidentally, you probably don't want to use ExecuteReader, since you're not returning any values (and aren't interested in any.) Try ExecuteNonQuery.

ETA: and Soner Gönül is absolutely right about the need for parameterized queries rather than string concatenation!

Finally, I assume that you aren't going to hard-code your connection string in your final version?

answered Dec 8, 2013 at 19:15

Ann L.

14k5 gold badges39 silver badges67 bronze badges

1 Comment

Soner Gönül Over a year ago

Beat me with 5 seconds :)

Collectives™ on Stack Overflow

Updating Database with DataGrid C#

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related