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.

Answers


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

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.


Need Your Help

Backup from subversion 1.7 and restore it to subversion 1.6

eclipse subversive

I have list of subversion repository in standalone linux machine where the subversion version is 1.7. I want to take backup of repositories using svndump and restore it to my central subversion ser...

Android: Create Allocation from pixels array for renderscript

android allocation renderscript

I am trying to use the Android Renderscript for blurring an image. My input is an array of integers that containt the pixel's colors. Here's what I did and not worked. The application shut down wit...