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