DataSet hell - "Failed to enable constraints. One or more rows contain values...."

Important note: Hi folks. This post was written in 2004. Yes, people have been getting this for over 6 years. It’s 2011 now, and here you are, still stuck working on this crap.  I highly suggest you read this, as an alternative rout to solve your problems. 

Thanks, Roy in 2011.

================================

Sunday, October 03, 2004 4:16 PM by Drew Marsh

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

I remember this[1] and nope I never found a solution. :(

[1] http://weblogs.asp.net/dmarsh/archive/2002/06/16/492.aspx

Sunday, October 03, 2004 5:59 PM by Miki Watts

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

While i don’t have a way to identifying the problem column, i encountered this problem while doing a select and not specifying a field that must not be null, such as primary key. Apparently, the dataset fills in null for the non-selected column values, and then checks the contstraints, which of course fails. The solution is to select the non-null column as well.

Sunday, October 03, 2004 6:46 PM by Darren Neimke

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

Hi Roy, the 2 simplest ways to debug this are:

a) Run the Sql statements in Query analyer and see what resulsets are returned. This will show what data might potentially be causing the issue.

b) there’s a setting on the dataset (or datarelation, can’t remember now) called “EnforceConstraints” - or something - if you set this to false, the dataset should load fine and, again, you will be able to see what data is causing the issue.

Sunday, October 03, 2004 6:58 PM by Roy Osherove

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

Darren - that’s exactly it - I don’t think I should even be doing these hhops to get the data I want. There should be a simpler way. Also Setting enforceCOnstraint on and off does not help. not when you have a dataset with 50 tables in it with relations on them….

Sunday, October 03, 2004 7:15 PM by Darrell

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

I’ve always had luck going through and making sure the non-null columns had no null values returned from the database.

Sunday, October 03, 2004 11:49 PM by Sanjay Sheth

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

The solution lies in the GetErrors method of the DataTable class:
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataDataTableClassGetErrorsTopic.asp

Essentially, when I see the message above, I check the HasErrors property for each DataTable in the data set and then invoke the GetErrors method on the tables reporting errors. The GetErrors returns a collection of DataRows and you can invoke the .RowError property on the each of the error-stricken rows to find out just exactly what the problem is.

Sunday, October 03, 2004 11:52 PM by Sanjay Sheth

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

I forgot to add the part about turning off the .EnableConstraints before the Fill. I normally enable the constraints again, only after the original Fill is complete. Then, you can catch the exception above and launch a error detection function which does what I described above.

Monday, October 04, 2004 5:50 AM by Jackie Goldstein

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

Yes, Sanjay has laid out the way to go on this.

Monday, October 04, 2004 10:52 AM by Roy Osherove

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

Sanjay - Thanks:)

Tuesday, October 05, 2004 10:07 PM by TrackBack

# DataSet hell -

Thursday, December 09, 2004 11:29 PM by TrackBack

# DataSet Constraint issues

Friday, June 23, 2006 9:16 AM by Lee

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

Hi, i’ve had the same problem but i don’t know how to turn off the .EnableConstraints property.  Appreciate if someone could help.

Thanks

Tuesday, June 27, 2006 11:35 AM by Alana

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

Try .EnforceConstraints = false;

Monday, July 10, 2006 12:09 PM by Mike

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

Yes, but it still returns the error string. I can’t figure out how to identify what row failed, only what the error is. Even when you cycle through rows in error, there’s nothing to point back to the original row index or field values??? mlreese@sunocoinc.com

Wednesday, July 19, 2006 2:37 PM by Joel

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

I ran into this problem as well, and it actually had nothing to do with null or unique values. The problem was caused by a discrepancy between the defined max size of a data column in my project’s XSD and the size in the database. When the call was made to fill the table adapter, the above error message appeared, apparently because one of the columns in the db was larger than the XSD / table adapter allowed for. As far as I know, there’s no meaningful way to parse and deal with this error message. Also, I couldn’t find a way to refresh the XSD, even after the reference to the problematic table in my data connections was updated. Best of luck, Joel

Thursday, July 20, 2006 11:57 AM by Hans

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

Joel, thanx, you saved my day!

Friday, July 21, 2006 12:42 PM by Greg

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

Saved my day too! As a test, I set all MaxLength values to -1. That proved it was a max column size issue. Now just have to narrow down which one. Greg

Saturday, July 22, 2006 12:44 AM by dudo

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

Joel, thx, you saved my day 2!

Sunday, July 30, 2006 8:39 AM by John

# re: Failed to enable constraints. One or more rows contain values….

Here’s another quirk. I got the error when a select statment just returned 1 row. Why this happened is beyond me, but I have been altering the database and the DataSet a lot. The solution was to just remove the Table and TableAdapter from the DataSet and add everything again. Basically updating the DataSet by refreshing schema or adding columns doesn’t work. And the EnabledConstraints propery usally never affects this problem. If you don’t have your database design set in stone, the DataSet schema will mostlikely be obsolete, and cause these kind of errors. Btw, the table is never returned, so it is not possible to access the GetErrors() method.

Tuesday, August 08, 2006 10:38 AM by Dnote

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

I got this message after I’ve deleted a table from the database. Although I checked the querydesigner twice, DataSets have the habit of remembering stuff that is already gone. I had to remove a column from the dataset manually in the dataset designer in order to solve the problem.

Monday, August 14, 2006 3:27 PM by Frustrated Bill

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

I got this error message by changing the length of a column when testing. The refresh schema and even rebuilding the dataset did not work. I also kept loosing my Objectdatasource references in VS. I had to shut it down and start it back up to see the datasources. After looking at these posts, I modified the data to fit the length constraint and magically everything worked. Once it worked once, I was able to type the extended text back in and it still worked?

Tuesday, August 29, 2006 11:16 AM by Justin

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

I noticed that if I had one query with a join and another query without that joing on one tableadapter, I would get this error. Adding the additional join corrected the problem.

Thursday, August 31, 2006 10:01 PM by Chris

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

I just went through this entire mess, and my fix was none of the above posts, but similar to Joel’s post on 7/19. I have 5 entries in the TableAdaptor, but when I really looked at the .XSD file, there were only 4 entries defined. I found the missing item, and regenerated it by opening the item, and clicking Finish. I checked the .XSD file again, and now there are 5 entries (as there should be). Now suddenly, the page is working again. Thanks go to everyone here, and especially to Joel for me!

Sunday, September 03, 2006 10:43 AM by Dnote

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

Another situation where this error can arise is when the result of the dataset is returning duplicate key values. This can happens because VS.NET assign a prim. key automatically on a column (with me always the first) of the dataset, while this column might not necessarily be primairy.

Saturday, September 09, 2006 11:41 AM by Qun Wang

# My way:

I use xsd file, objectdatasource and sps as my programming mode. I encounter the problem too. I add a function to handle the table’s initial event in the xsd file’s codebehind file, looks like this in userdataset.vb: Partial Public Class UsersDataSet Partial Class PeoplesDataTable Private Sub PeoplesDataTable_Initial(ByVal sender As System.Object, ByVal e As EventArgs) Handles Me.Initialized Me.Constraints.Clear() End Sub End Class

Wednesday, September 13, 2006 12:35 PM by JCR

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

Joel, you are the man.

Tuesday, September 19, 2006 5:32 PM by Kyle

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

Make sure all your quieries in your table adapters are returning the same Schema (the same columns) if they are not .. make them or make new table adapters. This method solved my issue with this error.

Monday, September 25, 2006 12:49 PM by Yaron

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

Sanjay, thanx again

Friday, September 29, 2006 4:39 PM by Hank

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

This post is what fixed it for me. The default Fill, GetData() query had to match my other parameterized query. “# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….” Make sure all your quieries in your table adapters are returning the same Schema (the same columns) if they are not .. make them or make new table adapters. This method solved my issue with this error. ” Thanks to all who submitted a post!

Saturday, September 30, 2006 4:51 AM by Jerry

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

Thanks Joel. You saved my day too! Thanks for his post on Wednesday, July 19, 2006

Tuesday, October 10, 2006 4:30 AM by Michael S.

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

I did it this way: try { this.myDataAdapter.Fill(myDataTable); } catch (System.Data.ConstraintException constrExc) { System.Data.DataRow[] rowsErr = myDataTable.GetErrors(); for (int i=0; i

Friday, October 13, 2006 3:52 AM by Winter

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

I got this error after having changed a field in my database from int to nvarchar (string). I did not realize that was problem right away, since I had made the change some time before I started using the TableAdapter that it was called by. But after having searched for a few hours I found that the field was still declared as an int in the DataSet-code (and was not nullable as it had been when it was an int, hence the non-null violation?). I resolved it by removing the field (right-click and delete) from the TableAdapter and then putting it back by rewriting the select-phrase to include it. Maybe this is all that is necessary instead of redoing an entire TableAdaptor in some cases?

Wednesday, November 08, 2006 5:00 AM by Tansu

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

I had the same problem and setting the MaxValues to -1 did not help. What did the trick for me was that, my query was combining couple of tables in a stored procedure. Anyway, some of the ID fields in the table had Default values of DBNULL even though they were not nullable. So, I just changed them to default values as in the SQL Server and everything worked fine after that.

Thursday, November 09, 2006 2:37 AM by another fix

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

I had the same issue. I located the problem by reducing the number of rows returned until the problem went away. Then I selected one EXTRA row in QA. the problem: returning multiple rows w/ the same primary key. I changed the fill command from: o_da.fillSchema(o_ds, SchemaType.Source) to: o_da.Fill(o_ds) —— problem solved.

Thursday, November 09, 2006 5:03 PM by Taras

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

Kyle’s post fixed the problem for me. I tried appending a query with a different schema. The base schema returned all fields whereas the new query had to return only one field The fix was to create a separate table adapter.

Monday, November 13, 2006 3:27 AM by Fedor

# re: DataSet hell - "Failed to enable constraints. One or more rows contain values…."

John’s post helped me a lot. When working with the DataSet designer, and I do that a lot, a lot of things are automatically wired up for one. And this is exactly where things can go wrong and the only way out is rebuilding the tableadapter from scratch. Especially when, as John puts it, “your database design is not set in stone”.

Friday, November 17, 2006 12:29 PM by robert barber

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

SOLVED!!!

My dataset is based on a stored procedure.  The stored procedure returns early if anticapited business rules are not complied.  When returning on an error condition the columns selected prior to a return did not match the columns selected when the stored procedure completed its logic

Wednesday, November 22, 2006 11:24 AM by Matt C

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

I had this problem in ASP.Net 2.0 when I had a dataset table containing BIT fields which allowed NULL values.  The table being returned (single record) contained a NULL value in one of my BIT fields.  The dataset (at least in 2.0) allows you to return “Empty” instead of NULL for string fields ONLY (this is under properties of each individual field in the dataset diagram).  For other field types, the only value it allows is “Throw Exception”. Great(!)

Solved it for this one record by going into the table and replacing the NULL with False, manually.

Longer term, if this is the reason for the error, this can be fixed by ensuring that NULL values are not permitted in your table, and instead setting a default value of (1) or (0) (i.e. true or false) in your BIT field.

That’s what I did and the problem has gone away :-)

Thursday, November 23, 2006 12:57 PM by duanxn

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

The same issue came up only when we upgrade the db to SQL Server 2005 for 2000, while the middle tier is always using .NET 2.0.

As an example, one of the problematic sproc uspGetOrderItem is like:

Select o.orderid, i.ItemName, i.Qty

From order o join orderitem i on o.orderid = i.orderid.

I replaced o.orderid in the select list with i.orderid. NO other code change at middle tier, and it worked! I feel lucky, but I cannot think of any reason why it worked on SQL Server 2000 but not on SQL Server 2005.

Can someone help?

Thursday, November 30, 2006 8:26 AM by Marja Klaver

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

I used a stored procedure that returned a different number of columns depending on a parameter. I changed the procedure so it always returns the same columns, and the problem is solved!

Friday, December 15, 2006 6:35 AM by Darren Wainwright

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

I have this problem time to time and have yet never managed to actually solve it - but i have worked around it..

Say you have a table adapter with many methods in it and one of those methods is causing this problem - normally the case for me - just take this one method and give it it’s own table adapter. Seems that some very very tiny detail is conflicting between the methods of the adapter, rather than the stored procedures etc.

just my thoughts.

Tuesday, January 09, 2007 10:14 AM by Rami

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

This is an annoying issue in 2005.

just do a try and catch, and save yourself the headache.

Wednesday, January 24, 2007 11:02 AM by Siju

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

Hi,

Thanks for all the comments posted here, they are definitely giving me new insights into Datasets. However, I am running into a problem that I can’t seem to solve. I have a dataset in my client application which has multiple tables and I have parent-child relationships and foreign key constraints set. Now when I fill the tables, I get row errors for the child table, that he value need to be present in the parent table. When debugging, I looked at the parent table and the values are there. So why is the child table still throwing errors? Anybody…??

Friday, January 26, 2007 10:32 PM by smith zhang

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

you must first fill the master table,second fill the detail table,maybe will help you to handle the question! good luckly!

Friday, February 09, 2007 9:31 AM by arcturus

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

I get the error when I try to enable constraints after Merge. I’ve 143 tables in my dataset and tried to pinpoint the table giving me grief by using Haserror and Geterrors. But there werent any error in any of the tables.

Any further suggestions?

Thanx

Wednesday, February 14, 2007 5:17 AM by Christophe

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

Got the error when databinding an objectDataSource to a repeater. Solved the problem by adding all the remaining columns (I’m not using) to the selectcommand. It works now, I’m happy again…(damn workarounds)

thx alot everyone…

Tuesday, March 20, 2007 1:33 PM by Dennis Alzoubi

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

I’ve got that error when I was using the strongly typed dataset, the solution that worked for me is that I’ve added “Distinct” keyword in the stored procedures that combine two or more tables in the returned final results while defining a primary key in the dataset.

Friday, March 30, 2007 2:05 PM by iHateSTDs

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

I had a get statement that would not work with the schema of my table adaptor….after hours of insanity I decided to just add another table adapter based on the query I wanted to use (without the where clause), then added a select query with the where clause to the new table adapter.

On my aspx page dropped in a new ObjectDataSource to use the new tableadaptor and it worked like a charm.

For now on I will create seperate table adaptors for complex select queries.

Hope this helps.

Wednesday, April 11, 2007 12:18 PM by Ed Giardina

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

With regards to Darren Wainwright’s comments, its because you have a mismatch between the TableAdapter and the specific query with regards to the amount of columns. If your TableAdapter is defined as

Select * FROM table_a

and your specific Fill Method/Get Method / Query is

SELECT field1 FROM table_a

or

SELECT DISTINCT field1 FROM table_a

and table_a has more than one column,for instance, the exception will be thrown. I saw this by ‘previewing’ the data with the designer. (Right click on the Query in the designer, and choose preview). The fields not listed in the query were empty, and that’s what the exception is barking about. The TableAdapter Query must have the same columns as the individual Queries attached to it.

It makes sense, if you think about it. The Fill/Get method of the TableAdapter is really creating a specific type of DataSet, and if you’re missing fields, the binding doesn’t work properly.

Wednesday, April 18, 2007 12:04 AM by Juan

# re: DataSet hell - “Failed to enable constraints. One or more rows contain values….”

Joel solution suited for me. Basically another developer changed the size of the column and when i did refresh schema the XSD didnt get refreshed as well. I went to check column by column and comparing it with my database and fix it up. Now all is cool. Thanks dude

Monday, July 23, 2007 7:22 AM by » First rule of blogging: Enable Anonymous Comments Community Hacker - Creating online communities and blogs, By Roy Osherove

# » First rule of blogging: Enable Anonymous Comments Community Hacker - Creating online communities and blogs, By Roy Osherove

Pingback from  » First rule of blogging: Enable Anonymous Comments Community Hacker - Creating online communities and blogs, By Roy Osherove

Sunday, September 23, 2007 10:11 PM by Brian Mains Blog

# DataSet Constraint Error

I was dealing with an error about "Failing to enable constraints". I was just added to this

Wednesday, February 27, 2008 3:52 AM by Community Blogs

# My top 21 Most Viewed Blog posts

Here are the top 21 viewed pages on my blog in the past year and a half: Essential Software to install

Monday, March 24, 2008 8:01 PM by Programming Links Of The Day, 3/24 « 36 Chambers - The Legendary Journeys

# Programming Links Of The Day, 3/24 « 36 Chambers - The Legendary Journeys

Pingback from  Programming Links Of The Day, 3/24 « 36 Chambers - The Legendary Journeys

Sunday, November 02, 2008 2:47 PM by nateirwin.net » Blog Archive » ASP.NET DataSet Error: “Failed to enable constraints. One or more rows…”

# nateirwin.net » Blog Archive » ASP.NET DataSet Error: "Failed to enable constraints. One or more rows…"

Pingback from  nateirwin.net  » Blog Archive   » ASP.NET DataSet Error: "Failed to enable constraints. One or more rows…"

Tuesday, February 10, 2009 11:47 AM by Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

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

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