Thabang Ronin Thabang Ronin - 1 month ago 11
Vb.net Question

I get "Syntax error in UPDATE statement" in vb.net Visual Studio 2010

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
'myConnection.Open()
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

updateCmd.ExecuteNonQuery()

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)))
Else
myConnection.Close()
Me.Refresh()
End If
Else

End If

Try
myConnection.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub


I get a huge
UPDATE
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

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.

References
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