Syntax error in UPDATE statement OleDb Exception in C#

Question

I already double check my SQL Statement and it seems that my SQL Statement is Error. I don't why it doesn't work. My SQL Statement is correct and It resulted to this OleDBException.

Here is the code

public void updateAccount(Int32 accountid, String username, String password, String isdisable)
{
     con.ConnectionString = db.konek();
     String sql = "UPDATE accounts SET username = @username, password = @password, isdisable = @isdisable WHERE accountid = @accountid";
     try
     {
         con.Open();
         OleDbCommand cmd = new OleDbCommand(sql, con);
         cmd.Parameters.AddWithValue("@username", username);
         cmd.Parameters.AddWithValue("@password", password);
         cmd.Parameters.AddWithValue("@isdisable", isdisable);
         cmd.Parameters.AddWithValue("@accountid", accountid);
         cmd.ExecuteNonQuery();
     }
     finally
     {
         con.Close();
     }
}

Screenshots of my MS Access Table enter image description here

Exception Screenshot enter image description here

The error is pointing at other code, not the code you posted. Note the error Syntax error in INSERT INTO statement so post that piece of code please. You can also see that you are calling cmd.Dispose() after that line... — JonH
– JonH, Commented Oct 11, 2012 at 11:20
Well the exception (and those are usually right) is speaking about INSERT statement, your code is showing UPDATE statement. You are not showing us the relevent code or there is a trigger in your database with some insert logic. — tpeczek
– tpeczek, Commented Oct 11, 2012 at 11:22

Steve · Accepted Answer · 2012-10-18 09:57:54Z

5

You are using a MICROSOFT JET reserved word PASSWORD.
This is the origin of your syntax error.

You should encapsulate your sql command with square brackets

 String sql = "UPDATE accounts SET username = @username, [password] = @password, "  + 
              "isdisable = @isdisable WHERE accountid = @accountid";

And for the named parameters problem.
As far as Microsoft Access is concerned, the provider Microsoft.ACE.OLEDB.12.0 allows you to use the syntax above compatible with SqlServer. However you should respect the correct order of parameter placeholders when inserting your parameters in the OleDbParameter collection of the OleDbCommand

edited Oct 18, 2012 at 9:57

answered Oct 11, 2012 at 12:37

Steve

217k22 gold badges242 silver badges296 bronze badges

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

Comments

Tobsey · Accepted Answer · 2012-10-11 11:35:20Z

When you add a named parameter like that, the text is not replaced in the sql query. You need to use "?" in the sql string and then the parameters will be replaced:

string sql = "UPDATE accounts SET username = ?, password = ?, isdisable = ? WHERE accountid = ?"

What you are doing there with the named parameters only works for procedures, not text.

If your command was set as follows:

cmd.CommandType = CommandType.StoredProcedure

then named parameters would work. However you are using CommandType.Text, which is the default, and therefore you need to use the "?" in order to get the OLEDB driver to use your parameters.

josef · Accepted Answer · 2013-03-23 04:59:45Z

the code for update is for storedprocedures in SQL server and may not work with access DB:

        string _Update_Emp = "UPDATE AlbahraniNetwork SET FirstName=@FirstName,SecondName=@SecondName,LastName=@LastName,Phone=@Phone,Cell=@Cell,Email=@Email,Address=@Address where FirstName=@FirstName";

        string appPath = Path.GetDirectoryName(Application.ExecutablePath);
        if (!appPath.EndsWith("\\"))
            appPath += "\\";
        _Conn.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + appPath + "Database31.accdb");

        //_Conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Abdullah\documents\visual studio 2010\Projects\AlbahraniNetwork2\AlbahraniNetwork2\Database31.accdb";
        _Conn.Open();
        OleDbCommand _Update_Command = new OleDbCommand(_Update_Emp, _Conn);

        _Update_Command.Parameters.AddWithValue("@FirstName", FirstName.Text);
        _Update_Command.Parameters.AddWithValue("SecondName", SecondName.Text);
        _Update_Command.Parameters.AddWithValue("@LastName", LastName.Text);
        _Update_Command.Parameters.AddWithValue("@Phone", Phone.Text);
        _Update_Command.Parameters.AddWithValue("@Cell", Cell.Text);
        _Update_Command.Parameters.AddWithValue("@Email", Email.Text);
        _Update_Command.Parameters.AddWithValue("@Address", Address.Text);
        _Update_Command.ExecuteNonQuery();

Make it simple and instead use:

  string _Update_Emp = "UPDATE AlbahraniNetwork SET " +
       FirstName=\"" + FirstName.Text + "\"" +
       ",SecondName=\"" + SecondName.Text  + "\"" +
       ",LastName=\""+ LastName.Text  + "\"" +
       ",Phone=\""+ Phone.Text + "\"" +
       ",Cell=\"" + Cell.Text + "\"" +
       ",Email=\"" + Email.Text + "\"" +
       ",Address=\"" + Address.Text + "\"" +
       " where FirstName=\"" + FirstName.Text +";";

        string appPath = Path.GetDirectoryName(Application.ExecutablePath);
        if (!appPath.EndsWith("\\"))
            appPath += "\\";
        _Conn.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + appPath + "Database31.accdb");

        //_Conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Abdullah\documents\visual studio 2010\Projects\AlbahraniNetwork2\AlbahraniNetwork2\Database31.accdb";
        _Conn.Open();
        OleDbCommand _Update_Command = new OleDbCommand(_Update_Emp, _Conn);

Collectives™ on Stack Overflow

Syntax error in UPDATE statement OleDb Exception in C#

3 Answers 3

Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Related