Dataset Merge Question

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)

--
Jim McFadden
Northern Health Anesthesia
A Division of VantageMed, Inc.

Alex
Mon Apr 04 13:31:43 CDT 2005

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/default.asp?url=/library/en-us/cpref/html/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


"Jim McFadden" <JimMcFadden@discussions.microsoft.com> wrote in message
news:F67C5E33-149E-4931-803B-B6549E0D1FB4@microsoft.com...
> 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)
>
> --
> Jim McFadden
> Northern Health Anesthesia
> A Division of VantageMed, Inc.

Papa
Mon Apr 04 15:17:06 CDT 2005

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" wrote:

> 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/default.asp?url=/library/en-us/cpref/html/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
>
>
> "Jim McFadden" <JimMcFadden@discussions.microsoft.com> wrote in message
> news:F67C5E33-149E-4931-803B-B6549E0D1FB4@microsoft.com...
> > 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)
> >
> > --
> > Jim McFadden
> > Northern Health Anesthesia
> > A Division of VantageMed, Inc.
>
>
>

JimMcFadden
Tue Apr 12 14:32:16 CDT 2005

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" wrote:

> 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/default.asp?url=/library/en-us/cpref/html/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
>
>
> "Jim McFadden" <JimMcFadden@discussions.microsoft.com> wrote in message
> news:F67C5E33-149E-4931-803B-B6549E0D1FB4@microsoft.com...
> > 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)
> >
> > --
> > Jim McFadden
> > Northern Health Anesthesia
> > A Division of VantageMed, Inc.
>
>
>

Alex
Wed Apr 13 10:40:08 CDT 2005

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



"Jim McFadden" <JimMcFadden@discussions.microsoft.com> wrote in message
news:3FF8A4C5-6304-4709-8C31-599A4713F154@microsoft.com...

> 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" wrote:
>
>> 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/default.asp?url=/library/en-us/cpref/html/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
>>
>>
>> "Jim McFadden" <JimMcFadden@discussions.microsoft.com> wrote in message
>> news:F67C5E33-149E-4931-803B-B6549E0D1FB4@microsoft.com...
>> > 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)
>> >
>> > --
>> > Jim McFadden
>> > Northern Health Anesthesia
>> > A Division of VantageMed, Inc.
>>
>>
>>

JimMcFadden
Mon Apr 18 09:43:11 CDT 2005

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

"Alex Passos" wrote:

> 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
>
>
>
> "Jim McFadden" <JimMcFadden@discussions.microsoft.com> wrote in message
> news:3FF8A4C5-6304-4709-8C31-599A4713F154@microsoft.com...
>
> > 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" wrote:
> >
> >> 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/default.asp?url=/library/en-us/cpref/html/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
> >>
> >>
> >> "Jim McFadden" <JimMcFadden@discussions.microsoft.com> wrote in message
> >> news:F67C5E33-149E-4931-803B-B6549E0D1FB4@microsoft.com...
> >> > 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)
> >> >
> >> > --
> >> > Jim McFadden
> >> > Northern Health Anesthesia
> >> > A Division of VantageMed, Inc.
> >>
> >>
> >>
>
>
>

WhiskyRomeo
Sat Jul 02 13:36:04 CDT 2005

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.
--
William R


"Jim McFadden" wrote:

> 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)
>
> --
> Jim McFadden
> Northern Health Anesthesia
> A Division of VantageMed, Inc.

Greg
Wed Jul 06 14:20:43 CDT 2005

Look at the SDK for "Merge", has a decent explanation.


"WhiskyRomeo" <WhiskyRomeo@discussions.microsoft.com> wrote in message
news:3C38459C-FF57-4DD2-B511-A0E91C30317B@microsoft.com...
> 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.
> --
> William R
>
>
> "Jim McFadden" wrote:
>
>> 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)
>>
>> --
>> Jim McFadden
>> Northern Health Anesthesia
>> A Division of VantageMed, Inc.

WhiskyRomeo
Fri Jul 08 14:28:01 CDT 2005

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.
--
William R


"Greg Robinson" wrote:

> Look at the SDK for "Merge", has a decent explanation.
>
>
> "WhiskyRomeo" <WhiskyRomeo@discussions.microsoft.com> wrote in message
> news:3C38459C-FF57-4DD2-B511-A0E91C30317B@microsoft.com...
> > 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.
> > --
> > William R
> >
> >
> > "Jim McFadden" wrote:
> >
> >> 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)
> >>
> >> --
> >> Jim McFadden
> >> Northern Health Anesthesia
> >> A Division of VantageMed, Inc.
>
>
>

WhiskyRomeo
Fri Jul 08 15:23:01 CDT 2005

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




--
William R


"Greg Robinson" wrote:

> Look at the SDK for "Merge", has a decent explanation.
>
>
> "WhiskyRomeo" <WhiskyRomeo@discussions.microsoft.com> wrote in message
> news:3C38459C-FF57-4DD2-B511-A0E91C30317B@microsoft.com...
> > 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.
> > --
> > William R
> >
> >
> > "Jim McFadden" wrote:
> >
> >> 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)
> >>
> >> --
> >> Jim McFadden
> >> Northern Health Anesthesia
> >> A Division of VantageMed, Inc.
>
>
>