none
Freakin TableAdapter! System.Data.ConstraintException RRS feed

  • Question

  • Hi, I'm getting the ol'

     

    System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

     

    error from one of my table adapters even though "EnforceConstraints" is turned off on the DataSet. Usually I solve this by deleting the TableAdapter and re-adding it to the dataset, but this time that is not working and I have no idea what the problem is. I'm about ready to just use an old school, weakly typed data table. The TableAdapter is using a stored procedure that recieves an integer and a string as parameters. The procedure is a search function and uses the integer to determine which columns to search, and the string as the where clause criteria. So depending on the integer's value it will execute a different block of dynamic sql. One clue may be that if my keyword is "alas" it returns 1 record without an error. If my keyword is "ala" it should return many rows, but instead causes the error. I am able to right click on the table adapter and use the "Preview data" function but it won't work at run time.

    Wednesday, October 24, 2007 9:59 PM

Answers

  • Problem solved. I discovered that my query was returning rows with duplicate IDs for the primary key column because of the join I was using in the SQL. I solved the problem my opening the DataSet xsd using the schema editor instead of design view. I then found the table adapter and right clicked on the primary key icon for the ID column and chose Delete Key. This removed the constraint that never should have been placed on it in the first place. This error may also be recieved if you have deleted a column in your table but the xsd still expects it, if you are receiving nulls on a column with a non-null constraint, or if you have changed the length of a column and the schema has a maxLength set to the old length. The datasets are not very good at updating.

    Monday, October 29, 2007 4:35 PM

All replies

  • I also get the error on another table adapter but on this one it's clear what the problem is. The query returns two results for one ID (for some reason...) so I think it thinks that a primary key cannot be included twice. Why is it doing this if enforce constraints was turned off for the dataset?

    Wednesday, October 24, 2007 11:58 PM
  • Problem solved. I discovered that my query was returning rows with duplicate IDs for the primary key column because of the join I was using in the SQL. I solved the problem my opening the DataSet xsd using the schema editor instead of design view. I then found the table adapter and right clicked on the primary key icon for the ID column and chose Delete Key. This removed the constraint that never should have been placed on it in the first place. This error may also be recieved if you have deleted a column in your table but the xsd still expects it, if you are receiving nulls on a column with a non-null constraint, or if you have changed the length of a column and the schema has a maxLength set to the old length. The datasets are not very good at updating.

    Monday, October 29, 2007 4:35 PM