System.Data.ConstraintException: Failed to enable constraints.

(continued from the title) One or more rows contain values violating non-null, unique, or foreign-key constraints.

I was working on a small project and I used the DataSet designer to quickly obtain an adapter and a strongly typed table.
I parameterized the table adapter so I can provide a different connection string and a different table for the SELECT query.

As always when you use the designer to initially generate the adapter and data table you have to point it to a table.
The designer inspects then the table and builds all the good things: table adapter, strongly typed data table and row, and so on.
The problem is that it hardcodes the connection string and the name of the table in the SELECT command and I would really like to re-use the generated code to obtain data from other tables too. So, I parameterized the adapter to accept a different table knowing well enough that the other tables have to have identical schema/structure.
I guess otherwise, the adapter.GetData() or adapter.Fill() methods will fail, probably with the above message – the one in the title.

If you read the error message in the title it doesn’t give you the impression it would be thrown for a schema discrepancy. Oh, but it does and that’s what confuses you as you don’t suspect a schema difference to be the problem! And we are not necessarily talking of a big discrepancy like missing a field or even a field of a different type.
No, the one that I encountered was caused by a different width of the field. The autogenerated MaxLength for one of my fields (10) was smaller than the width of the same field in a second table that I tried to use the adapter on. The field in the second table was 30 characters wide. This is what caused the exception to be thrown.

So, to get into some details I will start by saying that I am trying to access dbf tables somewhere in a folder. I am using the Visual FoxPro OLEDB driver which has the ability to treat a folder as a database and the dbf files in the folder as tables of the database. The Visual FoxPro ODBC driver does the same but needs a preconfigured entry in the ODBC connections of the system whereas with the OLEDB you can have the connection string built at run-time.

Anyway with MyDataAdapter and FileDataTable classes already generated, I wrote a few lines of code below to test that the adapter and my parameterizations work well enough to fill data from more than the initial table that I used to generate the classes from.

MyTableAdapter sta = new MyTableAdapter();
Test.FileDataTable sdt;
// setting the new connection for the adapter
sta.SetConnectionString(new OleDbConnection(“Provider=VFPOLEDB.1;Data Source=” + @
\\TESTSERVER\c$\FOLDER));
// setting the table we will use the adapter against
sta.SetTable(“FILE1.DBF”);
try {
// attempt to fill the DataTable
sdt = sta.GetData();
}
catch (Exception ex) {
System.Diagnostics.Debug.WriteLine(ex.Message);
throw;
}

Now, this code works well when trying to fetch the records from FILE1.DBF (the original file) but when I tried to do the same from a structurally identical (almost) FILE2.DBF an exception was thrown: System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

This message is not helping at all in finding precisely what the problem is and more, there was really no constraints to be concerned with and all the columns of the FileDataTable were defined to support nulls. Anyway, in an effort to see if I can get some more information I did a bit of digging and I was led to the Datatable.GetErrors() method. I thought it is worth a try as I had nothing else to go on with anyway.

So, I made the first modification to the code above by adding a DataRow[] dr = sdt.GetErrors(); line in the catch clause. That really didn’t work, as the line was causing an exception complaining that the sdt variable is null. Pretty obvious I said: “I am trying to call GetErrors on a table that doesn’t exist as it failed to be instantiated because an exception.” So, what I had to do is to instantiate an empty DataTable manually ahead of time, and then, in the try block use adapter.Fill(dataTable) instead of the GetData() syntax. This way by the time we call Fill(dataTable), dataTable is already an instantiated object which we can call GetErrors() on and obtain our array containing the DataRows with the problem.

MyTableAdapter sta = new MyTableAdapter();
Test.FileDataTable sdt = new Test.FileDataTable();
// setting the new connection for the adapter
sta.SetConnectionString(new OleDbConnection(“Provider=VFPOLEDB.1;Data Source=” + @
\\TESTSERVER\c$\FOLDER));
// setting the table we will use the adapter against
sta.SetTable(“FILE.DBF”);
try {
// attempt to fill the DataTable
sta.Fill(sdt);
}
catch (Exception ex) {

// get all the rows involved in errors
DataRow[] dr = sdt.GetErrors();
throw;

}

By expanding the first row in the now populated dr array, and looking at the RowError property, I can see:   “Column ‘prod’ exceeds the MaxLength limit.”
So this is when I checked the table and saw that the prod field was 30 chars wide, clearly more than the 10 chars MaxLength specified in the FileDataTable definition.
Apparently out of two tables which I believed identical the first one had a field narrower than the second table.

If you want to make this go away you could edit the FileDataTable’s problem field definition by modifying its MaxLength to be roomy enough.
Another way is to disable the enforcement of constraints at run-time by setting DataSet.EnforceConstraints = false, with the downside that the data in wider fields will be truncated and lost.

The last code addition is to accommodate this second approach. As the data tables don’t have their own EnforcedConstraints property we have to instantiate a DataSet.  We then add the table to it and then set the EnforceConstraints = false; This will cause the ignoring of the discrepancy between the field width in the data set definition and the width of the field that exists in the physical table. The code will not error out again but the value of the field will be truncated.

The final code could look something like this:

DataSet ds = new DataSet(“Test”)
MyTableAdapter sta = new MyTableAdapter();
Test.FileDataTable sdt = new Test.FileDataTable();
ds.Tables.Add(sdt);
ds.EnforceConstraints = true;
// setting the new connection for the adapter
sta.SetConnectionString(new OleDbConnection(“Provider=VFPOLEDB.1;Data Source=” + @
\\TESTSERVER\c$\FOLDER));
// setting the table we will use the adapter against
sta.SetTable(“FILE.DBF”);
try {
// attempt to fill the DataTable
sta.Fill(sdt);
}
catch (Exception ex) {

// get all the rows involved in errors
DataRow[] dr = sdt.GetErrors();
throw;
}

Cheers.

Advertisements

Talking about TFS 2010 Dashboard Permissions when running in SharePoint Server 2010 – Layers & Layers of Security

  This is a very interesting and revealing article about one of the many very misterious sides of the integration between Team Foundation Server Sharepoint. It is not exhaustive but for the amount of brand new information it contains, I surely not regret the 10 minutes that I spent reading it .

TFS 2010 Dashboard Permissions when running in SharePoint Server 2010 – Layers & Layers of Security

Good luck