Help With CommandBuilder Error?

tsadok · February 2nd, 2008, 11:03 AM

Hello Forum,

I have a small problem with my command builder (I think). Any help appreciated.

I Use the following application configuaration:
Untyped Dataset with unbound data.
MS Access 2003 Database (draft.mdb)
All Database fields are set to Access Data Type "TEXT", except for the ID field (Primary Key - Autonumber, Integer)

I create my objects like this:

Code:

        Dim LoadDataPath As String
        LoadDataPath = MdbFileName
        Dim sDraftConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & LoadDataPath
        conDraft.ConnectionString = sDraftConnection

        Dim cmdPersonalData As New OleDb.OleDbCommand
        cmdPersonalData.Connection = conDraft
        Dim sPersonalDataSelect As String = "Select PersonalData.* FROM PersonalData"
        cmdPersonalData.CommandText = sPersonalDataSelect
        daDraft.SelectCommand = cmdPersonalData
        Dim cbPersonalData As New OleDb.OleDbCommandBuilder()
        cbPersonalData.DataAdapter = daDraft

I Fill my tables with the following Code:

Code:

        Try
            conDraft.Open()
            daDraft.Fill(dsDraft, "PersonalData")
            conDraft.Close()
        Catch eOleDb As OleDb.OleDbException
            MessageBox.Show(eOleDb.Message, "Ms Access Error")
        End Try

I bind my controls like this (All database fields are correctly named and corres[ond with same naming in the MS Access DB):

Code:

        With cboMagnetic
            .DataSource = dsDraft.Tables("PersonalData")
            .DisplayMember = "PersNumber"
        End With
        txtPersNumber.DataBindings.Add("Text", dsDraft, "PersonalData.PersNumber")
        txtFname.DataBindings.Add("Text", dsDraft, "PersonalData.Fname")
        txtLname.DataBindings.Add("Text", dsDraft, "PersonalData.Lname")
        txtIdentity.DataBindings.Add("Text", dsDraft, "PersonalData.Identity")
        txtMobile.DataBindings.Add("Text", dsDraft, "PersonalData.Mobile")
        txtFatherName.DataBindings.Add("Text", dsDraft, "PersonalData.Fathername")
        txtAddress.DataBindings.Add("Text", dsDraft, "PersonalData.Address")
        txtHouseNumber.DataBindings.Add("Text", dsDraft, "PersonalData.HouseNumber")
        txtCity.DataBindings.Add("Text", dsDraft, "PersonalData.City")
        txtZip.DataBindings.Add("Text", dsDraft, "PersonalData.Zip")
        txtHomePhone.DataBindings.Add("Text", dsDraft, "PersonalData.Phone")
        dtpDob.DataBindings.Add("Text", dsDraft, "PersonalData.Dob")

Here is the problem:
Everything works fine in my application except for when I add a new row. I click the add new record button and the following code is excuted:

Code:

        bmbdraft.AddNew()
        Me.SetMaintenanceButtons(False)
        Me.SetAdminAddRecordButtons(False)
        tsBtnUpdate.Enabled = False
        bNewRow = True
        picBox.Image = Nothing
        txtPersNumber.Enabled = True
        txtIdentity.Enabled = True
        txtPersNumber.Focus()

All fields are nicely cleared (also a picture as you can see) and the form nicely accepts new data. When done I press the Update Dataset Button and the following code is executed:

Code:

        If ValidData() Then
            bmbdraft.EndCurrentEdit()
            If bNewRow Then
                cboMagnetic.SelectedIndex = bmbdraft.Count - 1
                bNewRow = False
            End If
            Me.SetMaintenanceButtons(True)
            tsBtnUpdate.Enabled = True
            txtMagnetic.Select()
        End If

The dataset is updated accordingly.
Note that if I press the cancel button after updating the dataset I perfectly cancels the updated record in the dataset.

Now when I press tsBtnUpdate (which updates the database) the following code is executed:

Code:

            daDraft.Update(dsDraft.Tables("PersonalData"))
            dsDraft.Tables("PersonalData").Clear()
            daDraft.Fill(dsDraft, "PersonalData")

While at line:

Code:

daDraft.Update(dsDraft.Tables("PersonalData"))

I get an error message saying:
Syntax Error in the INSERT INTO Statement

I debugged the program by putting a breakpoint at the line causing the error (above) and when hoovering over the commandbuilder it states that the Insert Command = ""

Anyone an Idea what I am doing wrong?
Thank you in Advance

- Tsadok