ConstraintException when filling a DataGridView from a SQL Server view

Question

Hi all,

I have a SQL Server view which does a simple join of two tables and returns some of the columns. Running this view in Management Studio does not cause any problems, the expected data (two rows) is returned.

When I use this view as data source for my TableAdapter in Visual Studio, I get a ConstraintException saying "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.".

I used EnforceConstraints = false before calling tableAdapter.Fill( dataSet.vwMyView ), so I could get some more information about the cause of this problem. I ended up with a message saying that the column "MyColumn" has a constraint that it must be unique, but the value 4 (an id) already exists (yes, there are two rows with this id).

I do understand that this particular is unique (and the primary key) in one of the two tables I join in my view, however, I do not get why this should cause an error in my DataGridView because

• I only want to display data
• I do not get any errors when executing the same select on the very same view in Management Studio

Why is that? I need to display the id value, and I also do not understand why I actually get that error.

Thanks in advance &

Best regards

Gorgsenegger

Answer 1

Hi,

It looks as this constraint is being set since the value is a primary key from the underlying table.

I'd suggest that you perhaps do one of the following:

1) If you only wish to display this information, then don't bother re-enabling the constraint. This might sound like a get-out, but really, if you don't require information then there's no point having it.

2) Even better, simply remove the key that is 'incorrectly' generated in the dataset designer. That will then mean you don't have to bother disabling the table constraints in the first place!

Hope this helps.

J Dubs.