Caveman's Blog

My commitment to learning.

Failed to enable constraints

leave a comment »


Debugging a “failed to enable constraint” error when filling a data table was very hard until I found this wonderful tip. This blog post is only for documentation purpose and the full credit goes to PaulStock for this awesome response.

This problem is usually caused by one of the following

  • null values being returned for columns not set to AllowDBNull
  • duplicate rows being returned with the same primary key.
  • a mismatch in column definition (e.g. size of char fields) between the database and the dataset

Try running your query natively and look at the results, if the resultset is not too large. If you’ve eliminated null values, then my guess is that the primary key columns is being duplicated.

Or, to see the exact error, you can manually add a Try/Catch block to the generated code like so and then breaking when the exception is raised:

enter image description here

Then within the command window, call GetErrors method on the table getting the error.
For C#, the command would be ? dataTable.GetErrors()
For VB, the command is ? dataTable.GetErrors

enter image description here

This will show you all datarows which have an error. You can get then look at the RowError for each of these, which should tell you the column that’s invalid along with the problem. So, to see the error of the first datarow in error the command is:
? dataTable.GetErrors(0).RowError
or in C# it would be ? dataTable.GetErrors()[0].RowError

enter image description here

Reference:

1. Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

Advertisements

Written by cavemansblog

June 10, 2012 at 10:26 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: