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
View Demo


Downloads

Download Code (5.65 kb)