4 Replies - 889 Views - Last Post: 11 June 2013 - 05:58 PM Rate Topic: -----

#1 optix212   User is offline

  • D.I.C Addict
  • member icon

Reputation: 30
  • View blog
  • Posts: 540
  • Joined: 10-October 09

Error in my SQL syntax while updating database

Posted 11 June 2013 - 12:15 PM

So, I am pretty new to SQL and have been integrating my own queries into a program that I have been creating. The program will connect to a database, retrieve a table, and display it on a DataGridView. My DataGridView is populating just fine, and I am able to edit the columns accordingly.. The thing that isn't working is the Save button... I don't know exactly what I am doing wrong in my query, but here is the code:

                // Set the UPDATE command and parameters.
                adapter.UpdateCommand = new MySqlCommand(
                    "UPDATE items SET [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], WHERE [email protected]",
                    connection);
                adapter.UpdateCommand.Parameters.Add("@AccountID", MySqlDbType.Int16, 3, "AccountID");
                adapter.UpdateCommand.Parameters.Add("@AccountName", MySqlDbType.VarChar, 100, "AccountName");
                adapter.UpdateCommand.Parameters.Add("@Address", MySqlDbType.VarChar, 100, "Address");
                adapter.UpdateCommand.Parameters.Add("@City", MySqlDbType.VarChar, 100, "City");
                adapter.UpdateCommand.Parameters.Add("@State", MySqlDbType.VarChar, 100, "State");
                adapter.UpdateCommand.Parameters.Add("@Zip", MySqlDbType.Int16, 11, "Zip");
                adapter.UpdateCommand.Parameters.Add("@Phone", MySqlDbType.Int16, 10, "Phone");
                adapter.UpdateCommand.Parameters.Add("@Fax", MySqlDbType.VarChar, 100, "Fax");
                adapter.UpdateCommand.Parameters.Add("@Email", MySqlDbType.VarChar, 100, "Email");
                adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;



That is the code for updating the data in the database with whatever modification I have made.

                try
                {
                    //Save records in database using DTItems which is datasource for Grid
                    adapter.Update(DTItems);
                    //Refresh grid
                    DTItems = GetAllAccountItems();
                    accountsDataGridView.DataSource = DTItems;

                    MessageBox.Show("Items saved successfully...");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }



the error message reads:

Quote

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 'WHERE AccountID=6' at line 1


That is the code called once the user clicks the "Save" button..

This code SHOULD take any modified value from the database and overwrite it, while also saving any NEW data entered into the database upon clicking the save button.. The program saves NEW records, but does not overwrite old ones..

This post has been edited by optix212: 11 June 2013 - 12:21 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Error in my SQL syntax while updating database

#2 Michael26   User is offline

  • Futurama: Insert funny joke here
  • member icon

Reputation: 414
  • View blog
  • Posts: 1,664
  • Joined: 08-April 09

Re: Error in my SQL syntax while updating database

Posted 11 June 2013 - 12:29 PM

I think you have extra coma in you statement near Email
[email protected], WHERE [email protected]"

This post has been edited by Michael26: 11 June 2013 - 12:35 PM

Was This Post Helpful? 1
  • +
  • -

#3 optix212   User is offline

  • D.I.C Addict
  • member icon

Reputation: 30
  • View blog
  • Posts: 540
  • Joined: 10-October 09

Re: Error in my SQL syntax while updating database

Posted 11 June 2013 - 12:34 PM

Wow! One more comma than I needed caused all of these problems lol.

Thanks Michael!
Was This Post Helpful? 0
  • +
  • -

#4 Michael26   User is offline

  • Futurama: Insert funny joke here
  • member icon

Reputation: 414
  • View blog
  • Posts: 1,664
  • Joined: 08-April 09

Re: Error in my SQL syntax while updating database

Posted 11 June 2013 - 12:38 PM

I'm glad my post was helpful :)
Was This Post Helpful? 0
  • +
  • -

#5 Momerath   User is offline

  • D.I.C Lover
  • member icon

Reputation: 1020
  • View blog
  • Posts: 2,463
  • Joined: 04-October 09

Re: Error in my SQL syntax while updating database

Posted 11 June 2013 - 05:58 PM

You can also remove the
[email protected],
from the SET statement as you'd just be setting it to itself, since you are using AccountID to find the record.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1