System.Data.ConstraintException when trying to load a datatable 1

[dseaver]

System.Data.ConstraintException was unhandled by user code
  Message="Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
  Source="System.Data"

I get the above error when trying to use an API we have developed to try to get a table from a SQL database. When developing the API, "preview data" works just fine and returns the table I need. Other functions in the API work fine without the constraint exception being thrown. Is there a way I can narrow down where the exception being thrown or allow the table to be pulled down from the DB? Thanks

 

[JurkMonkey]

Hopefully you are not using table adapters - what a mistake those are!

If you are doing a select query you can turn off constraints. Basically your DataSet needs to ignore constraints:

DataSet ds = new DataSet();
ds.EnforceConstraints = false;

then run your select query.

http://www.ubuntu.com/

 

[dseaver]

Well...The API was written using table adapters. I didn't write it, but I added to it. Should we not use table adapters? Is that the cause of the exception? The API is not that complex, should I redo it and how would I want to redo it? And if its not worth rewriting, is there a work around?

I have a bad feeling about this.

Thanks

 

[dseaver]

I have found what was wrong. We changed one of the columns from varchar(50) to varchar(100) to accomodate for longer strings, and I hadn't corrected the value in the API. I would still like to know about why table adapters are a mistake, for future reference. THanks JurkMonkey

 

[JurkMonkey]

TableAdapters have their place if used properly. Unfortunately it's too easy to attach a bound table to a GUI Control. When you edit the data in the GUI control, you can affect the DB directly.

This is a problem if you want a layered application.

Data Base <---------------|
Data Communication Layer |
Business Layer |You are skipping to here directly
Presentation <------------|

I have seen too much bad design used with TableAdapters.

http://www.ubuntu.com/

 

[dseaver]

Is the Data Communication Layer the same thing as the Data Access Layer?

 

[JurkMonkey]

Exactly.

http://www.ubuntu.com/

 

[dseaver]

We are using a DAL along with our BLLs. Its better to ask questions learn this stuff now when I don't know that much, rather than learn and use bad design and have to go back and fix things.