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

Question

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.

Answer 1

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")