Dynamically Change FilterExpression

I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview.

Something like this example...http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx

Here is some of my code..

PrivateSub ApplyFilter()

Dim _filterExpressionAsString =""

If (Not DropDownList1.SelectedIndex = 0)And (DropDownList2.SelectedIndex = 0)And (DropDownList3.SelectedIndex = 0)Then

_filterExpression ="CategoryName = '{0}'"

EndIf

Me.SqlDataSource1.FilterExpression = _filterExpression

EndSub

ProtectedSub DropDownList1_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles DropDownList1.SelectedIndexChanged

ApplyFilter()

EndSub

But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.

Hi nfalk,

a) :It seems to me that you have specified an filterexpression to use parameter while didn't add any values to the FilterParameters collection. As to how to use filter expression, see the code example below:

msdn:

 <asp:DropDownList id="DropDownList1" runat="server" AutoPostBack="True"> <asp:ListItem Selected>Sales Representative</asp:ListItem> <asp:ListItem>Sales Manager</asp:ListItem> <asp:ListItem>Vice President, Sales</asp:ListItem> </asp:DropDownList></p> <asp:SqlDataSource id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees" FilterExpression="Title='{0}'"> <FilterParameters> <asp:ControlParameter Name="Title" ControlId="DropDownList1" PropertyName="SelectedValue"/> </FilterParameters> </asp:SqlDataSource>

b): The filter expression only take affect when datasource.select() method is called. Thus only modifying the filter expression won't change your gridview content. You need to call gridview.databind() again

in your DropDownList1_SelectedIndexChanged method.

Hope my suggestion helps

|||

Hello Bo Chen,

a) I did have the filter parameters set I just forgot to mention it in the posting.

b) I was forgetting to call the DataBind() in the event handler. After I added it everything works fine.

Thanks for the reply.

|||

I am trying to do something similar but can't get it to work. I have the following code running every time the web page reloads:

ReportDataSource.FilterExpression ="SurveyID=" & SurveyList.SelectedValue.ToString

SurveyReportViewer.DataBind()

I have a ReportViewer (SurveyReportViewer) based on an ObjectDataSource (ReportDataSource). Every time the page reloads it checks the SurveyList drop-down to get the new ID. This works fine the first time the page is loaded, but the filter doesn't change after that. I know it's not supposed to work until the Select is called again, but I thought that's what the DataBind method did (I had tried using DataBind on the ObjectDataSource as well as the ReportViewer but it didn't make any difference). Since it does work the first time through, I'm assuming most everything is set up correctly and that I'm just missing something that's required to refresh the data source. Any suggestions?

|||

What I did was create a ApplyFilter() method with my logic to change the FilterExpression. And in the DropDownList SelectedIndexChanged event call the applyfilter and rebind to data source...

ProtectedSub DropDownList2_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles DropDownList2.SelectedIndexChanged

ApplyFilter()

Me.SqlDataSource1.DataBind()

EndSub

Also I don't know if it makes a difference but I used Filter parameters...

<asp:ControlParameterControlID="DropDownList2"DefaultValue="*"Name="Filter"PropertyName="SelectedValue"/>

In the ApplyFilter()...

Me.SqlDataSource1.FilterExpression="CategoryName = '{0}' "

Hope this helps

|||

Well I gave it a try but had the same results. I may have to try using a SqlDataSource instead of ObjectDataSource and see if there's a difference there. This is probably what I get for using wizards instead of coding it myself...

Dynamic SQL Queries!

An ASP application retrieves the DISTINCT records from all the columns
of a SQL Server DB table & populates them in drop-down lists. The no.
of drop-down lists on the web page depends upon the no. of columns in
the DB table. For e.g. if the DB table has, say, 5 columns, the web
page will show 5 drop-down lists.
Assume that the DB table stores information pertaining to books like
book name, category to which the book belongs to like sports, science,
music etc., author, publisher, publishing date etc.
Now suppose that a user selects an author named Author1 from the
drop-down list. When he does so, the page should get submitted & all
books that Author1 has penned should be displayed to the user. For e.g.
if Author1 has written 10 books, the user should be shown 10 records.
Now after Author1 has been selected & the appropriate records displayed
to the user, suppose the user selects an option from another drop-down
list like for e.g. the publisher drop-down list. An author can get his
books published by different publishers. When the user selects, say,
Publisher1, from the drop-down list, the user should now be displayed
all the records that Author1 has written BUT which have been published
by Publisher1 only. Now Author1 has written 10 books. Out of these 10
books, Publisher1 has published 4 books. So under such circumstances, 4
records should be retrieved & displayed to the user. The SQL query
would be
SELECT * FROM tblBooks WHERE Author='Author1' AND
Publisher='Publisher1'
The problem I am having is in adding the second WHERE clause i.e. 'AND
Publisher='Publisher1' in the SELECT query. Please note that all the
drop-down lists EXCEPT for the author drop-down list should change
again & contain only those records as options which are common to both
Author1 & Publisher1.
Arpan> The problem I am having is in adding the second WHERE clause i.e. 'AND
> Publisher='Publisher1' in the SELECT query.
So what is the problem? That query is syntactically valid so it isn't
obvious what your question is.
I would have expected separate tables for publisher and books joined by
a third table for the many-to-many relationship. Is it the join that
you have a problem with? For example:
SELECT ...
FROM tblBooks AS B
JOIN tblBookPublishers AS J
ON B.isbn = J.isbn
JOIN tblPublishers AS P
ON J.publisher_id = P.publisher_id
WHERE B.Author='Author1'
AND P.Publisher='Publisher1' ;
And by the way, books can have more than one author too, so author
probably shouldn't appear in the books table and you need at least two
more tables there.
If you need more help, please read this first:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||I do understand that your are right in saying that the records should
have been in seperate tables but the fact is the database that my
clients have wasn't created by a database expert; so they have all the
records in one table only & don't wish to seperate the records in
different tables because of time constraint.
Had related records been in different tables, there wouldn't have been
any problem but since that isn't the case, my problems have increased.
Any further suggestions?|||> Any further suggestions?
What is your question?
Please also include a CREATE TABLE statement if your question is about
a query.
David Portas
SQL Server MVP
--