Bind (Populate) GridView based on cascading DropDownList using C# and VB.Net in ASP.Net

Hi rajeshg12,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<table>
    <tr>
        <td>Country</td>
        <td>:
            <asp:DropDownList runat="server" ID="ddlCountries" AutoPostBack="True" OnSelectedIndexChanged="CountryChanged"
                AppendDataBoundItems="true">
                <asp:ListItem Value="0" Text="---Select---"></asp:ListItem>
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td>City</td>
        <td>:
            <asp:DropDownList runat="server" ID="ddlCities" AutoPostBack="True" OnSelectedIndexChanged="CityChanged"
                AppendDataBoundItems="true">
                <asp:ListItem Value="0" Text="---Select---"></asp:ListItem>
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td colspan="2" align="center">
            <asp:GridView runat="server" ID="gvEmployees" AutoGenerateColumns="false" OnRowDataBound="OnRowDataBound">
                <Columns>
                    <asp:TemplateField HeaderText="Row No">
                        <ItemTemplate>
                            <%# Container.DataItemIndex + 1 %>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="EmployeeID" HeaderText="Id" />
                    <asp:BoundField DataField="Name" HeaderText="Name" />
                    <asp:BoundField DataField="City" HeaderText="City" />
                    <asp:BoundField DataField="Country" HeaderText="Country" />
                </Columns>
            </asp:GridView>
        </td>
    </tr>
</table>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        // Get Country and City as per your logic.
        string country = "USA";
        string city = "Seattle";

        // Populate Country DropDownList.
        SqlCommand cmdCountry = new SqlCommand();
        cmdCountry.CommandText = "SELECT DISTINCT Country FROM Employees";
        PopulateDropDownList(ddlCountries, "Country", "Country", cmdCountry);
        if (ddlCountries.Items.FindByValue(country) != null)
        {
            // Set Country DropDownList selected value.
            ddlCountries.Items.FindByValue(country).Selected = true;
        }

        // Populate City DropDownList.
        SqlCommand cmdCity = new SqlCommand();
        cmdCity.CommandText = "SELECT DISTINCT City FROM Employees WHERE Country = @Country";
        cmdCity.Parameters.AddWithValue("@Country", ddlCountries.SelectedValue);
        PopulateDropDownList(ddlCities, "City", "City", cmdCity);
        if (ddlCities.Items.FindByValue(city) != null)
        {
            // Set City DropDownList selected value.
            ddlCities.Items.FindByValue(city).Selected = true;
        }

        // Populate GridView based on Country and City DropDownList selected values.
        SqlCommand cmdGrid = new SqlCommand();
        cmdGrid.CommandText = "SELECT EmployeeID,FirstName + ' ' + LastName Name,City,Country FROM Employees WHERE Country = @Country AND City = @City";
        cmdGrid.Parameters.AddWithValue("@Country", ddlCountries.SelectedValue);
        cmdGrid.Parameters.AddWithValue("@City", ddlCities.SelectedValue);
        DataTable dt = GetData(cmdGrid);
        gvEmployees.DataSource = dt;
        gvEmployees.DataBind();
    }
}
protected void CountryChanged(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "SELECT DISTINCT City FROM Employees WHERE Country = @Country";
    cmd.Parameters.AddWithValue("@Country", ddlCountries.SelectedValue);
    PopulateDropDownList(ddlCities, "City", "City", cmd);
    ddlCities.Enabled = ddlCountries.SelectedIndex > 0;
    gvEmployees.DataSource = null;
    gvEmployees.DataBind();
}
protected void CityChanged(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "SELECT EmployeeID,FirstName + ' ' + LastName Name,City,Country FROM Employees WHERE Country = @Country AND City = @City";
    cmd.Parameters.AddWithValue("@Country", ddlCountries.SelectedValue);
    cmd.Parameters.AddWithValue("@City", ddlCities.SelectedValue);
    DataTable dt = GetData(cmd);
    gvEmployees.DataSource = dt;
    gvEmployees.DataBind();
}
private void PopulateDropDownList(DropDownList ddl, string dataTextField, string dataValueField, SqlCommand cmd)
{
    DataTable dt = GetData(cmd);
    ddl.Items.Clear();
    ddl.DataTextField = dataTextField;
    ddl.DataValueField = dataValueField;
    ddl.DataSource = dt;
    ddl.DataBind();
    ddl.Items.Insert(0, new ListItem() { Text = "---Select---", Value = "0" });
}
private DataTable GetData(SqlCommand cmd)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        cmd.Connection = con;
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            da.Fill(dt);

            return dt;
        }
    }
}
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        string country = ddlCountries.SelectedValue;
        string city = ddlCities.SelectedValue;
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        ' Get Country and City as per your logic.
        Dim country As String = "USA"
        Dim city As String = "Seattle"

        ' Populate Country DropDownList.
        Dim cmdCountry As SqlCommand = New SqlCommand()
        cmdCountry.CommandText = "SELECT DISTINCT Country FROM Employees"
        PopulateDropDownList(ddlCountries, "Country", "Country", cmdCountry)
        If ddlCountries.Items.FindByValue(country) IsNot Nothing Then
            ' Set Country DropDownList selected value.
            ddlCountries.Items.FindByValue(country).Selected = True
        End If

        ' Populate City DropDownList.
        Dim cmdCity As SqlCommand = New SqlCommand()
        cmdCity.CommandText = "SELECT DISTINCT City FROM Employees WHERE Country = @Country"
        cmdCity.Parameters.AddWithValue("@Country", ddlCountries.SelectedValue)
        PopulateDropDownList(ddlCities, "City", "City", cmdCity)
        If ddlCities.Items.FindByValue(city) IsNot Nothing Then
            ' Set City DropDownList selected value.
            ddlCities.Items.FindByValue(city).Selected = True
        End If

        ' Populate GridView based on Country and City DropDownList selected values.
        Dim cmdGrid As SqlCommand = New SqlCommand()
        cmdGrid.CommandText = "SELECT EmployeeID,FirstName + ' ' + LastName Name,City,Country FROM Employees WHERE Country = @Country AND City = @City"
        cmdGrid.Parameters.AddWithValue("@Country", ddlCountries.SelectedValue)
        cmdGrid.Parameters.AddWithValue("@City", ddlCities.SelectedValue)
        Dim dt As DataTable = GetData(cmdGrid)
        gvEmployees.DataSource = dt
        gvEmployees.DataBind()
    End If
End Sub

Protected Sub CountryChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim cmd As SqlCommand = New SqlCommand()
    cmd.CommandText = "SELECT DISTINCT City FROM Employees WHERE Country = @Country"
    cmd.Parameters.AddWithValue("@Country", ddlCountries.SelectedValue)
    PopulateDropDownList(ddlCities, "City", "City", cmd)
    ddlCities.Enabled = ddlCountries.SelectedIndex > 0
    gvEmployees.DataSource = Nothing
    gvEmployees.DataBind()
End Sub

Protected Sub CityChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim cmd As SqlCommand = New SqlCommand()
    cmd.CommandText = "SELECT EmployeeID,FirstName + ' ' + LastName Name,City,Country FROM Employees WHERE Country = @Country AND City = @City"
    cmd.Parameters.AddWithValue("@Country", ddlCountries.SelectedValue)
    cmd.Parameters.AddWithValue("@City", ddlCities.SelectedValue)
    Dim dt As DataTable = GetData(cmd)
    gvEmployees.DataSource = dt
    gvEmployees.DataBind()
End Sub

Private Sub PopulateDropDownList(ByVal ddl As DropDownList, ByVal dataTextField As String, ByVal dataValueField As String, ByVal cmd As SqlCommand)
    Dim dt As DataTable = GetData(cmd)
    ddl.Items.Clear()
    ddl.DataTextField = dataTextField
    ddl.DataValueField = dataValueField
    ddl.DataSource = dt
    ddl.DataBind()
    ddl.Items.Insert(0, New ListItem() With {.Text = "---Select---", .Value = "0"})
End Sub

Private Function GetData(ByVal cmd As SqlCommand) As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        cmd.Connection = con
        Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
            Dim dt As DataTable = New DataTable()
            da.Fill(dt)
            Return dt
        End Using
    End Using
End Function

Protected Sub OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then
        Dim country As String = ddlCountries.SelectedValue
        Dim city As String = ddlCities.SelectedValue
    End If
End Sub

Screenshots

Values in code