How do you filter a TableAdapter's FillBy based on two tables?

Question

I'm using VS2008 C# Express and the Northwind database on a Windows Form application.

I used drag and drop to set up the master details binding (I used the Orders and Order Details) for the two datagridviews. At this point, everything works as expected. So as not to return every row in the table, I want to filter the Orders table based on a filter for the Orders Table AND also on a field in the Orders Details table. In the TableAdapter Configuration Wizard, I used the query builder to add a new FillByMyFilter which created the following query:

SELECT Orders.[Order ID], Orders.[Customer ID], Orders.[Employee ID], Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship Region], Orders.[Ship Postal Code], Orders.[Ship Country], Orders.[Ship Via], Orders.[Order Date], Orders.[Required Date], Orders.[Shipped Date], Orders.Freight FROM Orders INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID] WHERE (Orders.[Ship Name] LIKE N'A%') AND ([Order Details].Quantity < 20)

I got this by adding both tables but did not check any of the field boxes in the Order Details table so that it would only return the columns that were used in the original Fill query. I'm only trying to filter the DataSet in the master table at this point and not return a different number of columns. Child rows of the Order Details should still work like the default unfiltered result set.

Now the problem: When I click the Execute Query button it works fine. I get 53 rows from the above query rather than the 1078 using the default Fill created by the designer. It return the same columns as the original fill query. However, when I try and run the application I get the following constraint error:

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

What am I doing wrong?

UPDATE: I think I'm getting the constraint error because of the INNER JOIN created by the Wizard. If I edit the query to use LEFT JOIN then the Wizard changes it back to INNER JOIN.

My question still stands as how to filter records in the Parent table (Orders) based on criteria from both the Parent and Child table. My next test is to try and use a stored proc but would like to know using just the TableAdapter custom FillBy method.

Regards,

DeBug

Answer 1

This article contains some troubleshooting suggestions to pinpoint the exact row causing the problem:

DataSet hell - "Failed to enable constraints. One or more rows contain values...."