Updating Sql Server from xml document

  • Thread starter Thread starter foobar
  • Start date Start date
F

foobar

Okay, I have created a Customers class. This class allows a user to select
and update the customers table on an Sql Server database. I can retrieve
the datatable generated by the class. Make changes to the table. Pass the
table back to the class and update the backend with no problem However,
here is my problem: After writing the table to xml using
ds.writexml("path") and editing the document i then read the data into a
dataset and then try to update the back-end using the edited xml document.
The process errors and says i need and Insert command. Well - I should need
an insert. It is an update! What is going on?
 
That is "I should not need an Insert Command" because it is an update. For
some reason the the DataViewRowState = DataViewRowState.Added. Can I
change this?
 
...try to update the back-end using the edited xml document. The process errors and says i need and Insert command...

After you read the rows into the dataset, you must call AcceptChanges() since they are new, "inserted" rows.
However, calling AcceptChanges() will apply all changes to the DataSet, including previous updates.

So, perform any updates that you have first.
Then, read the xml into the DataSet.

You must specify an InsertCommand, to your DataAdapter, that can perform the inserts correctly.
 
Thanks Dave for your reply. Applying AcceptChanges() to the dataset before
I apply the update method doesn't do what I want it to do.
Okay here is the DataViewRowState when ds.AcceptChanges() is commented out:
//# of records added: 1
//# of CurrentRows: 1
//# of records Deleted: 0
//# of records ModifiedCurrent: 0
//# of records ModifiedOriginal: 0
//# of records .None: 0
//# of records OriginalRows: 0
//# of records Unchanged: 0
and a new record is inserted into the backend.

Here is the DataViewRowState using ds.AcceptChanges():
//# of records added: 0
//# of CurrentRows: 1
//# of records Deleted: 0
//# of records ModifiedCurrent: 0
//# of records ModifiedOriginal: 0
//# of records .None: 0
//# of records OriginalRows: 1
//# of records Unchanged: 1
and there is no change to the backend. I expected an update on the record.
 
Okay, I got it working. Here is what I did:

I loaded the xml document into a DataSet (ds). Then I loaded the table
schema from the backend with the current table from the backend into a
DataTable. This is to get all primarykey information created on my
datatable and corresponding data. Finally I merged the tables using
ds.Merge and passing preserveChanges as true.

After applying these changes my DataViewRowState was:

//# of records added: 0

//# of CurrentRows: 1

//# of records Deleted: 0

//# of records ModifiedCurrent: 1

//# of records ModifiedOriginal: 1

//# of records .None: 0

//# of records OriginalRows: 1

//# of records Unchanged: 0
 
Foobar,

Don't call your xml dataset a xml document. I thought that you was talking
about a file that you had exported from word or something. Reading the text
I saw now as well that it was an XML dataset.

You had luck that Dave readed it, however it is a lot better if you give the
right information with a problem direct.

Cor
 
I am talking about an xml document. I write a file using the
DataSet.WriteXml method. I then edit the file by opening it up in notepad.
I read the file back into a DataSet using the DataSet.ReadXml method....
Sorry that I confused you.
 
Back
Top