update statement with oledbconnection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, Can any1 help me identify the problem with my update statement? Everytime
I'm trying to run it, there's "invalid sql statement. expected 'delete',
'insert', 'procedure' or 'update'. error msg coming up. Or could it be
something else? Here's the code. Thx in advance!

Private Sub cmUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmUpdate.Click

strConn = ACCESS_CONNECTION_STRING

' Initialize variables to objects in the OleDb namespace.
Dim ocnnDvd As New OleDbConnection(strConn)

ocnnDvd.Open()

Dim myUpdateCmdSql As String

myUpdateCmdSql = "UPDATE Member SET"
myUpdateCmdSql &= "MemberID='" & txtMmID.Text & "',"
myUpdateCmdSql &= "Surname='" & cmbMmSurname.Text & "',"
myUpdateCmdSql &= "First_Name='" & txtMmFirstName.Text & "',"
myUpdateCmdSql &= "Address='" & txtMmAddress.Text & "',"
myUpdateCmdSql &= "Suburb='" & txtMmSuburb.Text & "',"
myUpdateCmdSql &= "Postcode='" & txtMmPostcode.Text & "',"
myUpdateCmdSql &= "State='" & cmbMmState.Text & "',"
myUpdateCmdSql &= "Phone(H)='" & txtMmPhoneH.Text & "',"
myUpdateCmdSql &= "Phone(W)='" & txtMmPhoneW.Text & "',"
myUpdateCmdSql &= "Phone(M)='" & txtMmPhoneM.Text & "',"
myUpdateCmdSql &= "Password='" & txtMmPw.Text & "',"
myUpdateCmdSql &= "Password_Q='" & txtMmPassReminder.Text & "',"
myUpdateCmdSql &= "WHERE MemberID=" & txtMmID.Text

Dim myUpdateCmd As New OleDbCommand("myUpdateCmdSql", ocnnDvd)

Try
myUpdateCmd.ExecuteNonQuery()
Catch exp As Exception
MessageBox.Show(exp.Message, Me.Text, MessageBoxButtons.OK, _
MessageBoxIcon.Error)
Exit Sub
End Try

ocnnDvd.Close()
end sub
 
yenyen,

You might need a space after the SET keyword:

myUpdateCmdSql = "UPDATE Member SET "

Kerry Moorman
 
Thx for the tip. I did that but its still showing the same msg, is kinda
weird. I tried many ways of modifying the updating statement, but non work.
Is there any other way I can update the db?
 
yenyen,

Try removing the quotes from around myUpdateCmdSql. Change:

Dim myUpdateCmd As New OleDbCommand("myUpdateCmdSql", ocnnDvd)

to

Dim myUpdateCmd As New OleDbCommand(myUpdateCmdSql, ocnnDvd)

Kerry Moorman
 
Back
Top