can anyone please help me with my update statement, i am trying to update a row within a access 2007 database

here is my code.

Dim con As New OleDb.OleDbConnection
            Dim cmd As New OleDb.OleDbCommand
            Try
                con.ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & Database & ";Persist Security Info=False"
                con.Open()
                cmd.Connection = con
                cmd.CommandText = "update tblUsers SET Password = @Password where ID= @ID"

                cmd.Parameters.AddWithValue("@Password", Me.txtpassword1.Text)
                cmd.Parameters.AddWithValue("@ID", _userid)
                cmd.ExecuteNonQuery()

            Catch ex As Exception
                MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
            Finally
                con.Close()
            End Try

i receive the error Syntax Error on Update STATEMENT

please can someone help this has me stuck all day

I'll take a stab at it. I'm going to assume _userid is integer. A typicl update statement would be (at least in MS SQL)

update tblUsers set Password = 'mypassword' where ID = 12345

Note the quotes around mypassword. try this code

cmd.CommandText = "update tblUsers SET Password = '@Password' where ID = @ID"

and see if you still get the error. I find it helps to create the query in the SQL panel in Access (or MS SQL) before coding it up in your app.

Edited 5 Years Ago by Reverend Jim: n/a

"Password" is a protected word in ACCESS (actually in all Databases)
Use instead:
cmd.CommandText = "update tblUsers SET [Password] = @Password where ID= @ID"

com = New OleDbCommand("update Table1 set present=" & Val(TextBox2.Text) & "where name='" & TextBox1.Text & "' ", con)
com.ExecuteNonQuery()
Call display()
MsgBox("updated")
TextBox1.Text = ""
TextBox2.Text = ""

Comments
incomplete information

Its probably the lack of a space between Val(TextBox2.Text) & "where name='" because its going to join the TextBox2.Text to where e.g. someValuewhere name = ....
And feel free to create your own posts, there's no need to hijack someone else's.

This article has been dead for over six months. Start a new discussion instead.