Click here to Skip to main content
15,500,921 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi All,

I am developing a Windows Application using MS SQL Server and C#. In the application I am trying to update an existing table record using a parameter that retrieve from a text box.

When I execute the SQL command I got an error saying syntax error in VALUES. I cannot find the error in the syntax. I have use the WHERE clause also. Below is the source code.

Could you please show me what I have done wrong?

Thanks,
Chiranthaka

What I have tried:

C#
private void btnUpdateData_Click(object sender, EventArgs e)
       {
               try
               {
                   SqlComm = new SqlCommand("UPDATE MyDataTable ('DataDesc', 'DataDate', 'DataQty') VALUES ('@DataDesc', '@DataDate', '@DataQty') WHERE DataID ='@DataID'", SqlConn);
                   SqlComm.Parameters.AddWithValue("@DataID", txtDataID.Text);
                   SqlComm.Parameters.AddWithValue("@DataDesc", txtDataDesc.Text);
                   SqlComm.Parameters.AddWithValue("@DataQty", int.Parse(txtDataQty.Text));
                   SqlComm.Parameters.AddWithValue("@DataDate", dtpDataDate.Value);
                   SqlComm.ExecuteNonQuery();

                   MessageBox.Show("Data updated!", "DB Connection With App.Config", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

                   Clear();
                   DisableButtons();
               }
               catch (Exception ex)
               {
                   MessageBox.Show(ex.Message);
               }
           }
Posted
Updated 13-Aug-17 1:36am

1 solution

Remove the ' single quotes around your values :
C#
SqlComm = new SqlCommand("UPDATE MyDataTable ('DataDesc', 'DataDate', 'DataQty') VALUES (@DataDesc, @DataDate, @DataQty) WHERE DataID = @DataID", SqlConn);
 
Share this answer
 
Comments
Chiranthaka Sampath 13-Aug-17 6:59am    
Not solved the error is still showing.
Mehdi Gholam 13-Aug-17 7:02am    
Check your data types and the value types you are sending.
Chiranthaka Sampath 13-Aug-17 7:08am     CRLF
I modified the INSERT INTO statement and used it for the UPDATE command. Below is the INSERT INTO command. Data insertion is nicely done. try { SqlComm = new SqlCommand("INSERT INTO MyDataTable (DataID, DataDesc, DataDate, DataQty) VALUES (@DataID, @DataDesc, @DataDate, @DataQty)", SqlConn); SqlComm.Parameters.AddWithValue("@DataID", txtDataID.Text); SqlComm.Parameters.AddWithValue("@DataDesc", txtDataDesc.Text); SqlComm.Parameters.AddWithValue("@DataQty", int.Parse(txtDataQty.Text)); SqlComm.Parameters.AddWithValue("@DataDate", dtpDataDate.Value); SqlComm.ExecuteNonQuery(); MessageBox.Show("Data inserted!", "DB Connection With App.Config", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); Clear(); btnAddData.Enabled = false; } catch (Exception ex) { MessageBox.Show(ex.Message); }
Mehdi Gholam 13-Aug-17 7:13am     CRLF
Oh! Update statements should be in the form of "UPDATE table SET x=y WHERE id=i"
Chiranthaka Sampath 13-Aug-17 7:29am     CRLF
I have fixed the UPDATE statement and it has no errors. SqlComm = new SqlCommand("UPDATE MyDataTable SET DataDesc='@DataDesc', DataDate='@DataDate', DataQty='@DataQty' WHERE DataID='@DataID'", SqlConn); But when I check the database table the record data hadn't updated. The code is at the below. private void btnUpdateData_Click(object sender, EventArgs e) { try { SqlComm = new SqlCommand("UPDATE MyDataTable SET DataDesc='@DataDesc', DataDate='@DataDate', DataQty='@DataQty' WHERE DataID='@DataID'", SqlConn); SqlComm.Parameters.AddWithValue("@DataID", txtDataID.Text); SqlComm.Parameters.AddWithValue("@DataDesc", txtDataDesc.Text); SqlComm.Parameters.AddWithValue("@DataQty", int.Parse(txtDataQty.Text)); SqlComm.Parameters.AddWithValue("@DataDate", dtpDataDate.Value); SqlComm.ExecuteNonQuery(); //sqlComm.CommandText = @"UPDATE tableName SET paramColumn='@paramName' WHERE conditionColumn='@conditionName'"; MessageBox.Show("Data updated!", "DB Connection With App.Config", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); Clear(); DisableButtons(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } }

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900