Constraint violations with batch updates in DataSet

[Grim Rob]

I am commiting changes to several existing records which have been
changed using a DataSet. In my table I have a field called "rank",
which has a unique constraint on it.

The first record has a rank of 1, the second a rank of 2. My update
causes the two ranks to reverse so that the first record now has a rank
of 2, and the second 1.

When I called the Update method of the SqlDataAdapter or OleDataAdapter
(with an Access database this time), I get a constraint violation for
the first record. Yet if it was smart enough to continue to the second
record and save that first there would be no constraint violation.

Is there any way in ADO.NET to do a true batch update so that
table-wide constraints are only applied after all the new records have
been commited?

Grim Rob

 

[Sahil Malik [MVP]]

I am assuming you are getting this constraint violation from your dataset.
Set dataset.EnforceConstraints = false right before the update, and then to
true right after the update. See if that helps.

 

[Grim Rob]

I am assuming you are getting this constraint violation from your dataset.
Set dataset.EnforceConstraints = false right before the update, and then to
true right after the update. See if that helps.

No we don't have an equivalent constaint in the database, only in the
actual database table. So the problem occurs when the Update method of
the DataAdapter is called to commit the data back to the database.

Grim Rob

 

[Sahil Malik [MVP]]

No we don't have an equivalent constaint in the database,

I said "DataSet" .

Okay, in order to answer your question, I need to know where exactly is the
erring constraint? Dataset or database - it's gotta be one of the two.

 

[Grim Rob]

The constraint is only in the Database. It is not in the Dataset. The
problem occurs in the DataAdapter when the changes in the DataSet are
transferred to the database.

Grim Rob