Access database syntax error in update into statement

Question

First of all I know password is a reserved type of Access Database. But I have read a post that you can put [password] like that and it will work. But its not working. I have tried many ways and still, I hope that some one will help.

OleDbCommand cmd = new OleDbCommand();

try
{
    String query = "update [Employe] set [UserName] ='" + txtNewUser.Text +"', [Password] ='"+ txtNewPass.Text + "', [Authorization] ='" + nudAuthorizationLvl.Value + "', where [Id] = '" + int.Parse(txtExistingId.Text);
    cmd.CommandText = query;
    cmd.Connection = conn;
    conn.Open();

    cmd.ExecuteNonQuery();
    System.Windows.Forms.MessageBox.Show("Info Updated!!!");

    conn.Close();
}
catch (Exception ex)
{
    MessageBox.Show("Error" + ex);
}
finally
{
    conn.Close();
}

Use [Parameters](http://www.dotnetperls.com/sqlparameter) or you might be a victim of a SQL Injection attack. What's not working exactly? Any exception or error message? — yazanpro, Apr 26 '15 at 03:44
As mentioned above, you should really change that to use parameters. Also, I believe you have an extra comma right before the "where" clause that could be causing a syntax error — Pablo Romeo, Apr 26 '15 at 04:11

score 1 · Accepted Answer · answered Apr 26 '15 at 04:21

I believe you have an extra comma right before your where clause and an extra quote before the ID.

Also, always use parameters, to avoid Sql Injection attacks:

conn.Open();
cmd.CommandText = "update [Employe] set [UserName] =@userName, [Password] =@password, [Authorization] =@authorization where [Id] = @id";
cmd.Connection = conn;
cmd.Parameters.AddRange(new OleDbParameter[]
       {
           new OleDbParameter("@userName", txtNewUser.Text),
           new OleDbParameter("@password", txtNewPass.Text),
           new OleDbParameter("@authorization", nudAuthorizationLvl.Value),
           new OleDbParameter("@id", int.Parse(txtExistingId.Text))
       });
cmd.ExecuteNonQuery();

didn't think it was possible to give the comand text through parameters. Thanks man really help full you just made my night. And thanks for the tip. I will try to change my structer with parameters. — NinjaUltra, Apr 26 '15 at 04:28
Glad to help :) Yes, always use parameters, or else you'll be open to very easy hacks — Pablo Romeo, Apr 26 '15 at 04:35

score 1 · Answer 2 · edited May 23 '17 at 12:06

I think there's a syntax error in your update query. Considering your ID field is of type INT, there should not be any ' before the actual value. So you should change your query to the following:

String query = "update [Employe] set [UserName] ='" + txtNewUser.Text +"', [Password] ='"+ txtNewPass.Text + "', [Authorization] ='" + nudAuthorizationLvl.Value + "', where [Id] = " + int.Parse(txtExistingId.Text);

With that being said, you should really be using parameterized query to pass parameters.

Access database syntax error in update into statement

2 Answers2