Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
| Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 2nd, 2008, 11:03 AM
Registered User
 
Join Date: Jan 2008
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help With CommandBuilder Error?

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Query Error & Run-Time Error 3022 DavidWE Access 1 July 31st, 2008 11:17 AM
Ch 4: Parse error: syntax error, unexpected T_SL hanizar77 BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 0 June 23rd, 2008 09:17 PM
Parse error: syntax error, unexpected T_STRING ginost7 Beginning PHP 1 November 9th, 2007 02:51 AM
VB Error: Syntax Error or Access Violation codehappy VB How-To 7 October 3rd, 2007 05:41 PM
Update a DataGridView without CommandBuilder sfx ADO.NET 1 August 4th, 2006 05:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.