Click here to Skip to main content
13,252,065 members (55,983 online)
Rate this:
 
Please Sign up or sign in to 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:

 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 13-Aug-17 1:15am
Updated 13-Aug-17 1:36am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Remove the ' single quotes around your values :
SqlComm = new SqlCommand("UPDATE MyDataTable ('DataDesc', 'DataDate', 'DataQty') VALUES (@DataDesc, @DataDate, @DataQty) WHERE DataID = @DataID", SqlConn);
  Permalink  
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);
}
}
}
Mehdi Gholam 13-Aug-17 7:34am
   
Remove the quotes around the values.
Chiranthaka Sampath 13-Aug-17 12:48pm
   
Problem solved. Many thanks for your help!

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.171114.1 | Last Updated 13 Aug 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100