?
Solved

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

Posted on 2005-04-14
8
Medium Priority
?
338 Views
Last Modified: 2010-04-23
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!
0
Comment
Question by:majnun
  • 4
6 Comments
 
LVL 8

Expert Comment

by:rodmjay
ID: 13788961
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
 

Author Comment

by:majnun
ID: 13792002
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
 

Author Comment

by:majnun
ID: 13792025
hmmm maybe spoke to soon... have some problems, i'll see if i can fix it...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:majnun
ID: 13792072
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
 

Author Comment

by:majnun
ID: 13792921
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
 

Accepted Solution

by:
PAQ_Man earned 0 total points
ID: 14024015
Question Closed, 500 points refunded.
PAQ_Man
Community Support Moderator
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month15 days, 21 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question