VS 2005 ASP.NET GridView Give "Failed to enable constraints. One or more rows contain values violating non-null, unique,.." Error

Note: There is a later post on this: http://devnote.stokemaster.com/search?q=enable+constraints Please read that first.

Symptom:

You created a web page with a GridView using a DataSet designer. You had a simple or a bit more complex joins in the table. Then one day, it stops working with an exception of:

"Failed to enable constraints. One or more rows contain values violating non-null, unique..." error.

You do know the target schema changed so you tried to re-configure the query but you still get the same error after re-compiling the dataset and associated DataAdapter.


My Root Cause:

This has happened in my case where the database schema changed drastically. After investigating the automatically generated code the failure occurs when filling the DataTable.

It also happend again much later when someone has changed the Field Size of a column too.

This meant that the the Query worked OK, but the DataTable did not get the necessary schema change.

What Fixed It:

Unfortunately I had to get rid of the entire DataAdapter associated with the query, rebuild the DataSet and then re-bind (re-cofnigure) the ObjectDataSource that binds to the GridView. The key here is that just configuring the query in the DataSet is not good enough, because the associated DataTable does not get the change of the schema and it will exception when the returned row set gets filled in.

Time Wasted:

I was at it for about two hours.