41 Comments

  • 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.

  • 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.

  • 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....

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

  • 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.

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

  • 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

  • Try .EnforceConstraints = false;

  • 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

  • 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

  • Joel, thanx, you saved my day!

  • 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

  • Joel, thx, you saved my day 2!

  • 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.

  • 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.

  • 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?


  • 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.

  • 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!

  • 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.

  • 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

  • Joel, you are the man.

  • 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.

  • Sanjay, thanx again

  • 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!

  • Thanks Joel. You saved my day too!

    Thanks for his post on Wednesday, July 19, 2006

  • 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<rowsErr.GetUpperBound(0); i++)
    {
    System.Windows.Forms.MessageBox.Show(rowsErr[i].RowError.ToString());
    }
    }

    The message now said clearly that the column ... did not allow nulls.

  • 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?

  • 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.

  • 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.

  • 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.

  • 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".

  • 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

  • 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 :-)

  • 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?

  • 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!

  • This is an annoying issue in 2005.
    just do a try and catch, and save yourself the headache.

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

  • 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...

  • 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.

  • 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.

  • 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

Comments have been disabled for this content.