CODEWITHSUNDEEP
c#sqlado.netoledbconnection
user3639093
user3639093

Reputation: 1

SQL update statement error with OLEDB

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();

Upvotes: 0

Views: 137

Answers (2)

Hassan
Hassan

Reputation: 5430

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.

Upvotes: 0

Grant Winney
Grant Winney

Reputation: 66449

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).

Upvotes: 2

Related Questions