CODEWITHSUNDEEP
sqlvb.netms-access
ken
ken

Reputation: 399

syntax error in sql update statement in vb

I have this sql statement to update a column in access db from vb but when I run the program it shows that there is a syntax error in the statement. The code: 

cmd.Connection = cnn
cmd.CommandText = 
    "UPDATE users SET password='" & 
    Me.pd.Text.Trim & "' WHERE password='" & Me.pd.Tag.ToString & "'"
cmd.ExecuteNonQuery()`

The error shows that there is a syntax error in update statement. I've tried to find the error but in vain.

Upvotes: 1

Views: 1325

Answers (2)

HansUp
HansUp

Reputation: 97131

Your question raised many other important issues as reflected in the comments you've received. I don't want to dismiss any of those issues. However I do want to draw your attention to the fact that password is a reserved word. See Problem names and reserved words in Access.

If you follow mazzucci's advice to bracket the table and field names, your syntax error could go away. However you shouldn't actually need to bracket the table name because users is not a reserved word.

Upvotes: 1

Stelian Matei
Stelian Matei

Reputation: 11623

First of all you should do the update by a different field (e.g. user id, name, email) and not by the current password.

Try to use named parameters instead of string concatenation to avoid errors due to values containing ' and SQL Injection.

You could also use [name] to escape the name of tables or fields (assuming you are using SQL Server).

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx#Y684

cmd.CommandText = "UPDATE [users] SET [password]=@current_password WHERE [password]=@new_password"
cmd.Parameters.AddWithValue("@current_password", Me.pd.Text.Trim)
cmd.Parameters.AddWithValue("@new_password", Me.pd.Tag.ToString)
cmd.ExecuteNonQuery()

Upvotes: 3

Related Questions