Error when filling child data table (constraints error) if not present in parent data

reyes

Member
Joined
Nov 11, 2016
Messages
20
Programming Experience
1-3
Hi all

Here's my issue:

I have a windows form which displays all customer information. It does that by filling all of the tables when the form loads, and this works.

I also have a 'Search' function which selects customers by criteria (such as a 'StatusID'). In both cases, the customer table (parent) is filled. However, because the customer table can contain only a partial amount of the table (because it's being filtered by the 'where' clause in its SQL query), when I then fill child tables whose FK of 'CustomerID' is not in the results of my dataset, I get the error:

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

I understand why I get this, but my problem is how do I avoid it? Do I simply set 'EnforceConstraints' to false to avoid the error and then back to true, or is there a way to fill the customer table with everyone and then apply the filter criteria (LINQ?), or an amendment I can make to my child table query (though I assume this wouldn't fix anything as the query would be checking the database, not my dataset!).

Thoughts?!

Thank you.
 
You have basically two choices:

1. Get all the parent data and all the child data and filter locally. That might mean binding the parent DataTable to a BindingSource and setting the Filter property of the BindingSource. That way, you have all the parent records in the DataSet but you only see some of them in the UI.

2. When getting your child data (and possibly your parent data too) call a GetData method instead of a Fill method. If you call Fill methods to populate DataTables in a DataSet then you're subject to the constraints of that DataSet. If you call GetData methods then you get new, standalone DataTables and are thus not subject to DataSet constraints.

If you're already retrieving child data without their parents then I have to think that you're not actually using the relation anyway, so I don't think there should be an issue with using option 2. One thing to keep in mind, though, is that the DataTables are created at run-time so cannot be bound in the designer.
 
Thanks jmcilhinney.

The solution using the BindingSource filter works like a gem and is exactly what I was looking for. It's almost far nicer than having to write stored procedures or different queries for such little filtering needs in my case, too.

Lovely pointer, thanks again.
 
Back
Top Bottom