Syntax error SQL query using UPDATE

Question

Context

I added the option to modify the password and email address on my website. I have a microsoft Access Database. It has the columns "EmailAddress", "Password" and "Username" in the "Login" Table.

Code

'For testing purposes, lets say :
VB_PasswordTXT = "1000:8NHLJo5oIDugFdzscuMrNSKyoNTRa0kI:6NSlpqGYC4zU2BG6cfJaHaPgVRCDPCc2"
VB_UserNameTXT = "Test123"
VB_EmailTXT = "[email protected]"

Using thisConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""path""")
    Using thisCommand As OleDbCommand = thisConnection.CreateCommand
        ' Open connection object
        thisConnection.Open()

        ' Initialize SQL SELECT command to retrieve desired data
        thisCommand.CommandText = "UPDATE Login SET EmailAddress = @EmailAddress, Password = @Password " & _
                                "WHERE Username = @Username"
        thisCommand.Parameters.AddWithValue("@Username", VB_UserNameTXT)
        thisCommand.Parameters.AddWithValue("@EmailAddress", VB_EmailTXT)
        thisCommand.Parameters.AddWithValue("@Password", VB_PasswordTXT)

        ' Create a DataReader object based on previously defined command object
        Dim thisReader As OleDbDataReader = thisCommand.ExecuteReader() 'Crashes here
        If thisReader.Read() Then
        End If

        thisReader.Close()
        thisConnection.Close()
    End Using
End Using

Crash log

Syntax error in the UPDATE command. (Free translation from French)

Question

(As far as I know), the problem is coming from the UPDATE syntax. What is wrong in it?

Steve · Accepted Answer · 2014-08-12 17:47:12Z

1

Password is a reserved keyword in MS-Access.
When you have a field named in that way you should enclose it in square brakets

  thisCommand.CommandText = "UPDATE Login SET EmailAddress = @EmailAddress, " & _
                            "[Password] = @Password " & _
                            "WHERE Username = @Username"

I suggest, if this is still possible, to change that name

And, while ExecuteReader works, the correct method to use for an UPDATE/INSERT/DELETE sql is

    thisCommand.ExecuteNonQuery()

Calling ExecuteReader is not good because you force it to create an OleDbDataReader not needed in case of an update.

EDIT
Reading your last comment I have noticed that you have your parameters added in the wrong order in the collection. OleDb doesn't recognize the parameters by their name but by their position in the parameters collection. You should add them in the exact order in which their respective placeholders appear in the command text (Move the username to be the last one added)

   thisCommand.CommandText = "UPDATE Login SET EmailAddress = @EmailAddress, " & _ 
                             "[Password] = @Password " & _
                             "WHERE Username = @Username"
    thisCommand.Parameters.AddWithValue("@EmailAddress", VB_EmailTXT)
    thisCommand.Parameters.AddWithValue("@Password", VB_PasswordTXT)
    thisCommand.Parameters.AddWithValue("@Username", VB_UserNameTXT)

edited Aug 12, 2014 at 17:47

answered Aug 12, 2014 at 17:21

Steve

217k22 gold badges242 silver badges296 bronze badges

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

7 Comments

Grim Over a year ago

Just as an addendum, it's good practice to put ALL table and column names in square brackets.

Sifu Over a year ago

I'll accept the answer as it fixed my crash problem. (I have actually changed the column name for clarity). Now, I am trying to figure out why it doesn't actually update the value.. Thing is, I have other UPDATE queries that work properly. Oh well, thanks!

Fionnuala Over a year ago

@Grim, it is much better practice to use an alias, thus eliminating the need for square brackets. No one should have spaces or reserved characters in field / column names.

Fionnuala Over a year ago

@grim Perhaps that was not clear, if you use an alias, you also avoid problems such as two columns from separate tables having the same name, as well as reserved words. For example login.password or l.password will work.

Sifu Over a year ago

@Steve, I had just found the issue (ordered parameters) and was going to write it down here, but it looks like you even found that! Thanks again, everything is working as intended now.

|

Collectives™ on Stack Overflow

Syntax error SQL query using UPDATE

Context

Code

Crash log

Question

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Context

Code

Crash log

Question

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related