0

The error is "Syntax error in UPDATE Statement"

string id = lblUserID.Text;
DB_Connection.con.Open();
string sql = "UPDATE TblUser SET LastName = @LastName,FirstName = @FirstName,MI = @MI,UserLevel = @UserLevel,Username = @Username,Password = @Password WHERE UserID = '" + id + "'";
DB_Connection.command = new OleDbCommand(sql, DB_Connection.con);

DB_Connection.command.Parameters.AddWithValue("@LastName", txtLastName.Text);
DB_Connection.command.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
DB_Connection.command.Parameters.AddWithValue("@MI", txtMI.Text);
DB_Connection.command.Parameters.AddWithValue("@UserLevel", cmbUserLevel.Text);
DB_Connection.command.Parameters.AddWithValue("@Username", txtUsername.Text);
DB_Connection.command.Parameters.AddWithValue("@Password", txtPassword.Text);
DB_Connection.command.ExecuteNonQuery();
DB_Connection.con.Close();
John Saunders
  • 160,644
  • 26
  • 247
  • 397
user3639093
  • 1

2 Answers2

2

Most likely UserID is a number in your table, but the apostrophes you've surrounded the value with means you're trying to assign a string literal to a number column.

Parameterize the user id too:

string sql = "UPDATE TblUser SET ..... WHERE UserID = @UserId";

...

var userId = int.Parse(lblUserID.Text);  // will throw exception if not a number
DB_Connection.command.Parameters.AddWithValue("@UserId", userId);

...
...

Unrelated, but another observation. Instead of using whatever class-level structure DB_Connection is, consider using using statements and create the connection and command locally, as you need them.

The using statement will close the connection so you don't have to call Close() explicitly (which, incidentally, won't run if an exception is thrown in your method).

Grant Winney
  • 65,241
  • 13
  • 115
  • 165
0

Syntax error in the query. Apparently it seems following reason:

  1. May be you are using single quote for userID. Assuming if userID is integer in database then you have to remove single quotes.

  2. Another possibility, may be you are missing adding brackets around database table column names. Which match the names of reserved words in SQL.

Example: 

[Password] = @Password

Please see this link to understand why square brackets are useful, although not mandatory.

Here is list of reserved words for Microsoft OLE DB Provider.

Community
  • 1
  • 1
Hassan
  • 5,360
  • 2
  • 22
  • 35
  • I think necessary when your DB column match with reserved words of SQL server. – Hassan May 15 '14 at 03:33
  • 1. The OP may or may not be using SQL Server. 2. It's not usually necessary, and only `Password` is a reserved word. – John Saunders May 15 '14 at 03:36
  • @JohnSaunders. I have specified Password column in the example. If you think my answer is not good then plz edit. – Hassan May 15 '14 at 03:43