"Failed to Enable Constraints" Error, and the Zen of DataSets
If you're like me, you love the TableAdapters feature of Visual Studio 2005. Simply right-click on the App_Code folder, add a new item, and choose DataSet. Then pick a database to model it against, and BAM! A Strongly typed data object is built for you on the fly. This model enhances portability for the code we write at my company, since we have a core set of tools that look at different models depending on where you are in my company. (Texas has a different application running their operations versus us here in Boston. Therefore, their data model is different. I could write two datasets and one presentation layer to cover both.)
Anyways, So I went a little DataSet-crazy by making a TableAdapter for every View we use (essentially, every business object has its own Table Adapter). Then I wrote accessor-methods to get the data out of the TableAdapter depending on the Report (some reports filtered by X, others by Y, etc).
However, one of the methods I was calling was failing pretty badly.
1
| ReportDataIndex.DataSource = T.GetDataGroupByWeek(ReportDate.SelectedDates[0]); |
Seems pretty simple, right? ReportDataIndex is a GridView, and T is an instance of my TableAdapter. But every time I ran it, I got an exception:
Failed to enable constraints. One or more rows contain
values violating non-null, unique, or foreign-key constraints.I read and read and read, and couldn't figure it out. A few forums recognized the problem, such as
this article about DataSets, but none specifically mentioned the problem as it could have related to TableAdapters. I couldn't turn off 'EnforceConstraints' on my GridView, and I really struggled. I tried wrapping the Query Columns with 'ISNULL()' but that didnt' work either. I restricted my data results from the DB to only include non-nulls, and it was still failing.
After much soul-searching, I found the issue: If you create additional accessor-methods to your DataSet, their column count has to match the TableAdapter column count. Any columns not mentioned in your addtional method is passed back into the DataSet object as NULL. That's why the Fill was failing. Now, when I build my accessor-method and Query in the designer, it should throw an error and not let me save the DataSet / Compile. However, no pre-compilation is done with this regard on the XML document generated by the DataSet designer.
This is kind of limiting and if you have a TableAdapter that has two methods, one to get rows and one to get DISTINCT subset of rows, it can really cause problems. However, this small flaw is no reason to abandon DataSets and TableAdapters, as they are still great for rapidly prototyping data structures in your application