[RESOLVED] syntax error in update statement

[jazman667]

i'm using this code to try and write a value to a table in an access database. when i try to execute this code i get the error "syntax error in UPDATE statement" i really need help with this and realise its probably a really simple problem but could you please help. if you need any of my other code etc to help then just ask

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUPdate.Click
        Dim myConnection As OleDb.OleDbConnection
        Dim ds As New DataSet
        myConnection = New OleDb.OleDbConnection
        Dim myDataAdapter As OleDb.OleDbDataAdapter
        Dim myTable As String = "Authentication"


        myConnection.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & dbFilePath
        myConnection.Open()
        Dim sql As String = "Select * from " & myTable
        myDataAdapter = New OleDb.OleDbDataAdapter(sql, myConnection)
        Dim cb As New OleDb.OleDbCommandBuilder(myDataAdapter)
        myDataAdapter.Fill(ds, "myTable")

        myConnection.Close()
        temp = txtNew.Text
        temp2 = ds.Tables("myTable").Rows(0).Item(1)

        If txtUser.Text = ds.Tables("myTable").Rows(0).Item(0) Then
            If txtOld.Text = temp2 And txtNew.Text = txtConfirms.Text Then
                encrypt()

                ds.Tables("myTable").Rows(0).Item(1) = temp
' error message on next line
                myDataAdapter.Update(ds, "myTable")

                MsgBox("password updated")
            Else
                MsgBox("passwords did not match")

            End If
        Else
            MsgBox("Username is incorrect")
        End If

    End Sub

[motil]

did you set the update statement ? did you made sure the parameters is not empty (null) ?

[jazman667]

the tutorial i was using, this one >>>> http://www.homeandlearn.co.uk/NET/nets12p10.html didn't tell me too. but also reading on msdn it seemed to say that for simple one table updates you didn't need to set an update statment

[si_the_geek]

Welcome to VBForums

Given that your code includes the words "username" and "password", I strongly suspect that one or more of your table name/field names are reserved words, and as such should not be used as a field/table name (it confuses the query parser within the database system).

For more information (including lists of words), see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of this forum)

[jazman667]

thank you very much that solved it you have no idea how long i've been trying to fix that XD

[danasegarane]

you are closing the connection during update. Close the connection only after the .update method.