How do you fix the error "Syntax error in UPDATE statement"?

How do you fix the error "Syntax error in UPDATE statement"?

• coolerelf

  Thank you for answering. I did number 1 but I'm not sure if what I did in number 2 is correct. In case both were followed, the code would look like the one below, right The problem I encountered now is it is not being stored in the actual database, just in memory only. Guys, please help.

  Public Sub changePassword(ByVal uName As String, ByVal pWord As String)

  Try

  'change the user's password

  Dim conn As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter

  Dim cmd As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand

  changeUserPasswordDataSet = New System.Data.DataSet

  connection2.Open()

  trans = connection2.BeginTransaction

  cmd.Connection = connection2

  cmd.CommandText = "UPDATE Users SET [password] = WHERE [username] = "

  cmd.Parameters.AddWithValue("[password]", uName)

  cmd.Parameters.AddWithValue("[username]", pWord)

  cmd.CommandType = CommandType.Text

  cmd.Transaction = trans

  cmd.ExecuteNonQuery()

  trans.Commit()

  Catch ex As Exception

  trans.Rollback()

  Throw ex

  Finally

  connection2.Close()

  connection2.Dispose()

  trans.Dispose()

  End Try

  End Sub

  
  

• Navaneeth

  
  You're assigning the uName value to the password parameter and the pWord value to the username parameter. I think you probably want the following:

  cmd.Parameters.AddWithValue("[password]", pWord)
  cmd.Parameters.AddWithValue("[username]", uName)

  
  
  

• AstralPilot

  a couple of suggestions:

  1. put square brackets around your field names when there is a possiblity of conflict with reserved/key words

  2. Try using stored procedures and pass the variables as parameters of the command object...

  hope that helps

  
  
  

• JD3027

  Oh my holy goodness caramba!!! How careless of me... That's the reason why I didn't get to perform the database command properly.

  Thanks Paul P Clement IV, it's correct this time! To DMan1, thank you for helping me out get to the correct solution too. :-)

  
  
• How do you fix the error "Syntax error in UPDATE statement"?