Click here to Skip to main content
15,886,100 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have recently create an application using Access Database. I use OLEDBConnection, OLEDBDataAdapter and DataSet. It's OK until Save function. When I write save function using OLEDBCommandBuilder and DataAdapter.Update, it shows Syntax Error in INSERT INTO statement. Why I got this error? I'm not understand. Please somebody help me!

Here's my code:
VB
Private Sub conSave()

    Dim con As OleDbConnection
    Dim conAdapter As OleDbDataAdapter
    Dim dbCommand As OleDbCommand
    Dim ds As New DataSet

    Dim cb As OleDbCommandBuilder

    con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\hp\Documents\dbLedger.mdb")
    con.Open()
    dbCommand = New OleDbCommand("SELECT * FROM tbItemList", con)
    conAdapter = New OleDbDataAdapter
    conAdapter.SelectCommand = dbCommand
    conAdapter.Fill(ds, "tbItemList")

    cb = New OleDbCommandBuilder(conAdapter)
    Dim dsNewRow As DataRow

    dsNewRow = ds.Tables("tbItemList").NewRow()

    With dsNewRow
        .Item("ID") = Val(lblID.Text)
        .Item("iName") = txtName.Text
        .Item("ItemType") = cbbItemType.Text
        .Item("UnitName") = txtUnit.Text
        .Item("SubunitName") = txtSubunit.Text
        .Item("DefaultUnit") = cbbDefaultUnit.Text
        .Item("Algorithm") = txtAlgorithm.Text
        .Item("Note") = txtNote.Text
    End With

    ds.Tables("tbItemList").Rows.Add(dsNewRow)

    conAdapter.Update(ds, "tbItemList")


    con.Close()
    con.Dispose()
    conAdapter.Dispose()
    dbCommand.Dispose()
    ds.Dispose()
    cb.Dispose()

    MsgBox("Save complete!")
End Sub
Posted
Updated 2-Oct-11 20:37pm
v2
Comments
Abhinav S 3-Oct-11 2:25am    
Please provide the appropriate code. There is on "Insert into" query here.
Firo Atrum Ventus 3-Oct-11 2:29am    
This code does the update:
ds.Tables("tbItemList").Rows.Add(dsNewRow)
conAdapter.Update(ds, "tbItemList")
Firo Atrum Ventus 3-Oct-11 2:27am    
Can you please post the exact error message?
And also the table structure, please.
agthumoe 3-Oct-11 3:30am    
My database file name is dbLedger.mdb
Database has 3 tables, named "tbItemList", "tbInput", "tbOutput"
tbItemList table has 8 fields named "ID", "iName", "ItemType", "UnitName", "SubunitName", "DefaultUnit", "Algorithm" and "Note" , where "ID" is primary key

VB.net shows error in "conAdapter.Update (ds, "tbItemList")
with error message "Syntax error in INSERT INTO statement"

1 solution

The CommandBuilder uses the SELECT statement string to write an UPDATE statement for it. What's screwing it up is the "SELECT *" portion. Don't use * to return all columns. Spell out the entire SELECT statement, specifying all the column names that you want returned. Then the UPDATE statement can be built.

There are a bunch more restrictions for using CommandBuilder, but from your code snippet, you're not hitting any of those.
 
Share this answer
 
Comments
Bala Selvanayagam 3-Oct-11 8:18am    
Also aung thu moe, may be if your "ID" column is set as autonumber then you would not .Item("ID") = Val(lblID.Text)
Dave Kreskowiak 3-Oct-11 11:26am    
That's what I get for answering questions so early in the morning. I missed that one.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900