Use Multiple DataTables in one DataSet to Insert new records in Access DB

▲
0
▼

VB2010 I have one DataSet and I add multiple tables, I then fill in these tables, and then insert those records into an Access db. 

    'create a new DataSet
    Dim dsNav As New DataSet

    'first table
    Dim daTrips As New OleDb.OleDbDataAdapter("SELECT * FROM Trips", connNav)
    daTrips.Fill(dsNav, "Trips")
    Dim cbTrips As New OleDb.OleDbCommandBuilder(daTrips)

   'second table
    Dim daCars = New OleDb.OleDbDataAdapter("SELECT * FROM Cars", connNavDb)
    daCars.Fill(dsNav, "Cars")
    Dim cbCars As New OleDb.OleDbCommandBuilder(daCars)

    'here i open a huge text file and depending on the data i encounter, I create
    'a new DataRow and add it to the appropriate table. i add many new rows to each
    'table. for example
    Dim dsNewRow As DataRow = {tblCars}.NewRow()
    dsNewRow.Item("CarId") = textline.Substring(0, 10)
    dsNewRow.Item("CarMake") = textline.Substring(11, 15)
    tblCars.Rows.Add(dsNewRow)

    'i finish reading the text file and filling up the tables in the one DataSet
    'now i want to insert those records into the Access db
    Dim rowCnt1 As Integer = daTrips.Update(dsNav, "Trips")
    Dim rowCnt2 As Integer = daCars.Update(dsNav, "Cars")

The first update works but on the second update I get the exception:

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll System.Data.OleDb.OleDbException (0x80040E14): Syntax error in INSERT INTO statement. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)

I've looked at various articles and they all suggest updating a database with one DataSet containing multiple DataTables is do-able, but just cant figure out why this is bombing.

vb.net
ms-access
datatable
dataset
oledb
asked on Stack Overflow Jun 29, 2012 by sinDizzy • edited May 3, 2019 by Cœur

2 Answers

▲
0
▼

Where are you setting up the insertcommand and the updatecommand properties of your dataadapter?

From your error it looks like the dataadapter is trying to insert the record into the database but the insertcommand isn't instantiated or given a value so its throwing the error.

Since data adapters decide what to do on the .Update call by examining each row in the datatables rowstate then any rows that you add to your table will try to use the insertcommand attached to your dataadapter.

Try setting up the insertcommand and updatecommand on your dataadapters and try running it again and see what happens from there.

answered on Stack Overflow Jun 29, 2012 by Mike_OBrien
▲
0
▼

I wish to put everything discovered in comments as an answer

  1. Try to avoid fields using reserved keywords in your tables (also fields with spaces embedded)
  2. CommandBuilders needs primary key infos, so add a adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey to recover primary key informations from the db schema

  3. Use the same connection for the Update as for the Fill

    Dim dsNav As New DataSet 
'first table 
Dim daTrips As New OleDb.OleDbDataAdapter("SELECT * FROM Trips", connNav) 
daTrips.MissingSchemaAction = MissingSchemaAction.AddWithKey 
daTrips.Fill(dsNav, "Trips") 
Dim cbTrips As New OleDb.OleDbCommandBuilder(daTrips) 

'second table 
Dim daCars = New OleDb.OleDbDataAdapter("SELECT * FROM Cars", connNav) 
daCars.MissingSchemaAction = MissingSchemaAction.AddWithKey 
daCars.Fill(dsNav, "Cars") 
Dim cbCars As New OleDb.OleDbCommandBuilder(daCars) 

'here i open a huge text file and depending on the data i encounter, I create 
'a new DataRow and add it to the appropriate table. i add many new rows to each 
'table. for example 
Dim dsNewRow As DataRow = {tblCars}.NewRow() 
dsNewRow.Item("CarId") = textline.Substring(0, 10) 
dsNewRow.Item("CarMake") = textline.Substring(11, 15) 
tblCars.Rows.Add(dsNewRow) 

'i finish reading the text file and filling up the tables in the one DataSet 
'now i want to insert those records into the Access db 
Dim rowCnt1 As Integer = daTrips.Update(dsNav, "Trips") 
Dim rowCnt2 As Integer = daCars.Update(dsNav, "Cars")
answered on Stack Overflow Jul 1, 2012 by Steve • edited Jul 2, 2012 by Steve

User contributions licensed under CC BY-SA 3.0