I've got two rows added to my datatable that I need to commit to the
database. I begin a transaction on my connection and attempt to update both
rows as one transaction to the database.

My first record is successful, but my second row fails due to a business
logic error raised in the stored procedure. The transaction, therefore gets
rolled back and neither record gets commited to the database (perfect).

My first successful row has now changed status from "Added" to
"Unchanged"(why). So, now I rectify my second row in order to rectify the
business logic error and my second row has remained as an "Added" row

Now I begin the transaction and only my second row gets commited to the
database and not my first, as it's status changed to "Unchanged".

How does one go about solving this issue, as one would expect both rows to
remain in the "Added" state, so that both rows can be commited to the

Someone gave me the following recommendation;

>> "Update calls AcceptChanges on each row as it updates them. If you're
>> using the 2.0 Framework (or 3.0), just set the AcceptChangesDuringUpdate to
>> false - then *after* update, when you know everything is kosher, call
>> AcceptChanges on the entire datatable.

>> If you're using an earlier version, you can use a new dataset =
>> OldDataSet.GetChanges();

>> Then, call Update on the *new* dataset. If it's successful, call
>> AcceptChanges on the *old* dataset and you're done. If it's not
>> successful,
>> you're rowstate is fine."

I've tested it using the recommendation and it works fine, but I have an ouput parameter in the stored procedure and that parameter seems to change the row state to 'Modified' instead of 'Added'.

How do get the row to remain in the 'Added' state when the stored
procedure returns an error (also the parameter is mapped to a column in a grid)

Please help...!

Re: .NET Framework Data Access and Storage DataAdapter.Update


It sounds like you might be using the above first suggestion. Try the second one ... it won't change the state of your "old" dataset until you're ready to change it yourself.

Re: .NET Framework Data Access and Storage DataAdapter.Update

William_Ryan MVP

If you use my second recommendation, the rowstate should be untouched since you are never passing it to anything and the adapter isn't touching it at all, it's only affecting the second dataset, the one which you called GetChanges on. So check the rowstate of the original dataset, it should be 'Added' or whatever it was before you called Update on the second dataset.