Bind (Populate) ASP.Net DropDownList on Selected Index Change with Stored Procedure using C# and VB.Net

Hi AngelUser,

Check this sample. now take its reference.

Database

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

Download Northwind Database

Storeprocedure

Country

CREATE PROCEDURE Customers_GetCountries    
AS    
BEGIN    
	SET NOCOUNT ON;    
	SELECT DISTINCT [Country]    
	FROM Customers    
	WHERE Country IS NOT NULL  
END 

City

CREATE PROCEDURE [dbo].[Customers_GetCities]  
@Country VARCHAR(20)
AS  
BEGIN  
	SET NOCOUNT ON;    
	SELECT
	DISTINCT [City] AS [CityName] 
    FROM [Customers]  
	WHERE Country=@Country OR @Country IS NULL AND City IS NOT NULL 
	ORDER BY [City]  
END  

HTML

Select Country : <asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="true" OnSelectedIndexChanged="OnSelectedCountry">
</asp:DropDownList><br /><br />
Select State : <asp:DropDownList ID="ddlCity" runat="server" AutoPostBack="true" OnSelectedIndexChanged="OnSelectedCity">
</asp:DropDownList><br />
<asp:Label ID="lblMessage" Text="" Style="color: Red;" runat="server" />

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 (!this.IsPostBack)
    {
        this.BindCountries();
    }
}

protected void OnSelectedCountry(object sender, EventArgs e)
{
    string country = ddlCountry.SelectedValue;
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("Customers_GetCities", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Country", !string.IsNullOrEmpty(country) ? country : "");
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = cmd;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                ddlCity.DataSource = dt;
                ddlCity.DataTextField = "CityName";
                ddlCity.DataValueField = "CityName";
                ddlCity.DataBind();
                ddlCity.Items.Insert(0, new ListItem
                                        {
                                            Text = "--Select City--",
                                            Value = ""
                                        });
            }
        }
    }
    lblMessage.Text = "Selected Country is " + ddlCountry.SelectedValue;
}

protected void OnSelectedCity(object sender, EventArgs e)
{
    lblMessage.Text = "Selected Country is " + ddlCountry.SelectedValue + " <br/>and City is " + ddlCity.SelectedValue;
}

private void BindCountries()
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("Customers_GetCountries", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = cmd;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                this.ddlCountry.DataSource = dt;
                this.ddlCountry.DataTextField = "Country";
                this.ddlCountry.DataValueField = "Country";
                this.ddlCountry.DataBind();
                this.ddlCountry.Items.Insert(0, new ListItem { Text = "--Select Country--", Value = "" });
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindCountries()
    End If
End Sub

Protected Sub OnSelectedCountry(ByVal sender As Object, ByVal e As EventArgs)
    Dim country As String = ddlCountry.SelectedValue
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("Customers_GetCities", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Country", If(Not String.IsNullOrEmpty(country), country, ""))
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                sda.SelectCommand = cmd
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                ddlCity.DataSource = dt
                ddlCity.DataTextField = "CityName"
                ddlCity.DataValueField = "CityName"
                ddlCity.DataBind()
                ddlCity.Items.Insert(0, New ListItem With
                                        {
                                         .Text = "--Select City--",
                                         .Value = ""
                                        })
            End Using
        End Using
    End Using
    lblMessage.Text = "Selected Country is " & ddlCountry.SelectedValue
End Sub

Protected Sub OnSelectedCity(ByVal sender As Object, ByVal e As EventArgs)
    lblMessage.Text = "Selected Country is " & ddlCountry.SelectedValue & " <br/>and City is " + ddlCity.SelectedValue
End Sub

Private Sub BindCountries()
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("Customers_GetCountries", con)
            cmd.CommandType = CommandType.StoredProcedure
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                sda.SelectCommand = cmd
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                Me.ddlCountry.DataSource = dt
                Me.ddlCountry.DataTextField = "Country"
                Me.ddlCountry.DataValueField = "Country"
                Me.ddlCountry.DataBind()
                Me.ddlCountry.Items.Insert(0, New ListItem With {
                    .Text = "--Select Country--",
                    .Value = ""
                })
            End Using
        End Using
    End Using
End Sub

Screenshot