Mysterious ConstraintException was Unhandled error driving me crazy!

Question

I am connecting to an Access Database and am using the following query to retrieve products related to a customer based on names:

 

this.ParentTableAdapter.FillByNames(table1,firstname,middlename,lastname);

 

FillByName Query Details:

SELECT         TChild.product, TChild.cId, TChild.tId

FROM          (TParent INNER JOIN

                          TChild ON TParent.cId = TChild.cId)

WHERE         (TParent.firstname = ?) AND (TParent.middlename = ?) AND (TParent.lastname = ?)

 

It returns the product of the customer as well as the ids when I enter the names.

This query has no problems when there is only 1 order under the person like:

Jane

-          Lotion

But if the database entry contains more than 1 product:

Jane

-          Lotion

-          Bubblebath

It returns a ConstraintException was Unhandled error with the following suggestions:

-          Relax or turn off constraints in your dataset

-          Clear datasets before loading them from view state

-          Be sure you are not trying to assign a value to a primary key field whose primary key already exists

 

The strange thing is, this query worked fine before!  The code I have afterwards actually handles cases when customers have more than 1 order.  It ceased working just yesterday and I don’t know what I did to cause this to malfunction, I think all I did was added some other queries, but I thought this shouldn’t affect the original query.   Can someone tell me where I should start looking to solve this?  Thanks a lot!

Answer 1

This almost always means that you are using a DataTable with a uniqueness constraint on a column, and your query is returning multiple rows with the same value in that column.  In this example, if you've specified a uniqueness constraint on the primary key of Parent, any query that returns multiple rows with the same Parent will throw this exception.  Look at the properties for the Parent's PK column in the data set designer.

Answer 2

I had this and thanks to Robert's post I found my issue.

I had created the data table with a ID field, I then added a second table with a join.......

I removed the ID field from the output  VB then dropped the column constraint, and every think worked.

as it happens I was only using the ID in the where section so don;t need it as an output column, however I think I could now add it in.

the way to tell if this is an issue is to look if there is a KEY in the GUI

---

Alun Davies

Answer 3

This error happened in the bind () of a GridView, but the return of error alleges an error of ConstraintException. Your Messages with the following:

• Relax or turn off constraints in your DataSets.
• Make sure you are not Trying to assign a value to a primary key field where the primary key already exists in the data table.
• Clear datasets before loading Them from view state.
  
  The solution was that even removing the ID in the JOIN.
  Anyone know the cause of this error or can be considered a BUG?
  

Answer 4

First I guess you need to find out what is the root cause of the exception.

If it was "Relax or turn off contraints in your DataSet", here below is the link for solving the problem. 

http://msdn.microsoft.com/en-us/library/s3bxwk8b.aspx

It works for my case. Hope it helps your case.

BTW, I am using VS2008.