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.

+1  A: 

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.

Cade Roux
+1  A: 

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.

Alfred Myers
DataTable.GetErrors.Length is 0 so this doesn't help, unfortunately.
I would expect there to be errors in DataTable.GetErrors if I'd added a row to the DataTable which (say) had a null in a non-nullable column, but the rows which are in the DataTable are valid rows. It's just when they hit the database that they clash with rows already in the DB.
What are you using to get the data from the DataTable to the database? A DataAdapter?
Alfred Myers
I'm using a TableAdapter.
I'm not sure if it affects your line of thought but for both of the users in my initial example, they started with an empty DataTable as (when they started editing) the DB table was empty.