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!

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.

This is an annoying issue in 2005.

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

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

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

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

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.

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.

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

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

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

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

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

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

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