Which dataset row is violating the DB unique constraint?

Question

Hi,

I'm writing an application using c# 2005 and Sql Server 2000.

I have a table, with a unique constraint and, in the case I am concerned with, I have two users using a form which will (when Save is pressed) update the table.

If the table is, say, NAMES( ID int, NAME varchar(20)) and the unique constraint is on NAME, if the first user to save adds the NAMEs 'David' and 'John' then that's fine. If the second user tries to update using a DataTable which contains rows with the NAMEs 'John' and 'Susan', then a SqlException is thrown. However there is nothing in the exception which tells me which row in my DataTable violated the constraint.

Other than getting the details of the unique constraint's composition from the DB and then using this info to check each row in my DataTable to see if that row violates the constraint, is there a way to determine which row is at fault?

Thanks, Dave.

Answer 1

In addition, it is possible that the rows in the DataTable simply conflict with each other, and thus the database operation fails, even though nothing committed in the database conflicts with the DataTable.

Answer 2

You should use DataTable.GetErrors to get an array of the DataRows with errors.

For each DataRow in the array, you should check DataRow.RowError and which columns are in error with DataRow.GetColumnsInError.