CODEWITHSUNDEEP
c#.netado.netdatatabledataset
Jon H
Jon H

Reputation: 1071

Why isn't my Merge working?

I have what I am sure is a typical situation.

I have a DB and I have an XML document of data. I'd like to combine the 2 and update the db.

I have 'fill'ed a DataSet and/or DataTable with the DB data, and if I modify the data in that structure i can call Update and all is well.

Here is what I have.

con.Open();
adapter.Fill(ds2, "BASIC");
ds.ReadXmlSchema(@"C:\asis.xsd");
ds.ReadXml("c:\\asis.xml");

// This is the XML data
DataTable loadeddt = ds.Tables["BASIC"];

// This is from the DB
DataTable dbdt = ds2.Tables["BASIC"];

// I want to put the data from xml into the dataset from the DB
dbdt.Merge(loadeddt);


// I want to them put those changes in the db
adapter.Fill(dbdt);

Here is what happens, before I loaded the XSD I got an exception saying that types didn't match.

Now I get nothing, no exception no changes. neither of the DataTable show changes and neither does the DB.

Can anyone offer any suggestions?

If I change the last line to Update I get the following:

Violation of PRIMARY KEY constraint 'PK_BASIC'. Cannot insert duplicate key in object 'dbo.BASIC'.

I have noticed that if you do a .Rows.Count pre/post the merge the record count goes from 10 to 20 so it is combining them... not merging them. argh!

FUTHER MORE if I call dbdt.GetChanges(); it returns the whole lot. if i accept changes and update it still does nothing.

Upvotes: 0

Views: 500

Answers (2)

Jon H
Jon H

Reputation: 1071

Right guys,

I found this article:

http://www.dotnetmonster.com/Uwe/Forum.aspx/dotnet-ado-net/14201/Merge-function-in-DataSet

Basically the XSD needs to completely describe the primary key for the merge to work.

Essentially I needed to add:

<xs:key name="ROOTKey1"  msdata:PrimaryKey="true">
    <xs:selector xpath=".//BASIC" />
    <xs:field xpath="MEMBNO" />
</xs:key>

Upvotes: 0

Austin Salonen
Austin Salonen

Reputation: 50225

You should call Update instead of Fill for this line:

// I want to them put those changes in the db 
adapter.Fill(dbdt);

After question update:
You're inserting a key that already exists. Either the merge is telling the adapter that it should insert when it should update or the DB fill isn't returning all rows. Hard to tell with what's given.

This seems like somewhat of a hack but if you know what rows should be considered updates, you can call SetModified on those rows.

Upvotes: 1

Related Questions