Syntax error in UPDATE statement OleDbException

Question

I'm writing an application which stores user information. Currently the user is supposed to update their Name, Height, Weight and Birthday.

string height = TB_ClientHeight.Text;
    string weight = TB_ClientWeight.Text;
    string name = TB_ClientName.Text;
    string bday = dateTimePicker1.Value.ToString("dd-MM-yyyy");
    int heightint = Convert.ToInt32(height);
    int weightint = Convert.ToInt32(weight);

It's updated by calling the public static string username variable from another form and using that as the WHERE UserName = @username.

usernamestringo = Login.usernameFromLogin;

I've followed other SO answers in this context and corrected some issues (like preventing SQL Injection). However I'm still getting a syntax error while updating these fields as claimed by OleDbException.

using (OleDbConnection myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\Repos\Database\Database.accdb;Persist Security Info=False"))
using (OleDbCommand cmd = new OleDbCommand())
{
    cmd.CommandType = CommandType.Text;
    string query = "UPDATE TPersons SET Name=@Name, SET Height=@Height, SET Weight=@Weight, SET Bday=@Bday " + " WHERE FirstName= @username";
    cmd.CommandText = query;
    cmd.Parameters.AddWithValue("@Name", name.ToString());
    cmd.Parameters.AddWithValue("@Height", heightint.ToString());
    cmd.Parameters.AddWithValue("@Weight", weightint.ToString());
    cmd.Parameters.AddWithValue("@Bday", bday.ToString());
    cmd.Parameters.AddWithValue("@username", usernamestringo);

    cmd.Connection = myCon;
    myCon.Open();
    cmd.ExecuteNonQuery();
    MessageBox.Show("Updated!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
    cmd.Parameters.Clear();
}

The OleDbException is:

Index #0 NativeError: -526847407 Source: Microsoft Access Database Engine SQLState: 3000 Description (message): Syntax error in UPDATE statement.

Could anyone guide me where my syntax is wrong? Thank you!

I think you just need one SET, followed by all the comma separated field name to value assignments. — user1726343
– user1726343, Commented May 2, 2015 at 22:54
have you looked up how to use the SET statement.. you could have saved yourself a ton of time by just doing a simple msdn Update Set` statement.. you only need one SET statement separating the rest of the values that follow with a , — MethodMan
– MethodMan, Commented May 2, 2015 at 22:54
I just realised about the SET statement thing. Additionally, apparently "Name" is a reserved word so I also did [Name]. The code works perfectly now! Thank you. — kkmonlee
– kkmonlee, Commented May 2, 2015 at 23:00

Steve · Accepted Answer · 2015-05-02 23:01:06Z

2

The UPDATE syntax is

 UPDATE <tablename> SET field1=Value1, field2=Value2 WHERE primarykeyname=Value3

The SET keyword precedes only the first column to update, and you have another problem with the NAME column. In Access this is a reserved keyword. Use brackets around that column name (or better change it to something not so troublesome)

So:

string query = @"UPDATE TPersons SET [Name]=@Name, 
                 Height=@Height, Weight=@Weight, Bday=@Bday 
                 WHERE FirstName= @username";

Not strictly related to your current problem, but you should look also at this article Can we stop using AddWithValue already? The DbCommand.AddWithValue is a shortcut with numerous drawbacks. Better avoid it.

edited May 2, 2015 at 23:01

answered May 2, 2015 at 22:57

Steve

217k22 gold badges242 silver badges296 bronze badges

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

2 Comments

user1726343 Over a year ago

Missed the issue with the Name column. +1

kkmonlee Over a year ago

Yes - I realised the [Name] issue as I published my question. Thanks so much for all of your help!

Collectives™ on Stack Overflow

Syntax error in UPDATE statement OleDbException

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related