In this article I will explain how to populate (display) data in in GridView based on DropDownList Selected value (Selection) in ASP.Net using C# and VB.Net.
The GridView data will be populated when the DropDownList selected item is changed.
Database
For this tutorial I am using Microsoft’s Northwind Database. You can download it using the link below.
Download NorthWind Database
HTML Markup
The
following HTML Markup consists of an ASP.Net GridView with a DropDownList inside
Header Template of the Country TemplateField column. The DropDownList has been assigned a SelectedIndexChanged event handler and also AutoPostBack property is set to True.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns = "false"
AllowPaging = "true" PageSize = "10" Font-Names = "Arial"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
HeaderStyle-BackColor = "green" OnPageIndexChanging = "OnPaging">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:TemplateField>
<HeaderTemplate>
Country:
<asp:DropDownList ID="ddlCountry" runat="server"
OnSelectedIndexChanged = "CountryChanged" AutoPostBack = "true"
AppendDataBoundItems = "true">
<asp:ListItem Text = "ALL" Value = "ALL"></asp:ListItem>
<asp:ListItem Text = "Top 10" Value = "10"></asp:ListItem>
</asp:DropDownList>
</HeaderTemplate>
<ItemTemplate>
<%# Eval("Country") %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="PostalCode" HeaderText="Postal Code" />
</Columns>
</asp:GridView>
Stored Procedure
The following
Stored Procedure is used to filter the GridView records. The selected value of
the DropDownList will be passed as parameter to the Stored Procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spx_GetCustomers
@Filter VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
IF @Filter = 'ALL'
SELECT ContactName, City, Country, PostalCode
FROM Customers
ELSE IF @Filter = '10'
SELECT TOP 10 ContactName, City, Country, PostalCode
FROM Customers
ELSE
SELECT ContactName, City, Country, PostalCode
FROM Customers WHERE Country=@Filter
END
GO
Binding the GridView
Inside
the Page Load event handler of the Page, the BindGrid method is called which populates
the GridView and the Country DropDownList. A ViewState variable is used to store
the value of the Filter The default value is set to All in order to load all
records intially.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["Filter"] = "ALL";
BindGrid();
}
}
private void BindGrid()
{
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("spx_GetCustomers");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Filter", ViewState["Filter"].ToString());
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
DropDownList ddlCountry =
(DropDownList)GridView1.HeaderRow.FindControl("ddlCountry");
this.BindCountryList(ddlCountry);
}
VB.Net
Protected Sub Page_Load(sender As Object , e As EventArgs) Handles Me.Load
If Not IsPostBack Then
ViewState("Filter") = "ALL"
Me.BindGrid()
End If
End Sub
Private Sub BindGrid()
Dim dt As New DataTable()
Dim strConnString As String = System.Configuration.ConfigurationManager _
.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim sda As New SqlDataAdapter()
Dim cmd As New SqlCommand("spx_GetCustomers")
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Filter", ViewState("Filter"))
cmd.Connection = con
sda.SelectCommand = cmd
sda.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()
Dim ddlCountry As DropDownList = DirectCast(GridView1.HeaderRow _
.FindControl("ddlCountry"), DropDownList)
Me.BindCountryList(ddlCountry)
End Sub
Populating AutoFilter DropDownList
The following method is used to populate the AutoFilter DropDownList which present in the Header Template of the ASP.Net GridView control.
It selects all the Distinct countries present in the Customers Table of the
Nortwind database.
C#
private void BindCountryList(DropDownList ddlCountry)
{
String strConnString = System.Configuration.ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("select distinct Country" +
" from customers");
cmd.Connection = con;
con.Open();
ddlCountry.DataSource = cmd.ExecuteReader();
ddlCountry.DataTextField = "Country";
ddlCountry.DataValueField = "Country";
ddlCountry.DataBind();
con.Close();
ddlCountry.Items.FindByValue(ViewState["Filter"].ToString())
.Selected = true;
}
VB.Net
Private Sub BindCountryList(ByVal ddlCountry As DropDownList)
Dim strConnString As String = System.Configuration.ConfigurationManager _
.ConnectionStrings("conString").ConnectionString()
Dim con As New SqlConnection(strConnString)
Dim sda As New SqlDataAdapter()
Dim cmd As New SqlCommand("select distinct Country" & _
" from customers")
cmd.Connection = con
con.Open()
ddlCountry.DataSource = cmd.ExecuteReader()
ddlCountry.DataTextField = "Country"
ddlCountry.DataValueField = "Country"
ddlCountry.DataBind()
con.Close()
ddlCountry.Items.FindByValue(ViewState("Filter").ToString()) _
.Selected = True
End Sub
AutoFilter Functionality
Inside
the SelectedIndexChanged event handler of the DropDownList, the GridView records are
filtered based on the Selected value of the DropDownList.
C#
protected void CountryChanged(object sender, EventArgs e)
{
DropDownList ddlCountry = (DropDownList)sender;
ViewState["Filter"] = ddlCountry.SelectedValue;
this.BindGrid();
}
VB.Net
Protected Sub CountryChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim ddlCountry As DropDownList = DirectCast(sender, DropDownList)
ViewState("Filter") = ddlCountry.SelectedValue
Me.BindGrid()
End Sub
PageIndexChaging event
Inside the PageIndexChanging event
handler of the GridView, the GridView is again populated with the new PageIndex using the BindGrid method.
C#
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
this.BindGrid();
}
VB.Net
Protected Sub OnPaging(sender As Object , e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
Me.BindGrid()
End Sub
Screenshot
![Populate (Display) data in GridView based on DropDownList Selected value (Selection) in ASP.Net]()
Demo
Downloads
Download Code (5.65 kb)
