How do I apply a filter to a BindingSource when doing a scalar query using a TableAdapter?

gtar
gtar used Ask the Experts™
on
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));

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
How are you grouping?

Author

Commented:
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.

Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.


Commented:
The SUM aggregate function works only for GROUPed BY command.

Can you post the table structure and the query you want to run?

Author

Commented:
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

Commented:
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

Author

Commented:
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"

Commented:
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

Author

Commented:
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.
Commented:
You have to add the GROUP BY Location

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial