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

Solution 1

Thanks to all that posted an answer. Here is how I did it using the TableAdapter Wizard and the Northwind typed dataset.

1) Right click the Parent table in the xsd designer to add or configure the query. 2) Click the "Next" button in the Wizard until you see the "Query Builder" button. Click the Query Builder button to get yourself in the query builder mode. 3) Right click and add the child table in the design pane. You should have both tables and the default constraint that connects them. 4) Click the column on the child table that you want to filter (this check mark will be removed later) in order to add it to the criteria pane so you can filter it. 5) Add the filter for the Parent and Child columns. In this example, I filtered the Ship Name LIKE 'A%' and the Order Quantity < 20.

Note that at this point you can test your query by clicking the Execute Query button. Using the Northwind DB for SQL 2008 compact edition I get 53 rows returned. If you were to save it at this point it would fail at runtime because of the duplicate Primary Keys in the result set. So the next few steps will get rid of 'em.

6) In the criteria pane, uncheck the child table column that you added previously. The filter will remain and the same column will now be uncheck in the design pane as well. If you run the query you will still have 53 rows but without the child table column. 7) Right click the design pane and add the "Group By". At this point, when you execute this query, you should have no duplicates in the Order ID. I got exactly 29 rows returned. 8) Click OK and then the "Next" button until you save your new FillBy query. 9) Change your source code to use your new FillBy.

When I ran the application, I got the filtered Parent table with the same 29 rows that the Execute Query button returned. The child table worked as expected and contained at least one child row that contained a quantity of < 20.

For a real world application, I think it would be better to use a stored proc or LINQ. But this problem had me scratching my head and so I "made it fit" just because it was a challenge (at least for me).

Solution 2

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...."

Solution 3

If you look in the Orders.Designer.cs (guessing, as I am working in VB), you will likely see a unique constraint defined on Orders (for the primary key).

I suspect the problem is, when you run your query, you are getting one or more individual Order who has > 1 OrderDetails.Quanity > 20....so, that Order will be returned twice in your resultset, violating the primary key.

Try: SELECT * from orders where [Ship Name] LIKE '%whatever% AND OrderID in (select OrderID from OrderDetails where Quantity < 20)

This is likely a very inefficient way to do it, in oracle you would use EXISTS() instead of IN() but I don't know the sql server equivalent.