GridView Filter Expression With DropDownList ASP.NET

This example explains how to Filter GridView With DropDownList In ASP.NET Using FilterExpression And Filter Paramaters Or GridView Filtering with Sql Server And SqlDataSource.

Filter GridView With DropDownList In ASP.NET
I m using northwind database and customers table to show data and filter gridview with dropdownlist.

First of all open aspx page in design view and place 2 dropdownlist, 1 gridview and 3 SqlDatasource on the page.

Configure all 3 sqldatasources as according to code mentioned below. and use them for datasource to populate city dropdown, country dropdown and gridview.

You can also read ModalPopUp extender in Gridview to know how to configure SqlDataSource.


HTML Markup to Populate Dropdowns
<asp:DropDownList ID="ddlCity" runat="server" 
                  AppendDataBoundItems="True" 
                  AutoPostBack="True" 
                  DataSourceID="sqlDataSourceCity" 
                  DataTextField="City" 
                  DataValueField="City" Width="100px">
 <asp:ListItem Value="%">All</asp:ListItem>
 </asp:DropDownList>

<asp:SqlDataSource ID="sqlDataSourceCity" runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT DISTINCT City FROM Customers">
</asp:SqlDataSource>


<asp:DropDownList ID="ddlCountry" runat="server" 
                  AppendDataBoundItems="True" 
                  AutoPostBack="True" 
                  DataSourceID="sqlDataSourceCountry" 
                  DataTextField="Country" 
                  DataValueField="Country" Width="100px">
<asp:ListItem Value="%">All</asp:ListItem>
</asp:DropDownList>


<asp:SqlDataSource ID="sqlDataSourceCountry" runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT DISTINCT [Country] FROM [Customers]">
</asp:SqlDataSource>

Now Configure third sqldatasource to populate gridview based on filter expression as mentioned below

HTML markup of gridview and sqldatasource with filter expression
<asp:GridView ID="GridView1" runat="server" 
              AllowPaging="True" 
              DataSourceID="sqlDataSourceGridView" 
              AutoGenerateColumns="False"
              CssClass="GridViewStyle" 
              GridLines="None" Width="650px" 
              ShowHeader="false">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Customer ID"/>
<asp:BoundField DataField="CompanyName" HeaderText="Company"/>
<asp:BoundField DataField="ContactName" HeaderText="Name"/>
<asp:BoundField DataField="City" HeaderText="city"/>
<asp:BoundField DataField="Country" HeaderText="Country"/>
</Columns>
</asp:GridView>


<asp:SqlDataSource ID="sqlDataSourceGridView" 
                   runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], 
[City], [Country] FROM [Customers]" 
FilterExpression="[City] like '{0}%' and [Country] like '{1}%'">
<FilterParameters>
<asp:ControlParameter ControlID="ddlCity" Name="City" 
                      PropertyName="SelectedValue" 
                      Type="String" />
<asp:ControlParameter ControlID="ddlCountry" Name="Country" 
                      PropertyName="SelectedValue" 
                      Type="String" />
</FilterParameters>
</asp:SqlDataSource>

Build and run the application.

Download Sample Code


If you like this post than join us or share

11 comments:

Mindy and Marty said...

I left a comment on your post in 2009 about a code for page views on your blog. Can you see my question about how to reset the page views for each post I make so it starts the count at zero again. I tried removing it and doing a whole new one but it just picks right back up where it left off. How can I reset the count?>!
thanks! my blog is mindymarty.blogspot.com


kvministries said...

Hi, I got the code from your blog. But I am also with the same question, even I do not know the trick.


shiko said...

can u writ co C#


Anonymous said...

thanks !


comment system said...

Very well written post ! I just tried the above given code and it worked great. Thank a lot. You saved my couple of hours :))


sameer said...

hi i have dropdownlist, i binding the data in the dropdown list, my problem is , how to set default value is NULL ?


Anonymous said...

Cannot perform 'Like' operation on System.Decimal and System.String.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.EvaluateException: Cannot perform 'Like' operation on System.Decimal and System.String.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[EvaluateException: Cannot perform 'Like' operation on System.Decimal and System.String.]
System.Data.LikeNode.Eval(DataRow row, DataRowVersion version) +475
System.Data.BinaryNode.EvalBinaryOp(Int32 op, ExpressionNode left, ExpressionNode right, DataRow row, DataRowVersion version, Int32[] recordNos) +12138
System.Data.BinaryNode.Eval(DataRow row, DataRowVersion version) +29
System.Data.DataExpression.Invoke(DataRow row, DataRowVersion version) +151
System.Data.Index.AcceptRecord(Int32 record, IFilter filter) +103
System.Data.Index.InitRecords(IFilter filter) +303
System.Data.Index..ctor(DataTable table, Int32[] ndexDesc, IndexField[] indexFields, Comparison`1 comparison, DataViewRowState recordStates, IFilter rowFilter) +491
System.Data.DataTable.GetIndex(IndexField[] indexDesc, DataViewRowState recordStates, IFilter rowFilter) +228
System.Data.DataView.UpdateIndex(Boolean force, Boolean fireEvent) +165
System.Data.DataView.UpdateIndex(Boolean force) +12
System.Data.DataView.SetIndex2(String newSort, DataViewRowState newRowStates, IFilter newRowFilter, Boolean fireEvent) +113
System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, IFilter newRowFilter) +17
System.Data.DataView.set_RowFilter(String value) +160
System.Web.UI.WebControls.FilteredDataSetHelper.CreateFilteredDataView(DataTable table, String sortExpression, String filterExpression, IDictionary filterParameters) +396
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2012
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +75
System.Web.UI.Control.EnsureChildControls() +102
System.Web.UI.Control.PreRenderRecursiveInternal() +42
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2496

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.225


Anonymous said...

hi

i have a praticular problem actually the problem is that i have a grid having some data in it and i want my grid to be filter with text box i mean to say when i enter some text in textbox so grid should only show the relevant data.
.


Unknown said...

@Above: You can change dropdownlists to textbox and propertyname to text in filterparameters

Check this sample code


Anonymous said...

Do you have the stylesheet.css that goes with this project? Thanks!


Bob said...

Very nice example. I also would like the css file. The grid looks great.


Find More Articles