Dataset Merge Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know exactly how the MERGE method on the dataset functions? The
reason that I am asking is that it does not appear to be doing what I expect,
and what the documentation claims will happen (if I am understanding it
correctly).

According to the following link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskmergingdatasets.asp

You can specify merge options to achieve a variety of goals:

Add new records to one dataset by copying them from another dataset.
Update the state of records in one dataset based on another. In this
situation, the second dataset contains information not only about new
records, but also changed records and deleted records, and you want your
first dataset to reflect all these changes.
Copy not only new records and changes, but specify what to do if the
datasets do not have the same schemas.

My problem is that it will not update changed records. It will only add new
records. I have included my code below. If anyone has a suggestion, I would
greatly appreciate it.

'Get SQL Table for each table in XML File
TableName = xmlDataSet.Tables(TableLoop).TableName
SQLText = "SELECT * FROM " & TableName
Dim SQLAdapter As SqlDataAdapter = New SqlDataAdapter
SQLAdapter.SelectCommand = New SqlCommand(SQLText, SQLConnect)
Dim SQLCmdBuild As SqlCommandBuilder = New SqlCommandBuilder(SQLAdapter)
sqlDataSet = New DataSet(TableName)
'Fill Dataset with SQL Table, and then merge in XML Table
SQLAdapter.ContinueUpdateOnError = True
SQLAdapter.Fill(sqlDataSet)
sqlDataSet.Merge(xmlDataSet)
SQLAdapter.Update(sqlDataSet, TableName)
 
I think your problem might be that the source data (new feed from XML) is
not flagged with changes or may not contain a primary key. Here is a quote
from MSDN about merging datasets that may be applicable:

"When merging a new source DataSet into the target, any source rows with a
DataRowState value of Unchanged, Modified, or Deleted, are matched to target
rows with the same primary key values. Source rows with a DataRowState value
of Added are matched to new target rows with the same primary key values as
the new source rows."

The link:
http://msdn.microsoft.com/library/d...ml/frlrfsystemdatadatasetclassmergetopic2.asp

So my question for you is does your XML dataset have primary keys in its
schema, if not you may want to consider adding the constraint. I think the
add works because there are no primary key collisions during your update, I
am assuming that the primary key is not something that is updated often as
part of the XML service. Take a look at the article and see if it helps.

Alex
 
That is usually the culprit: primary keys being different in the 2 datasets.
Check that out first. I just ran a test and found that when I do this:

oDs.Merge(oDtNewData)

If oDs does not have its primary key contraint set, all of the rows from
oDtNewData are added to oDs's DataTable. you can set the PK like this:

DataTable oDt = oDs.Tables["MyTable"]
oDt.PrimaryKey = new DataColumn[]{oDt.Columns["MyPKColumn"]};


John Papa
http://codebetter.com/blogs/john.papa



Alex Passos said:
I think your problem might be that the source data (new feed from XML) is
not flagged with changes or may not contain a primary key. Here is a quote
from MSDN about merging datasets that may be applicable:

"When merging a new source DataSet into the target, any source rows with a
DataRowState value of Unchanged, Modified, or Deleted, are matched to target
rows with the same primary key values. Source rows with a DataRowState value
of Added are matched to new target rows with the same primary key values as
the new source rows."

The link:
http://msdn.microsoft.com/library/d...ml/frlrfsystemdatadatasetclassmergetopic2.asp

So my question for you is does your XML dataset have primary keys in its
schema, if not you may want to consider adding the constraint. I think the
add works because there are no primary key collisions during your update, I
am assuming that the primary key is not something that is updated often as
part of the XML service. Take a look at the article and see if it helps.

Alex
Click to expand...
 
Sorry it took me so long to get back. I got pulled off into something else.

The XML does not have a schema with it. I used the example to set the
primary key on the datatable like this:

xmlDataColumn(0) = xmlDataTable.Columns(0)
xmlDataTable.PrimaryKey = xmlDataColumn

It works with no errors, but still does not update any existing items.
Regarding your second thought, that the XML datatable might not be flagged
with changes, that could very well be. It is a simple text/XML file with
ItemId,Description. No other information. How would I mark all of the
datarows in the table as changed?

Alex Passos said:
I think your problem might be that the source data (new feed from XML) is
not flagged with changes or may not contain a primary key. Here is a quote
from MSDN about merging datasets that may be applicable:

"When merging a new source DataSet into the target, any source rows with a
DataRowState value of Unchanged, Modified, or Deleted, are matched to target
rows with the same primary key values. Source rows with a DataRowState value
of Added are matched to new target rows with the same primary key values as
the new source rows."

The link:
http://msdn.microsoft.com/library/d...ml/frlrfsystemdatadatasetclassmergetopic2.asp

So my question for you is does your XML dataset have primary keys in its
schema, if not you may want to consider adding the constraint. I think the
add works because there are no primary key collisions during your update, I
am assuming that the primary key is not something that is updated often as
part of the XML service. Take a look at the article and see if it helps.

Alex
Click to expand...
 
Hi Jim,

To setup the background here is a quote from MSDN in regards to the merge
operation:

When merging a new source DataSet into the target, any source rows with a
DataRowState value of Unchanged, Modified, or Deleted, are matched to target
rows with the same primary key values. Source rows with a DataRowState value
of Added are matched to new target rows with the same primary key values as
the new source rows.

perhaps in the original data set you can do a call to AcceptChanges() which
will reset the datarow states to Unchanged, then load up your 2nd data set
and all the rows states should be Added, do AcceptChanges() on it as well
and then the Merge. See if that will work for you.



Alex



Sorry it took me so long to get back. I got pulled off into something
else.

The XML does not have a schema with it. I used the example to set the
primary key on the datatable like this:

xmlDataColumn(0) = xmlDataTable.Columns(0)
xmlDataTable.PrimaryKey = xmlDataColumn

It works with no errors, but still does not update any existing items.
Regarding your second thought, that the XML datatable might not be flagged
with changes, that could very well be. It is a simple text/XML file with
ItemId,Description. No other information. How would I mark all of the
datarows in the table as changed?
Click to expand...
 
I had hopes, but unfortunately, it didn't appear to make a difference.

Here is what I did:

'Fill Dataset with SQL Table, and then merge in XML Table
SQLAdapter.ContinueUpdateOnError = True
SQLAdapter.Fill(sqlDataSet)
sqlDataSet.AcceptChanges()
sqlDataSet.Merge(xmlDataSet)
SQLAdapter.Update(sqlDataSet, TableName)

Again, it adds any new records, it just won't update any existing ones.

Any other ideas?

Thanks,

Jim
 
Has then been resolved? I am having this same problem and I use Papa's
article to develop my solution. I have a word doc that shows, prior to the
merge, the row in the changes data set exactly match the row in the original
dataset based on the original version of the PK values. Yet they will not
merge.
 
When a I do a search using merge, I get nothing close to a decent
explanation. In fact that was the place I started. If you have a particular
knowledge base article in mind, it would be very helpful if you could post
the link. Thanks, for your reply.
 
I spoke too quickly, i took another stab at it and may have found the article
you spoke about.

In there it states added rows are merged on the basis of the current values
of the PK since added rows do not have original values. If this is the case,
you will never be able to merge rows where the PK is an identity field. I am
speaking about the scenario where one marshals changes in an separate dataset
and uses that 2nd dataset to update the datasource.

If, in your original dataset, you set your PK identity field to be
autoincrementing, seed = -1 and step = -1. Your first added row will have a
PK (current) value of -1. The corresponding row in the 2nd dataset, after
having updated the datsource, will have been refreshed with the real PK
(current) value for example, 3234. Thus according to this article there is
no way for them to merge.

So you must always the delete the added rows from the original dataset
before the merge. Correct?

WRL
 
Back
Top