UPDATE statement in VB connecting to Access

Question

I'm trying to update the position column in my access database but the problem is I'm having problem update that column while the rest of the column will not give out error message.

The error message: Syntax error in UPDATE statement, Microsoft JET database engine...

The code:

Dim myConnection As OleDbConnection = New OleDbConnection

Dim ds As New DataSet
Dim da As OleDbDataAdapter
Dim MaxRows As Integer
Dim i As Integer
Dim sql As String


Private Sub updateButton_Click(sender As Object, e As EventArgs) Handles updateButton.Click
    Using myConnection = New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =C:\LecturerDetail.mdb")

        myConnection.Open()

        Dim str As String

        str = "UPDATE lecturer " & _
              "SET [empName] = ?,[empId] = ?, [position] =?, [faculty] = ? " & _
              " , [degree1] = ?, [degree2] = ?, [degree3] = ?,[degree] = ?, [empType] = ? " & _
              " ,[icNo] = ?, [citizenship] = ?, [phoneNo] = ?, [email] = ?,[permitNo] = ? " & _
              " , [permitStartDate] = ?, [permitEndDate] = ?, [pStatus] =?, [remark] =? " & _
              " WHERE ([empId] = ?) "

        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        cmd.Parameters.AddWithValue("@empName", nameTxt.Text)
        cmd.Parameters.AddWithValue("@empId", empIdTxt.Text)
        cmd.Parameters.AddWithValue("@position", positionComboBox.SelectedText)
        cmd.Parameters.AddWithValue("@faculty", facultyComboBox.SelectedText)
        cmd.Parameters.AddWithValue("@degree1", empDeg1.Text)
        cmd.Parameters.AddWithValue("@degree2", empDeg2.Text)
        cmd.Parameters.AddWithValue("@degree3", empDeg3.Text)
        cmd.Parameters.AddWithValue("@degree", empDeg4.Text)
        cmd.Parameters.AddWithValue("@empType", empTypeComboBox.SelectedText)
        cmd.Parameters.AddWithValue("@icNo", icTxt.Text)
        cmd.Parameters.AddWithValue("@citizenship", citizenshipComboBox.SelectedText)
        cmd.Parameters.AddWithValue("@phoneNo", phoneTxt.Text)
        cmd.Parameters.AddWithValue("@email", emailTxt.Text)
        cmd.Parameters.AddWithValue("@permitNo", permitNoTxt.Text)
        cmd.Parameters.AddWithValue("@permitStartDate", DateTimePicker1.Text)
        cmd.Parameters.AddWithValue("@permitEndDate", DateTimePicker2.Text)
        cmd.Parameters.AddWithValue("@pStatus", statusComboBox.Text)
        cmd.Parameters.AddWithValue("@remark", remark.Text)
        cmd.Parameters.AddWithValue("@empId", empIdTxt.Text)

        Try
            cmd.ExecuteNonQuery()
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            ds.Tables("lecturer").Rows(i).Item(0) = empIdTxt.Text
            ds.Tables("lecturer").Rows(i).Item(1) = nameTxt.Text
            ds.Tables("lecturer").Rows(i).Item(2) = positionComboBox.Text
            ds.Tables("lecturer").Rows(i).Item(3) = facultyComboBox.Text
            ds.Tables("lecturer").Rows(i).Item(4) = empDeg1.Text
            ds.Tables("lecturer").Rows(i).Item(5) = empDeg2.Text
            ds.Tables("lecturer").Rows(i).Item(6) = empDeg3.Text
            ds.Tables("lecturer").Rows(i).Item(7) = empDeg4.Text
            ds.Tables("lecturer").Rows(i).Item(8) = empTypeComboBox.Text
            ds.Tables("lecturer").Rows(i).Item(9) = icTxt.Text
            ds.Tables("lecturer").Rows(i).Item(10) = citizenshipComboBox.Text
            ds.Tables("lecturer").Rows(i).Item(11) = phoneTxt.Text
            ds.Tables("lecturer").Rows(i).Item(12) = emailTxt.Text
            ds.Tables("lecturer").Rows(i).Item(13) = permitNoTxt.Text
            ds.Tables("lecturer").Rows(i).Item(14) = DateTimePicker1.Value
            ds.Tables("lecturer").Rows(i).Item(15) = DateTimePicker2.Value
            ds.Tables("lecturer").Rows(i).Item(16) = statusComboBox.Text
            ds.Tables("lecturer").Rows(i).Item(17) = remark.Text

            da.Update(ds, "lecturer")
            ds.AcceptChanges()
            myConnection.Close()
            MsgBox("Record Updated")
        Catch ex As Exception
            MessageBox.Show(ex.Message & "-" & ex.Source)
        End Try
    End Using
End Sub

My code is to update the columns and allow user to navigate to the next record.

I'm new to visual basic so detail description and guidance are appreciated. Thanks. In addition, i'm trying to create auto notification system based on the dates. Anyone might enlighten me on which methods or applications to be used in visual basic to do it.

Check out this question: http://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database — GregHNZ, Apr 19 '16 at 05:55
Thanks, but it's still don't solve my problems. The other fields can update without problem why only that field is giving me error? — bloom, Apr 19 '16 at 06:18
Your code doesn't really make sense. Why are you calling `ExecuteNonQuery` on a command and `Update` on a data adapter as well? You need to choose one or the other, whichever is more appropriate. The first option would be the one to use if you don't want or need to retrieve the data from the database first. — jmcilhinney, Apr 19 '16 at 06:33
As for the issue, I'll wager that it's the `Update` call that's throwing the exception and the `ExecuteNonQuery` is working fine. That's because you are wrapping all the column names in brackets for the `ExecuteNonQuery` call but the command builder won't do that by default. That means that any reserved words in your column names will cause a syntax error. — jmcilhinney, Apr 19 '16 at 06:35
1.Thanks. But i still don't understand how to get the column position to be updated. For my projects, i have multiple forms and i have a datagrid view that let user click to the above update page. 2.However when i tried to go back to the datagridview the column is not updated first but i have to rerun that page to see the changes. 3.So is there any solution to these? — bloom, Apr 19 '16 at 07:20

score 0 · Answer 1 · answered Apr 19 '16 at 06:28

No need to create the update query hand. The CommandBuilder itself generate the necessary instructions to update the database.

Dim ds As New DataSet
Dim da As OleDbDataAdapter
Dim MaxRows As Integer
Dim i As Integer
Dim sql As String

Private Sub updateButton_Click(sender As Object, e As EventArgs) Handles updateButton.Click
 Using myConnection = New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =C:\LecturerDetail.mdb")
        myConnection.Open()        
        Dim str As String
        str = "SELECT * FROM lecturer WHERE ([empId] = @empId)"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)       
        cmd.Parameters.AddWithValue("@empId", empIdTxt.Text)

        Try
            da = new OleDbDataAdapter(cmd)
            da.Fill(ds,"lecturer")            
            ds.Tables("lecturer").Rows(i).Item(0) = empIdTxt.Text
            ds.Tables("lecturer").Rows(i).Item(1) = nameTxt.Text
            ds.Tables("lecturer").Rows(i).Item(2) = positionComboBox.Text
            ds.Tables("lecturer").Rows(i).Item(3) = facultyComboBox.Text
            ds.Tables("lecturer").Rows(i).Item(4) = empDeg1.Text
            ds.Tables("lecturer").Rows(i).Item(5) = empDeg2.Text
            ds.Tables("lecturer").Rows(i).Item(6) = empDeg3.Text
            ds.Tables("lecturer").Rows(i).Item(7) = empDeg4.Text
            ds.Tables("lecturer").Rows(i).Item(8) = empTypeComboBox.Text
            ds.Tables("lecturer").Rows(i).Item(9) = icTxt.Text
            ds.Tables("lecturer").Rows(i).Item(10) = citizenshipComboBox.Text
            ds.Tables("lecturer").Rows(i).Item(11) = phoneTxt.Text
            ds.Tables("lecturer").Rows(i).Item(12) = emailTxt.Text
            ds.Tables("lecturer").Rows(i).Item(13) = permitNoTxt.Text
            ds.Tables("lecturer").Rows(i).Item(14) = DateTimePicker1.Value
            ds.Tables("lecturer").Rows(i).Item(15) = DateTimePicker2.Value
            ds.Tables("lecturer").Rows(i).Item(16) = statusComboBox.Text
            ds.Tables("lecturer").Rows(i).Item(17) = remark.Text
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            da.Update(ds, "lecturer")
            ds.AcceptChanges()
            myConnection.Close()
            MsgBox("Record Updated")
        Catch ex As Exception
            MessageBox.Show(ex.Message & "-" & ex.Source)
        End Try

    End Using
End Sub

i tried the above one but it give error, OleDbCommand need to set all parameter.... — bloom, Apr 19 '16 at 06:55

UPDATE statement in VB connecting to Access

1 Answers1