none
Run time error in Update statement using Visual studio 2008 RRS feed

  • Question

  • i linked Microsoft access database in my visual studio project. When i click the update button, it keeps on displaying "Syntax error in update statement". whats wrong? I really need this asap. I'm sick with this!

    here is my code:

    Public Class Form1
        Dim inc As Integer
        Dim MaxRows As Integer
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String

        Private Sub TblContactsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblContactsBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.TblContactsBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.AddressBookDataSet)

        End Sub

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'AddressBookDataSet.TblContacts' table. You can move, or remove it, as needed.
            Me.TblContactsTableAdapter.Fill(Me.AddressBookDataSet.TblContacts)


            dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
            dbSource = "Data Source = C:\Documents and Settings\Owner\My Documents\InfoSystem\InfoSystem\AddressBook.mdb"

            con.ConnectionString = dbProvider & dbSource

            con.Open()

            sql = "SELECT * FROM tblContacts"
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "AddressBook")

            con.Close()

            MaxRows = ds.Tables("AddressBook").Rows.Count
            inc = -1
        End Sub

        Private Sub NavigateRecords()

            txtFirstName.Text = ds.Tables("AddressBook").Rows(inc).Item(1)
            txtSurname.Text = ds.Tables("AddressBook").Rows(inc).Item(2)
            txtAge.Text = ds.Tables("AddressBook").Rows(0).Item(3)
            txtCompanyName.Text = ds.Tables("AddressBook").Rows(0).Item(4)
            txtTelNo.Text = ds.Tables("AddressBook").Rows(0).Item(5)
            txtWifeName.Text = ds.Tables("AddressBook").Rows(0).Item(6)
            txtWifesContact.Text = ds.Tables("AddressBook").Rows(0).Item(7)
            txtDateOfCall.Text = ds.Tables("AddressBook").Rows(0).Item(8)
            txtWS.Text = ds.Tables("AddressBook").Rows(0).Item(9)
            txtFreq.Text = ds.Tables("AddressBook").Rows(0).Item(10)
            txtCname.Text = ds.Tables("AddressBook").Rows(0).Item(11)
            txtWhoCalled.Text = ds.Tables("AddressBook").Rows(0).Item(12)
            txtTimeIn.Text = ds.Tables("AddressBook").Rows(0).Item(13)
            txtTimeOut.Text = ds.Tables("AddressBook").Rows(0).Item(14)
            txtTotalMin.Text = ds.Tables("AddressBook").Rows(0).Item(15)
            txtCharges.Text = ds.Tables("AddressBook").Rows(0).Item(16)

        End Sub
       
        Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
            If inc <> MaxRows - 1 Then
                inc = inc + 1
                NavigateRecords()
            Else
                MsgBox("No More Rows")
            End If
        End Sub

        Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
            If inc > 0 Then
                inc = inc - 1
                NavigateRecords()
            Else
                MsgBox("First Record")
            End If
        End Sub

        Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
            If inc <> MaxRows - 1 Then
                inc = MaxRows - 1
                NavigateRecords()
            End If


        End Sub

        Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
            If inc <> 0 Then
                inc = 0
                NavigateRecords()
            End If
        End Sub

        Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
            Dim cb As New OleDb.OleDbCommandBuilder(da)

            ds.Tables("AddressBook").Rows(inc).Item(1) = txtFirstName.Text
            ds.Tables("AddressBook").Rows(inc).Item(2) = txtSurname.Text
            ds.Tables("AddressBook").Rows(inc).Item(3) = txtAge.Text
            ds.Tables("AddressBook").Rows(inc).Item(4) = txtCompanyName.Text
            ds.Tables("AddressBook").Rows(inc).Item(5) = txtTelNo.Text
            ds.Tables("AddressBook").Rows(inc).Item(6) = txtWifeName.Text
            ds.Tables("AddressBook").Rows(inc).Item(7) = txtWifesContact.Text
            ds.Tables("AddressBook").Rows(inc).Item(8) = txtDateOfCall.Text
            ds.Tables("AddressBook").Rows(inc).Item(9) = txtWS.Text
            ds.Tables("AddressBook").Rows(inc).Item(10) = txtFreq.Text
            ds.Tables("AddressBook").Rows(inc).Item(11) = txtCname.Text
            ds.Tables("AddressBook").Rows(inc).Item(12) = txtWhoCalled.Text
           

            da.Update(ds, "AddressBook")
            MsgBox("Data updated")
        End Sub
    End Class

    Tuesday, June 12, 2012 8:36 AM

Answers