Monday, June 23, 2008

System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

Don't you just love messages like that when you are programming!! I sure as hell do! Whats not to love about an obscure message that has nothing to do with the problem you are experiencing? Recently, after adding a column to a stored procedure I created, my application started giving me this wonderfully helpful error message. First, I went to my stored procedure to see what was coming back from the database. Everything was coming back just fine. Then, I went to my dataset in Visual Studio and....everything looked fine!! Darn!!! Wasn't everything just working a minute before I added that line to my stored proc???

I went back to my dataset in Visual Studio and looked through it once more. The answer to this stupid error message came jumping out at me. I had changed the stored proc in SQL Server and then right clicked on the table adapter in the dataset and selected Configure to add the column. You have to use Configure in order to get the dataset to recognize the column you just added to the stored proc. The column was added perfectly, except there was ONE little detail that was wrong - the max length of the column had defaulted to 10. Changing this stopped the error!!!!

Here is a screenshot of the item that must be changed to stop this stupid error.





I love you Microsoft, but darn! Can't you give me accurate messages when there is something wrong? Something that says that I violated a primary key constraint is a lot different from needing to change the MaxLength of a column.....or at least I think it's different :-) (ha,ha...don't send me a message! I'm being sarcastic here!)

I don't know...maybe I'm just stupid!! :-)


Problem:

You add a new column to a stored proc or sql statement and when the data is pulled using the fill method of a strongly typed dataset you get an error message about a constraint issue.

Error Message:
System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

Solution:
Change the MaxLength value of the column you just added.





1 comment:

Anonymous said...

I have similar problem after changing a column for Int32 to int64. Pulling my hair out for hours!!!!!!!!
This post is very helpfull. Thank you.