saving merged dataset to database

[JohnBeton]

Hello,

I have the following problem:
I have 2 access databases: 1 for administration, 1 for production. This 2 databases have the exact same table: 'orderDetail'.
When new orders arrive, the new orderDetails from the administrationDB and existing orderDetails from the productionDB are merged in the production-dataset and should be saved in the production-DB.
The merging works, but the dataAdapter doesn't save the new data in the database:

my code:

    Public Function updateProductionData(ByVal orderIds As String)
        Dim admCnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=//Server/personal/NRW2.mdb"
        Dim prodCnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=//Server/personal/NRW_productie.mdb"

        Dim admCN As OleDbConnection = New OleDbConnection(admCnString)
        Dim prodCN As OleDbConnection = New OleDbConnection(prodCnString)

        Dim admDA As OleDbDataAdapter = New OleDbDataAdapter("Select * from tblOrderDetail where orderId in (" & orderIds & ")", admCN)
        Dim prodDA As OleDbDataAdapter = New OleDbDataAdapter("Select * from tblOrderDetail", prodCN)

        Dim admDS As DataSet = New DataSet
        Dim prodDS As DataSet = New DataSet

        Dim cb As OleDbCommandBuilder
        Try
            admCN.Open()
            admDA.Fill(admDS, "orderDetail")
            admCN.Close()

            prodCN.Open()
            prodDA.Fill(prodDS, "orderDetail")
            prodCN.Close()

            cb = New OleDbCommandBuilder(prodDA)

            debug.writeLine(prodDS.Tables(0).Rows.Count)

            prodDS.Merge(admDS, False)

            debug.writeLine(prodDS.Tables(0).Rows.Count)
            prodDS.AcceptChanges()
            prodDA.Update(prodDS, "orderDetail")
        Catch ex As Exception

        End Try
    End Function

before the merge the table in prodDS has 3 rows, after merging, it has 8 rows, so no problem with .merge

I don't get any errors, the update just doesn't happen.
If i don't use the commandBuilder, i don't get any errors either, even though i don't have a valid updatecommand

a little help is much appreciated
Thanks already...

 

[jebenson]

The problem is that you are calling AcceptChanges before calling Update:

prodDS.AcceptChanges()
prodDA.Update(prodDS, "orderDetail")

This causes the DataSet to accept any changes, which means that when update is called, the DataAdapter finds no changes in the data and so "thinks" there is nothing to update. Change your code to this:

prodDA.Update(prodDS, "orderDetail")
prodDS.AcceptChanges()

and everything should work.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[JohnBeton]

Hello Jebenson,

Thanks for your reply,

But I tried your suggestion, and it is still the same:
no errors, but no updates to database neither.

any other suggestions ?

 

[jebenson]

Try setting the PreserveChanges parameter of the Merge method to True:

prodDS.Merge(admDS, [red]True[/red])

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[JohnBeton]

I changed the parameter but that doesn't make any difference...

But i made the following test:

The number of rows in the table of the dataset before merging : 5
The number of rows in the table of the dataset after merging : 10

So there are 5 new rows in this table.

When I run through each row of this table, and check the rowstate, each row, including the new rows, have a rowstate: unchanged.

So it's normal that the db is not updated, because the dataAdapter 'thinks' there is nothing to update.
But how come the new rows have a rowstate unchanged instead of added ?
does merge automatically do an 'acceptChanges' ?

Can anyone help me please ?

Thanks in advance