none
Help Please w/Commandbuilder Sytax Error Insert Into Statement RRS feed

  • Question

  •  

    Hello Forum,

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

    I Use the following application configuaration:

    Visual Basic 2005
    Untyped dataset with unbound data.
    MS Access 2003 Database (draft.mdb) no database security.
    All Database fields are set to Access Data Type "TEXT", except for the ID field (Primary Key - auto number, integer)

    I create my objects like this:

            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:

    
            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 correspond with same naming in the MS Access DB):

    
            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:

    
            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 accepts new data. When done I press the Update Dataset Button and the following code is executed:

    
            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 it perfectly cancels the updated record in the dataset.

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

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


    While at line:
    
    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
    Sunday, February 3, 2008 4:47 AM

Answers

  • You are getting this error because most likely some of the column names in your INSERT SQL statement are reserved word. I believe it could be Identity column that causes that error. To resolve the issue you need to put square brackets around all your fields and table name. To do it automatically you need to set QuotePrefix and QuoteSuffix properties of OleDbCommandBuilder, like

     

    MyOleDbCommandBuilder.QuotePrefix="["

    MyOleDbCommandBuilder.QuoteSuffix="]"

     

    After your INSERT will be generated by command builder, it will look like the following one and should work fine

     

    INSERT INTO [PersonalData] ([PersNumber], [Fname], [Lname], [Identity], [City], [Address], [HouseNumber], [Zip], [Phone], [Mobile], [Dob,] [FatherName]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

     

    Monday, February 4, 2008 10:49 AM
    Moderator
  • Basically square brackets required in a case if any table or column name contains unusual characters, which are not supported by standard identifiers (like spaces, percent etc.) or in a case if name is a reserved work in a database. It will allow database engine to distinct those names from standard one and process SQL statements accordingly.
    You do not need to use square brackets inside of your code to work with the actual columns, but need to construct valid SQL statement to execute it without error. In this case you have two choices:

    1. Assign explicit SQL statements inside of you code where you put square brackets around column and table names

    2. Let CommandBuilder class to generate SQL statements for you, but in this case you need to instruct it to add something around all the names, and this something defined by QuotePrefix, which added before the name, and QuoteSuffix, which appended after each name. In this case if you have following SQL statement

     

    Select * FROM PersonalData

     

    and have next code

     

    cbPersonalData..QuotePrefix="["
    cbPersonalData..QuoteSuffix="]"

     

    then CommandBuilder will generate INSERT SQL statement (and other one) like

     

    INSERT INTO [PersonalData] ([MyColumnnameHere], [MyAnotherColumnNamehere],.........

     

    You do not need to add anything in a binding.

     

    Tuesday, February 5, 2008 12:06 AM
    Moderator

All replies

  • Hello,

     

    I'm not sure but i think you have not written your code in the right order .

    I suggest :

    Dim daDraft As New OleDbDataAdapter()

    daDraft.SelectCommand = new OleDbCommand(sPersonnalDataSelect,conDraft)

    Dim cbPersonalData As New OleDb.OledbCommandBuilder(daDraft)

    Try

         conDraft.Open()

         daDraft.Fill(dsDraft,"PersonalData')

         ' put all the code for updating your rows, or insert your rows

        daDraft.Update(dsDraft,""PersonalData")

         conDraft.Close()

    Catch ( err As OleDb.OleDbException )

         MessageBox.Show(err.Message,"MS Access error update 1")   ' refining to personalize each error

    End Try

     

     

    A small advice : put an Imports System.Data.OleDb at the right beginning of your code file.

    So you would not be obliged to repeat OleDb. before everything relative to OleDb

    Small code and more easy for reading

     

    I hope i have not done too many mistakes with your personal variables names

     

    I hope that we help you

     

    Have a nice day

     

    Sunday, February 3, 2008 11:13 AM
  • Dear Papy,

     

    Thank you for taking the time to help me with my problem.

    I re-aranged my code to your suggestion (Although Data Adapters make their own connection - no need to tell them to do that) but I inserted it (got connection was not closed error)... so without the con.open and con.close I ran the program but I still have the same problem.

     

    Syntax Error Insert Into Statement

    Could it be that the Insert Command is not generated by the OledbDatadapter (a bug?).

     

    - Tsadok

     

     

    Sunday, February 3, 2008 12:44 PM
  • Hello,

     

    Could you post the InsertCommand generated by the OleDbCommandStringBuilder

    see the link :

    http://msdn2.microsoft.com/en-us/library/4d6a7dkd(VS.80).aspx

     

    Dim cmd As OleDbCommand = cmbPersonnalData.GetInsertCommand()

    MessageBox.Show(cmd.CommandText,"Text of the generated InsertCommand)

     

    Put this code just before the adapter.fill()  Sorry i don't remember the name of your variables

     

    I'm waiting to see what's happening with this Insert Command

     

    Another thing : could you test this command with Sql Server Management Studio ?

    Maybe you will have more explanations

     

    Sorry to be late i've just finished my lunch.

    Have a nice day

    Sunday, February 3, 2008 1:37 PM
  •  

    Dear Papy,

    Again thank you for your patience with me.

     

    I changed the message box into console.writeline and this is the result..

    (No P@'s) so it looks good?

     

    INSERT INTO PersonalData (PersNumber, Fname, Lname, Identity, City, Address, HouseNumber, Zip, Phone, Mobile, Dob, FatherName) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

     

    - Tsadok

    Sunday, February 3, 2008 3:17 PM
  • Hello,

     

    For me , the Insert Command is correct, but i'm not a specialist of Jet

    I will plunge in Access documentation

     

    Have a nice day

     

    Sunday, February 3, 2008 4:45 PM
  • You are getting this error because most likely some of the column names in your INSERT SQL statement are reserved word. I believe it could be Identity column that causes that error. To resolve the issue you need to put square brackets around all your fields and table name. To do it automatically you need to set QuotePrefix and QuoteSuffix properties of OleDbCommandBuilder, like

     

    MyOleDbCommandBuilder.QuotePrefix="["

    MyOleDbCommandBuilder.QuoteSuffix="]"

     

    After your INSERT will be generated by command builder, it will look like the following one and should work fine

     

    INSERT INTO [PersonalData] ([PersNumber], [Fname], [Lname], [Identity], [City], [Address], [HouseNumber], [Zip], [Phone], [Mobile], [Dob,] [FatherName]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

     

    Monday, February 4, 2008 10:49 AM
    Moderator
  • Dear VMazur,

     

    This sound pretty likely - never thought about this - thank you. Please forgive my ignorance but can you elaborate a little more on the how to achieve this. Do I understand it right (that you suggest) that where ever I refer to column names in my application - that I must use e.g. [PersNumber]?

     

    Or should I use your suggested statement Oledb.OledbCommandBuilder.QuotePrefix="[" somewhere during creating the ADO objects? E.g.:

     

    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 * FROM PersonalData"

    cmdPersonalData.CommandText = sPersonalDataSelect

    daDraft.SelectCommand = New OleDb.OleDbCommand(sPersonalDataSelect, conDraft)

    Dim cbPersonalData As New OleDb.OleDbCommandBuilder(daDraft)

    cbPersonalData..QuotePrefix="["

    cbPersonalData..QuoteSuffix="]"

     

    ...and then use the brackets in the binding like this:

    txtPersNumber.DataBindings.Add("Text", dsDraft, "[PersonalData.PersNumber]")

    txtFname.DataBindings.Add("Text", dsDraft, "[PersonalData.Fname]")

     

    If I got it all wrong, could you please point it out into detail when you have a few spare minutes for me. Your help is greatly appreciated!

     

    - Tsadok 

     

     

    Monday, February 4, 2008 5:39 PM
  • Basically square brackets required in a case if any table or column name contains unusual characters, which are not supported by standard identifiers (like spaces, percent etc.) or in a case if name is a reserved work in a database. It will allow database engine to distinct those names from standard one and process SQL statements accordingly.
    You do not need to use square brackets inside of your code to work with the actual columns, but need to construct valid SQL statement to execute it without error. In this case you have two choices:

    1. Assign explicit SQL statements inside of you code where you put square brackets around column and table names

    2. Let CommandBuilder class to generate SQL statements for you, but in this case you need to instruct it to add something around all the names, and this something defined by QuotePrefix, which added before the name, and QuoteSuffix, which appended after each name. In this case if you have following SQL statement

     

    Select * FROM PersonalData

     

    and have next code

     

    cbPersonalData..QuotePrefix="["
    cbPersonalData..QuoteSuffix="]"

     

    then CommandBuilder will generate INSERT SQL statement (and other one) like

     

    INSERT INTO [PersonalData] ([MyColumnnameHere], [MyAnotherColumnNamehere],.........

     

    You do not need to add anything in a binding.

     

    Tuesday, February 5, 2008 12:06 AM
    Moderator
  • Dear VMazur,

     

    Awesome!!!

    It works. I am really gratefull for your time and for your solution. Your explanation positively contributed to my hopefully short VB learning curve. A simple answer would not have made me understand why this is so important. Thank you so much!

     

    - Tsadok

     

    Tuesday, February 5, 2008 3:01 AM