Thabang Ronin Thabang Ronin - 1 year ago 67 Question

I get "Syntax error in UPDATE statement" with OleDB

I am developing an information system that works with a connected data source / MS Access database. The question is kinda cliche but I can't seem to find a proper solution from the similar ones I have come across.

Here is my code for the button.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'myConnection.ConnectionString = connString
If Me.txtConfirmPasscode.Text = Me.txtNewPasscode.Text Then
Dim updateCmd As OleDbCommand = New OleDbCommand("UPDATE Users SET Password = @ConfPasscode WHERE [Usernames] = @UsersID", myConnection)
Dim dr2 As OleDbDataReader = updateCmd.ExecuteReader 'SYNTEX ERROR IN UPDATE STATEMENT

With updateCmd.Parameters
updateCmd.Parameters.AddWithValue("@value", txtUserID.Text)
updateCmd.Parameters.AddWithValue("@firstname", txtConfirmPasscode.Text)
End With


Dim recFound As Boolean = False
Dim UserName As String = ""

While dr2.Read
recFound = True
UserName = dr2("Usernames").ToString
End While

If recFound = True Then
MessageBox.Show("Password changed successfully for " & UserName & ".", "Password Changed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

'updateCmd.Parameters.Add(New OleDbParameter("Password", CType(txtConfirmPasscode.Text, String)))
End If

End If

Catch ex As Exception
End Try
End Sub

I get a huge
statement syntax error when I reach these lines of code:

Dim updateCmd As OleDbCommand = New OleDbCommand("UPDATE Users SET Password = @ConfPasscode WHERE [Usernames] = @UsersID", myConnection)
Dim dr2 As OleDbDataReader = updateCmd.ExecuteReader 'I GET THE SYNTAX ERROR IN UPDATE STATEMENT ERROR HERE!

I hope that I can get a solution that works without overly formatting the code. I would also like to get solutions to my code grammer / syntax that could possibly cause some other problems in the above code

Answer Source

Password is a reserved keyword in ms-access. You need square brackets around it, but then you have another problem. You should set the parameters BEFORE executing the query, and albeit OleDb doesn't recognize parameters by name but by position, giving a matching name with your placeholders doesn't hurt

Dim updateCmd As OleDbCommand = New OleDbCommand("UPDATE Users 
     SET [Password] = @ConfPasscode 
     WHERE [Usernames] = @UsersID", myConnection)
With updateCmd.Parameters
    ' First ConfPasscode because is the first placeholder in the query
    updateCmd.Parameters.AddWithValue("@ConfPasscode ", txtConfirmPasscode.Text)
    ' Now UsersID as second parameter following the placeholder sequence
    updateCmd.Parameters.AddWithValue("@UsersID", txtUserID.Text)
End With
Dim rowUpdated = updateCmd.ExecuteNonQuery

In response to the comment below of Andrew Morton, I should mention to the problems caused by AddWithValue. In this context, with just strings, it is a performance problem, in other context (dates and decimals) could escalate to a correctness problem.

Can we stop to use AddWithValue already?
How data access code affects database performance

Also, as noted in another answer, the correct method to use for an Update query is ExecuteNonQuery, but also ExecuteReader can update your table but because it build an infrastructure required only when you have something to read is less efficient for an Update. In any case just use only ExecuteNonQuery or ExecuteReader

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download