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