Question syntax error in update statement

[Jazman667]

hey guys
i'm new here because noone else was replying on any other forums, i hope you can help.

i get the error message "syntax error in UPDATE statement" when my code reaches the line "myDataAdapter.update(ds, "Table") any ideas why and how to solve it?

Dim myConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection
Dim myDataAdapter = New OleDb.OleDbDataAdapter
Dim ds As New DataSet
Dim Table As String = "authentication"
Dim sql As String = "Select * from " & Table



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUPdate.Click

myConnection.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & dbFilePath
myConnection.Open()
myDataAdapter = New OleDb.OleDbDataAdapter(sql, myConnection)
myDataAdapter.Fill(ds, "Table")

temp = txtNew.Text
temp2 = ds.Tables("Table").Rows(0).Item(1)

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

Dim cb As New OleDb.OleDbCommandBuilder(myDataAdapter)
ds.Tables("Table").Rows(0).Item(1) = temp

myDataAdapter.Update(ds, "Table")

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

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

myConnection.Close()

End Sub

 

[gchq]

Do you have a unique key defined in the backend DB?

Wouldn't it be better to loop through the data and make any changes?

For Each Row As DataRow In DS.Tables(0).Rows
            If Row("UserName") = TextUser.text Then
                'Update the row
                Row("UserName") = TextNew.Text
            End If
        Next

 

[Jazman667]

yh i have a primary key defined.

i don't really know how to use the way you said (yh A-levels aren't what they used to be) so any ideas how to get mine to work? if not thne can you explain a bit more about yours?

cheers

 

[gchq]

It's been a long time since I used OleDB - but I would go for a route like this:-

Private UserID as Session(UserID)

============================

Dim Df As DbProviderFactory
    Dim dbProv As String = "System.Data.oleDB"
 Df = DbProviderFactories.GetFactory(dbProv)
        Dim ConnString1 As String = "DSN"
        Dim ConnString2 As String = "Name"
        Dim Reader1 As Object = oleDBDataReader
        Dim csb As DbConnectionStringBuilder = Df.CreateConnectionStringBuilder()
        csb.Add(ConnString1, ConnString2)
        Dim Conn As DbConnection = Df.CreateConnection()
        Conn.ConnectionString = csb.ConnectionString
        Dim cmd As DbCommand = Conn.CreateCommand()


Dim CheckPassword as integer = 0
cmd.CommandText = "SELECT COUNT(Key_ID) FROM Table_Name WHERE User_Name = '" & UserName.Text & "'"
CheckPassword = cmd.ExecuteScalar()
If not CheckPassword = 1 then
'The user name doesn't exist
'Send out a message
Conn.Close()
Exit Sub
End If

'Otherwise update the password field
cmd.CommandText = "UPDATE Table_Name SET User_Password = '" & NewPassword.Text & "' WHERE KeyID  = "  & UserID
cmd.ExecuteNonQuery()
Conn.Close()

The syntax may be a bit out - as I said a bit rusty with that now

Using this method you are only returning the one record you need to update

This is based on the concept that the user enters a unique user name and a new password into TextBoxes

 

[Jazman667]

ok my teacher has said your code looks too complicated for my level of programming so i need to fix mine :/ else they'll know i didn't do it