Merging updates based on an oledbAdapter with Autonumber Primary key (constraint problem)

myForm.BindingContext(myDataSet, myDataSetTableName).EndCurrentEdit()
        Dim dsChanges As New DataSet()
        dsChanges = myDataSet.GetChanges
        If Not dsChanges Is Nothing Then
            Try
                myConnection.Open()
                myAdapter.Update(dsChanges, myDataSetTableName).
            Catch updateException As System.Exception
                MsgBox("Update error: " & updateException.GetBaseException.Message & vbCrLf & updateException.Message)
            Finally
                myConnection.Close()
            End Try
            Try
                myDataSet.Merge(dsChanges, True)
            Catch mergeException As System.Exception
                MsgBox("Merge error: " & mergeException.Message)
                Dim eTable As DataTable
                Dim eRow As DataRow
                For Each eTable In myDataSet.Tables
                    If eTable.HasErrors Then
                        MsgBox("Error in " & eTable.TableName)
                        For Each eRow In eTable.Rows
                            If eRow.HasErrors Then
                                MsgBox("Row error: " & eRow.RowError)
                            End If
                        Next
                    End If
                Next
            End Try
            Try
                myDataSet.AcceptChanges()
            Catch acceptChangesException As System.Exception
                MsgBox("AcceptChanges error: " & acceptChangesException.GetBaseException.Message & vbCrLf & acceptChangesException.Message)
            End Try
        End If

I get a constraint error regarding my "ID" field, which is an autonumber primary key.
Is merge not smart enough to know that if I am merging rows (which represent changes) with the same primary key, they should accept the new rows into the dataset? How do I tell it to overwrite (or delete and insert) the merging rows over the existing rows in the case where there this particular column has unique constraint conflicts?

More generically:
Assuming you use getChanges on a dataset that has a primary key that is unique, how do you merge the changes back into the dataset so the user can see them?

Thanks!
majnunAsked:
Who is Participating?
 
PAQ_ManCommented:
Question Closed, 500 points refunded.
PAQ_Man
Community Support Moderator
0
 
rodmjayCommented:
What about a loop through query for the origional table with something like this

        Dim newdt As New DataTable ' A new datatable that will hold modified values
        Dim olddt As New DataTable ' Your old data table from your dataset

        Dim dr As DataRow
        For Each dr In olddt.Rows
            If dr.RowState = DataRowState.Modified Then
                newdt.ImportRow(dr)
                olddt.Rows.Remove(dr)
            End If
        Next

I think this tecnique should work nicely for what you want to do.  Instead of working with the entire table at a time, try working with individual rows.  It will give you a little more flexibility at least from my limited experience with VB.NET.  You will probably have to modify this code a bit, but I think that the concepts are here.
0
 
majnunAuthor Commented:
Cool idea... i wasn't able to do it so elegantly since i think vb bugs out when you dry to delete from a collection you are for eaching thru... but it inspired the following code which (seems) to work fine:

        Dim dsChanges As New DataSet()
        dsChanges = myDataSet.Clone
        Dim dr As DataRow
        Dim r As Integer = 0
        Dim removeRowIndexCollection As New Collection()
        For Each dr In myDataSet.Tables(myNavigationDataSetTableName).Rows
            If dr.RowState <> DataRowState.Unchanged And dr.RowState <> DataRowState.Detached Then
                If dr.RowState = DataRowState.Modified Then
                    dsChanges.Tables(myNavigationDataSetTableName).ImportRow(dr)
                    removeRowIndexCollection.Add(r)
                Else
                    dsChanges.Tables(myNavigationDataSetTableName).ImportRow(dr)
                End If
            End If
            r += 1
        Next
        For Each r In removeRowIndexCollection
            myDataSet.Tables(myNavigationDataSetTableName).Rows.RemoveAt(r)
        Next

... update dataset etc...
          Try
                myDataSet.Merge(dsChanges, True)
  ...

Any thoughts? Spot any problems? Is there a better, more elegant way to do this?

Thanks!
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
majnunAuthor Commented:
hmmm maybe spoke to soon... have some problems, i'll see if i can fix it...
0
 
majnunAuthor Commented:
I'm rethinking this approach...

I think I want to just merge everything as in the first try, and then remove the first of conflicting rows since it is the modified row... we'll see...

I'm still open to any solution that works.

Thanks.
0
 
majnunAuthor Commented:
Ok there is atcually no problem merging updates AS LONG AS YOU DEFINE A PRIMARY KEY FOR THE DATASET TABLE... which makes sense...

I forgot to explicitly set a primary key for the table in myDataset

once i did that, it had no problem merging the dataset of changes with myDataSet.

Incase anyone is wondering how to set the primary key for a dataset...

you do it like this:
Dim myColArray(1) As DataColumn
myColArray(0) = dataTable.Columns("CustID")
dataTable.PrimaryKey = myColArray

I think you use an array of columns incase you have a multi-column primary key, but in this case its an array of a single column.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.