after datatable merge, the tableadapter.update method returns 0

Question

I have tried so many ways to get my database to update my added rows but with no luck. This is what I have:

• 2x databases (local and remote)
• 2x data tables in their own datasets
• the local database has 3 extra columns (1 nchar(15) and 2 bit all NULLABLE)
• 2x separate table adapters and bindingsource links to datagridviews (dgv only for referencing)
• 1x insanely large headache

what I need to do is populate dgv1 with localDB table, then look at the last invoice number in the table for the starting point in the remoteDB to populate dgv2. using the data added in both datatables, I then merge them into the localDB dataset and calling me.MasterInvoiceTableAdapter.Update(me.MasterInvoiceDataSet.MasterInvoices) should save the added rows to the database...

then I populate the datatables again. It looks all fine in the dgv's but exiting the form and loading again reveals no actual update was done. The return code on the update method also returns 0 validating that no lines were affected. code found below:

Try
        If stats2 = False Then
            Me.MasterInvoicesTableAdapter.Fill(Me.MasterInvoicesDataSet.MasterInvoices)
        Else
            Me.MasterInvoicesTableAdapter.GetData()
        End If

        Dim rowcount As Integer = DataGridView1.RowCount - 1
        Dim lookup As String = Trim(DataGridView1.Item(0, rowcount - 1).Value)
        MsgBox(lookup)
        Me.SorMasterTableAdapter.Fill(Me.InvoicesFDataSet.SorMaster, lookup)
        MasterInvoicesDataSet.MasterInvoices.Merge(InvoicesFDataSet.SorMaster, True)
        InvoicesFDataSet.SorMaster.Clear()
    Catch ex As Exception
        MsgBox(ex.Message)
        TextBox1.Text = ex.Message
    End Try
    Try
        Me.Validate()
        Me.MasterInvoicesBindingSource.EndEdit()
        MsgBox(Me.MasterInvoicesTableAdapter.Update(Me.MasterInvoicesDataSet.MasterInvoices))
        stats = True
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

fyi. the stats variable is use to control a refresh button behaviour.

I've tried looping through the dataset and adding the datarows, one-by-one (newrow with row.add, importrow, item.add), I've tried working with a linkedDB query to do all of it at once (with this I found that the collation was not the same on the DB's), and a few other methods not even worthy of mentioning.

Any advise?

Answer 1

I fixed my problem by doing some research on the merge method and found that the rowstate does not always change to Added on extra rows, this was fixed by adding For Each drow As DataRow In InvoicesFDataSet.SorMaster drow.SetAdded() Next before merging the tables.

Hope this helps anyone that has been searching for an answer to an issue similar to this.