Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Filter Asp.net Gridview with Dropdownlist using FilterExpression in SqlDatasource

Mar 23, 2015
Introduction

Here I will explain how to filter
asp.net gridview data with dropdownlist using filter expression in sqldatasource in c#, vb.net or filter gridview records with dropdownlist selection in asp.net using sqldatasource in c#, vb.net.

Description:
  
In previous articles I explained
Delete multiple rows in gridview using checkbox in asp.net, Get selected row cell value from gridview in asp.net, retrieve images from path stored in database in asp.net, Get textbox value palced in gridview in asp.net and many articles relating to asp.net, c#,vb.net and jQuery. Now I will explain how to filter asp.net gridview data with dropdownlist using filter expression in sqldatasource in c#, vb.net.

Before implement this example first design one table UserDetails in your database as shown below

Column Name
Data Type
Allow Nulls
UserId
Int(IDENTITY=TRUE)
Yes
UserName
varchar(50)
Yes
Education
varchar(50)
Yes
Location
varchar(50)
Yes
Once table created in database enter some dummy data to test application after that create new windows form application project for that follow steps

Now open your aspx page and write the code like as shown below


<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title> Beautiful Gridview with Filtering Option using </title>
<%--Styles to Change the appearance of Girdview --%>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
Table.Gridview{border:solid 1px #df5015;}
.GridviewTable{border:none}
.GridviewTable td{margin-top:0;padding: 0; vertical-align:middle }
.GridviewTable tr{color: White; background-color: #df5015; height: 30px; text-align:center}
.Gridview th{color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;padding:0.5em 0.5em 0.5em 0.5em;text-align:center}
.Gridview td{border-bottom-color:#f0f2da;border-right-color:#f0f2da;padding:0.5em 0.5em 0.5em 0.5em;}
.Gridview tr{color: Black; background-color: White; text-align:left}
:link,:visited { color: #DF4F13; text-decoration:none }
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDiv">
<table style="width: 420px" border="0" cellpadding="0" cellspacing="1" class="GridviewTable">
<tr >
<td style="width: 40px;">
UserId
</td>
<td style="width: 120px;" >
LastName
</td>
<td style="width: 130px;">
UserName
</td>
<td style="width: 130px;">
Location
</td>
</tr>
<tr >
<td style="width: 40px;">
</td>
<td style="width: 120px;">
</td>
<td style="width: 130px;">
<asp:DropDownList ID="ddlUserName" runat="server" DataSourceID="dsUserName" DataValueField="UserName" AutoPostBack="true" Width="120px" Font-Size="11px" AppendDataBoundItems="true">
<asp:ListItem Text="All" Value="%"/>
</asp:DropDownList>
</td>
<td style="width: 130px;">
<asp:DropDownList ID="ddlLocation" runat="server" DataSourceID="dsLocation" DataValueField="Location" AutoPostBack="true" Width="120px" Font-Size="11px" AppendDataBoundItems="true">
<asp:ListItem Text="All" Value="%"/>
</asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="4">
<asp:GridView runat="server" ID="gvdetails" ShowHeader="false" AllowPaging="true" PageSize="10" DataSourceID="dsdetails" AutoGenerateColumns="false" Width="420px"  CssClass="Gridview">
<Columns>
<asp:BoundField DataField="UserId" HeaderText="UserId" ItemStyle-Width="40px" />
<asp:BoundField DataField="Education" HeaderText="Education" ItemStyle-Width="120px" />
<asp:BoundField DataField="UserName" HeaderText="UserName" ItemStyle-Width="130px"/>
<asp:BoundField DataField="Location" HeaderText="Location" ItemStyle-Width="130px"/>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
<asp:SqlDataSource ID="dsUserName" runat="server" ConnectionString="<%$ConnectionStrings:dbconnection %>" SelectCommand="Select Distinct UserName from userdetails"></asp:SqlDataSource>
<asp:SqlDataSource ID="dsLocation" runat="server" ConnectionString="<%$ConnectionStrings:dbconnection %>" SelectCommand="Select Distinct Location from userdetails"></asp:SqlDataSource>
<asp:SqlDataSource ID="dsdetails" runat="server" ConnectionString="<%$ConnectionStrings:dbconnection %>" SelectCommand="select * from userdetails" FilterExpression=" UserName Like '{0}%' and Location Like '{1}%'">
<FilterParameters>
<asp:ControlParameter Name="UserName" ControlID="ddlUserName" PropertyName="SelectedValue" />
<asp:ControlParameter Name="Location" ControlID="ddlLocation" PropertyName="SelectedValue" />
</FilterParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
If you observe above code in header section I written css classes by using those we can change the appearance of gridview and written code to bind dropdownlists, gridview and bind the gridview records based on dropdownlists selection.

Here don’t forgot to set the connection string in web.config file here I am getting database connection from web.config file for that reason you need to set the connectionstring in web.config file like as shown below


<connectionStrings>
<add name="dbconnection" connectionString="Data Source=Suresh;Initial Catalog=MySampleDB;Integrated Security=true" providerName="System.Data.SqlClient" />
</connectionStrings>
Demo


Filter Asp.net Gridview with Dropdownlist using FilterExpression in SqlDatasource

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

1 comments :

Narsimha .Hyd said...

It is possible to do with Datagrid instead of Gridview

Give your Valuable Comments

Other Related Posts

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.