Why am I getting a "Syntax error in UPDATE statement." exception?

Question

 public void EditUser(string id, string userid, string email, string password)
    {
        try
        {
            _dbConn.Open();

            OleDbCommand updateCmd = new OleDbCommand("UPDATE LOGINS SET Username=?, Password=?, Email=? WHERE ID=?", _dbConn);


            OleDbParameter param1 = new OleDbParameter("Username", userid);
            OleDbParameter param2 = new OleDbParameter("Password", password);
            OleDbParameter param3 = new OleDbParameter("Email", email);
            OleDbParameter param4 = new OleDbParameter("ID", id);

            param1.DbType = DbType.AnsiString;
            param1.Direction = ParameterDirection.Input;
            param2.DbType = DbType.AnsiString;
            param2.Direction = ParameterDirection.Input;
            param3.DbType = DbType.AnsiString;
            param3.Direction = ParameterDirection.Input;
            param4.DbType = DbType.AnsiString;
            param4.Direction = ParameterDirection.Input;

            updateCmd.Parameters.Add(param1);
            updateCmd.Parameters.Add(param2);
            updateCmd.Parameters.Add(param3);
            updateCmd.Parameters.Add(param4);


            int rowsAffected = updateCmd.ExecuteNonQuery();

        }
        catch (Exception excep)
        {

            System.Diagnostics.Trace.WriteLine(excep.Message);
        }
        finally
        {
            if (_dbConn.State == ConnectionState.Open)
                _dbConn.Close();

        }
        return;
    }

I'm using C# and Access 2007, this works perfectly fine when I'm updating a single column. But when I'm trying to update multiple columns at the same time I start to get this exception

"Syntax error in UPDATE statement.".

Soner Gönül · Accepted Answer · 2015-04-30 12:53:06Z

6

Password is a reserved keyword in OLE DB Provider. You need to use it with square brackets like [Password].

As a best practice, change it to non-reserved word.

Also use using statement to dispose your OleDbCommand and OleDbConnection even we can't see it.

answered Apr 30, 2015 at 12:53

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

1 Comment

Ali Over a year ago

Thank you, also appreciate the advice on the using statement

Nimish goel · Accepted Answer · 2015-05-01 09:39:33Z

0

I agree with Sonar. Password is a reserved keyword in OLE DB Provider.

If you are using multiple tables, To avoid Confusion from reserved keywords -- you must use. select table.[Password] from table;

UPDATE table SET table.[Password]=?

answered May 1, 2015 at 9:39

Nimish goel

2,7796 gold badges31 silver badges42 bronze badges

Collectives™ on Stack Overflow

Why am I getting a "Syntax error in UPDATE statement." exception?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related