John John - 3 months ago 14
Vb.net Question

My code seems to merge datasets but it does not update to the access DB

I am trying to merge two tables and update one table with the merged records from two separate MS access databases

Both tables are identical and both tables are password protected with auto number primary key

The following code merges the datasets memory, or at least by the msgbox messages the record count is correct.

But it does not update to the table in the access database..

I have searched the net long and hard and am really at a loose end..

daDBTarget = New OleDbDataAdapter
daDBTarget.SelectCommand = New OleDbCommand("SELECT * FROM DBUSERTASK", Conn1)
daDBTarget.Fill(dsDBTarget, "tbl1")

daDBSource = New OleDbDataAdapter
daDBSource.SelectCommand = New OleDbCommand("SELECT * FROM DBUSERTASK", conn2)
daDBSource.Fill(dsDBSource, "tbl2")

MsgBox("Dataset 1 dstTaskComp is full with " & dsDBTarget.Tables(0).Rows.Count & vbCr & vbCr & "Dataset 2 dstTaskComp is full with " & dsDBSource.Tables(0).Rows.Count)
dsDBTarget.Tables("tbl1").Merge(dsDBSource.Tables("tbl2"), True)

MsgBox("Dataset 1 dstTaskComp is now merged and full with " & dsDBTarget.Tables(0).Rows.Count)

dsDBTarget.AcceptChanges()

Dim cb As New OleDbCommandBuilder(daDBTarget)

daDBTarget.Fill(dsDBTarget)
cb.GetUpdateCommand()
daDBTarget.Update(dsDBTarget)

Answer

There are a couple of details I dont know. For instance, if the source/master has deleted rows which are not (yet) deleted in the destination, should they be deleted as part of the merge? Since they do not exist in the Source, nothing will happen to them in the process unless you handle it manually. I ignored that aspect since it was not mentioned.

The key element to know is that each row has a RowState indicating -- what if anything -- the DataAdapter will do with each row. There are some key steps elements wrong or missing:

  • When a DataAdapter fills a table, it sets all the row states to Unchanged. In order for the source rows to be eligible for insert/update to the Dest, you'd want them to have a RowState of Added.
  • In order to prevent simply adding copies (with new Ids), you need to also load the schema, so it can compare the PK and know how to handle new rows.
  • When you merged, you use PreserveChanges = True which tells OleDB to ignore column level changes in the source.

Not related, but important is to dispose of DB Provider objects when you are done with them. This will merge 2 tables from the same DB, so the same connection can be used, but otherwise illustrates the process. I also skipped the DataSets as they are not essential.

Dim SQLa = "SELECT * FROM MergeA"
Dim SQLb = "SELECT * FROM MergeB"
Dim dtSrc As New DataTable
Dim dtDest As New DataTable

Using dbcon As New OleDbConnection(ACEConnStr)
    Using da As New OleDbDataAdapter(SQLa, dbcon)

        dbcon.Open()
        ' Do Not set the RowState to UnChanged, please
        da.AcceptChangesDuringFill = False
        da.Fill(dtSrc)
    End Using

    Using da As New OleDbDataAdapter(SQLb, dbcon)
        Dim cb = New OleDbCommandBuilder(da)

        ' same connection, already open

        ' load the schema to get the PK etc
        da.FillSchema(dtDest, SchemaType.Source)
        da.Fill(dtDest)

        ' merge the tables
        dtDest.Merge(dtSrc, False, MissingSchemaAction.Add)
        Dim rows = da.Update(dtDest)

    End Using    ' close and dispose
End Using

The results:

enter image description here

  • XRay was in the source, but not the dest, so it was added.
  • All the RED items were that way in the Source only, so the column data was updated in the destination as is usually desired.
  • There are 3 new rows in the Source which were added (not shown)

Perhaps more important is that item 2 - Uniform exists in the Destination but had been deleted in the Source. The DataAdapter wont remove it because it is there and the code didnt change the RowState to Deleted. Merging will accumulate rows and column data changes depending on the options used, but removing deleted rows is more like a synch operation. It is pretty simple to incorporate though.