CODEWITHSUNDEEP
c#databasems-accesssyntax-errorinsert-update
NinjaUltra
NinjaUltra

Reputation: 13

Access database syntax error in update into statement

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

Upvotes: 1

Views: 1023

Answers (2)

Piyush
Piyush

Reputation: 826

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.

Upvotes: 1

Pablo Romeo
Pablo Romeo

Reputation: 11396

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

Upvotes: 1

Related Questions