sqlTransaction problem

have a DataTable with an autoincrement primary key that is retrieved
from the database after insert.
When I execute my InsertCommand within an SqlTransaction the rows in the DataTable are updated with the autogenerated key from SQL Server.
However, when one of the inserts fail and I rollback the entire
transaction, all my DataRows are left with the invalid keys from the
database and a RowState of DataRowState.Unchanged instead of
DataRowState.Added.

So, when I rerun this code:
Me.DataAdapter.Update(Me.Table)

nothing happens. No inserts are performed. This kind of defeats the
purpose of having an SqlTransaction. No changes are made to the
database, but permanent changes are made to the DataTable. Is this a
bug A feature Can anyone help me with a workaround

Answer this question

sqlTransaction problem

Johan Lindfors - MSFT
One thing you might try.

Create a copy of the dataset using either dataset.Copy() or dataset.GetChanges()
Run the update against the copy and if it fails just release it, else acceptchanges on the original.
Firemarble
Thanks for response. I tried and it's works. Now i have a question: when the update fails on dataset.getchanges can i show error on row affected on original dataset thanks
JimJBq
Try using the DataSet.Merge method. I'm not sure if it copies error information, but its worth a shot.
ASHAIKH
Can you show me some code,please
Newland
You might copy the error to the source DataRow (by matching PKs) I guess.
sqlTransaction problem

Answer this question