How do I apply a filter to a BindingSource when doing a scalar query using a TableAdapter?
I'm using a binding source on a Windows Form application and I'm trying to filter the data using the "Filter" property on the binding source. I can't get the filter to work when doing Scalar queries (i.e. SUM(), AVG(), COUNT() etc.). Here is the basic flow of the code that I have.
//apply filter to inventory datasource
bindingSource.Filter = filterString;
OnHandQuantity = Convert.ToDouble(tableAdapter.SumOfInventory(productCode));
How are you grouping?
ASKER
There is no actual grouping using the GROUP clause. I just filter out the rows by using a WHERE IN (....) clause. This is done with the bindingsource filter.
You cannot group in the binding source.
You can create the Sql Command by hand and assign to the binding source and not use the filter.
If you want to use the filter, you can create a view and us it
You can create the Sql Command by hand and assign to the binding source and not use the filter.
If you want to use the filter, you can create a view and us it
ASKER
I don't think I have to do a group.
If I filter out to just the rows I want and then SUM() them all it should work fine. I want to "Filter" the binding source then use the SUM() Agg. function.
If I filter out to just the rows I want and then SUM() them all it should work fine. I want to "Filter" the binding source then use the SUM() Agg. function.
The SUM aggregate function works only for GROUPed BY command.
Can you post the table structure and the query you want to run?
Can you post the table structure and the query you want to run?
ASKER
The Sum function does work without the GROUP BY clause.
Basically the SQL that I'm trying to achive via BindingSource.Filter is
SELECT SUM(Quantity) FROM Inventory_View WHERE productCode = 'prodCode' AND Location IN ( 'loc1', 'loc2', etc...)
The view structure looks like
ProductCode varchar
LocationCode varchar
Quantity decimal
Basically the SQL that I'm trying to achive via BindingSource.Filter is
SELECT SUM(Quantity) FROM Inventory_View WHERE productCode = 'prodCode' AND Location IN ( 'loc1', 'loc2', etc...)
The view structure looks like
ProductCode varchar
LocationCode varchar
Quantity decimal
If you have just une record for each Product and location, you don't need groups. If you have more records, I suggest you to add an Id or another identificadion fields.
You need to use this query:
SELECT SUM(Quantity) TotalQuantity FROM Inventory_View WHERE productCode = 'prodCode' AND Location IN ( 'loc1', 'loc2', etc...) GROUP BY Location
and then filter using TotalQuantity
You need to use this query:
SELECT SUM(Quantity) TotalQuantity FROM Inventory_View WHERE productCode = 'prodCode' AND Location IN ( 'loc1', 'loc2', etc...) GROUP BY Location
and then filter using TotalQuantity
ASKER
What I'm trying to do is use the filter for "Location IN ('loc1','loc2')". You can't use the "IN" in the typed data set because you can't create paramaters when you don't now how many there will be. For example one user might be "Filtering" to view only 3 locationCodes and another user might want to view 10 locationCodes.
What do you mean by "filter using TotalQuantity"
What do you mean by "filter using TotalQuantity"
ok. Now I understand your question.
You can prepare the filterString to Appending all the values you want to have in the IN and then filter
You can prepare the filterString to Appending all the values you want to have in the IN and then filter
ASKER
When I do this for a query that returns a single value, like SUM(), it get the sum for ALL rows. Could it be a timing thing. Where the table adapter fills with the single SUM() value and at that point it is to late to filter because the SUM() function has already been executed?
Thanks for your help on this! I have found a work around but it is even more messy.
Thanks for your help on this! I have found a work around but it is even more messy.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.