update query for 2007 vb.net

[Mike Bishop]

i am having issues with an update query.

i normally use SQL 2005 but for this project i am using an access 2007 database

please can anyone point me in the right direction,

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 " & vbCrLf & _
                                "SET Password = @Password" & vbCrLf & _
                                 "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

            MsgBox("you have changed your password")

someone please help this has stopped me in my tracks now for over a day
the error i get is syntax error in update statement.

[adam_k]

try adding a space before the where and after the double quotes.
If this doesn't work get a glimpse of your update by using debug.print and copy/paste it here.

PS: Why are you using VBCrLf in the update?

[Mike Bishop]

Hi adam_k sorry but i am a newbie at VB, my code is

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

but still getting the error "Syntax error in update statement"

how do i use the debut.print?

[adam_k]

before cmd.executenonquery insert debug.print cmd.commandtext
In the output window of your VS while debugging you'll get the actual command send to the db, once the program has reached that command. You can insert a breakpoint at executenonquery to be sure you don't miss the query.

Take the query from the output window and try to run it against the db manually. Sometimes it's just a fat-fingered field name.

PS: On a second thought, what is _userid ?

[Reverend Jim]

Try putting single quotes around '@Password' in line 7. If _userid is also a string then do the same around '@ID'

[Mike Bishop]

thanks but still no joy

[adam_k]

You still haven't answered what is _userid. I hope it's a string or int var and not a textbox or some other control.

[Reverend Jim]

Go into Access and open the database. From there, open a SQL query window and try a few similar queries in immediate mode until you get the format right.

[Mike Bishop]

sorry _userid is stored in a Int var

i have copyed the sql command into access and it works fine