Introduction
If you've ever been frustrated by the vague error message that the DataSet code gives when trying to fill a DataTable with constraint errors, then maybe this will help.
Background
If you've just started using DataSets and DataTables, you've learnt the basics and you've started to build your application, you start to understand that you could do things better. So you add constraints to the DataSet designer, and try your code again. You then get treated to the following error "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.", in code you didn't even write!
Using the Code
To help diagnose the problem, try the following:
- Find the
.Fill
/.GetBy
line that caused the problem. - Select the line, and surround it with a try/catch block.
- Change the catch block so that it looks like:
catch (ConstraintException)
{
DataRow[] rowErrors = this.YourDataSet.YourDataTable.GetErrors();
System.Diagnostics.Debug.WriteLine("YourDataTable Errors:"
+ rowErrors.Length);
for (int i = 0; i < rowErrors.Length; i++)
{
System.Diagnostics.Debug.WriteLine(rowErrors[i].RowError);
foreach (DataColumn col in rowErrors[i].GetColumnsInError())
{
System.Diagnostics.Debug.WriteLine(col.ColumnName
+ ":" + rowErrors[i].GetColumnError(col));
}
}
}
- Now re-run your program.
- When you open the form, go back to Visual Studio and look at the Output window.
- You should see a count of the errors found, the row error and maybe a column error too.
For some cases that will be enough, but for others it still not explain why the code is failing.
One of the most common issues will be the ordering of the Fill
statements. You probably started with your main DataTable
then added lookup tables etc afterwards. Unfortunately .NET doesn't know that!, so when you call Fill
on your main DataTable
it tries to make sure that any Foriegn Key column in that DataTable is valid by checking the data in other DataTable
. The one you've not filled yet! So if your still having problems re-order your Fill lines, so that the tables (the ones with the Primary Key side of constraint) are filled first. Then move onto your main tables, then finally any tables that reference the main.
Note that you might have to fill DataTables even when you have no intent of displaying their data on your form. e.g. Your Main table as a person Foreign Key from your people table, and your people table has a Foreign Key from your ShoeSize table. You will need to fill the ShoeSize, then People, then Main to avoid the error.
Points of Interest
If you are certain you are not going to alter any of the data, then you can set the EnforceConstraints
property of the DataSet to false. This will stop the checks, but obviously you could loose the data integrity.
History
v1.0: 18/06/2012: Initial version.