title: VB.NET Merge multiple tables and then update MS Access db
body: <p>I am hitting an MS Access db with VB2005 and I am creating a table object like this</p>
  
  <pre><code>Dim sSQL As String = "SELECT * FROM tblCars WHERE WeekOf=#6/1/2011#"
  Dim da As New OleDb.OleDbDataAdapter(sSQL, conn)
  Dim ds As New DataSet
  da.Fill(ds, "CarData")
  Dim cb As New OleDb.OleDbCommandBuilder(da)
  </code></pre>
  
  <p>The table should have no rows since the data is brand new. I read a data text file with new data and pump this data into the table and add new rows like so:</p>
  
  <pre><code>'create a new empty row
  Dim dsNewRow As DataRow = ds.Tables("CarData").NewRow()
  
  'update the new row with fresh data
  dsNewRow.Item("DriveDate") = dd
  dsNewRow.Item("DCode") = dc
  dsNewRow.Item("CarNum") = carID
  'about 15 more fields
  
  'add the filled row to the DataSet table
  ds.Tables("CarData").Rows.Add(dsNewRow)
  
  'end loop
  
  'update the database with the new rows
  da.Update(ds, "CarData")
  </code></pre>
  
  <p>The table in the db has no primary keys but all this works and i get all my new data inserted into the Data table tblCars.</p>
  
  <p>Now it so happens that I must also account for cars with no data. For that my plan was to get a list of cars from the control table and merge it with the Data table.</p>
  
  <pre><code>    Dim sSQL As String = "SELECT CarId FROM tblControlCars WHERE ActiveCar=True;"
      Dim daAct As New OleDb.OleDbDataAdapter(sSQL, conn)
      Dim ds As New DataSet
      daTail.Fill(ds, "ActCars")
      Dim cbAct As New OleDb.OleDbCommandBuilder(daAct)
  </code></pre>
  
  <p>I then create my primary keys for the tables and then merge them.</p>
  
  <pre><code>    ds.Tables("CarData").Merge(ds.Tables("ActCars"))
  </code></pre>
  
  <p>This will give me a table with all the new data for cars that do have data and also a place holder for those cars that are active but did not get any data. However, when I try to do an update back to the db I get an error</p>
  
  <blockquote>
    <p>System.InvalidOperationException: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.</p>
  </blockquote>
  
  <p>I'm not sure I understand why the Merged data is not able to be pushed to the database.  </p>