Click here to Skip to main content
15,908,013 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 0: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    
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    
Oh!
Update statements should be in the form of "UPDATE table SET x=y WHERE id=i"
Chiranthaka Sampath 13-Aug-17 7:29am    
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