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 or you might be a victim of a SQL Injection attack. What's not working exactly? Any exception or error message? — yazanpro
– yazanpro, Commented Apr 26, 2015 at 3: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
– Pablo Romeo, Commented Apr 26, 2015 at 4:11

Pablo Romeo · Accepted Answer · 2015-04-26 04:21:49Z

1

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

answered Apr 26, 2015 at 4:21

Pablo Romeo

11.4k2 gold badges32 silver badges60 bronze badges

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

2 Comments

NinjaUltra Over a year ago

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.

Pablo Romeo Over a year ago

Glad to help :) Yes, always use parameters, or else you'll be open to very easy hacks

Community · Accepted Answer · 2017-05-23 12:06:21Z

1

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.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Apr 26, 2015 at 4:23

Piyush

8248 silver badges20 bronze badges

Collectives™ on Stack Overflow

Access database syntax error in update into statement

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related