Cascading dropdown in asp.net - Part 22

Suggested Videos
Part 16 - Dropdownlist in asp.net
Part 17 - Data bind dropdownlist with data from the database
Part 21 - Retrieving selected item text, value and index of the dropdownlist

In this video we will discuss about cascading dropdownlists. First create the required tables and populate them, with some sample data using the SQL script below.

Create Table tblContinents
(
ContinentId int identity primary key,
ContinentName nvarchar(50)
)

Insert into tblContinents values ('Asia')
Insert into tblContinents values ('Europe')
Insert into tblContinents values ('South America')



Create Table tblCountries
(
CountryId int identity primary key,
CountryName nvarchar(50),
ContinentId int foreign key references dbo.tblContinents(ContinentId)
)

Insert into tblCountries values ('India', 1)
Insert into tblCountries values ('Japan', 1)
Insert into tblCountries values ('Malaysia', 1)

Insert into tblCountries values ('United Kingdom', 2)
Insert into tblCountries values ('France', 2)
Insert into tblCountries values ('Germany', 2)

Insert into tblCountries values ('Argentina', 3)
Insert into tblCountries values ('Brazil', 3)
Insert into tblCountries values ('Colombia', 3)



Create Table tblCities
(
CityId int identity primary key,
CityName nvarchar(50),
CountryId int foreign key references dbo.tblCountries(CountryId)
)

Insert into tblCities values ('Bangalore', 1)
Insert into tblCities values ('Chennai', 1)
Insert into tblCities values ('Mumbai', 1)

Insert into tblCities values ('Tokyo', 2)
Insert into tblCities values ('Hiroshima', 2)
Insert into tblCities values ('Saku', 2)

Insert into tblCities values ('Kuala Lumpur', 3)
Insert into tblCities values ('Ipoh', 3)
Insert into tblCities values ('Tawau', 3)

Insert into tblCities values ('London', 4)
Insert into tblCities values ('Manchester', 4)
Insert into tblCities values ('Birmingham', 4)

Insert into tblCities values ('Paris', 5)
Insert into tblCities values ('Cannes', 5)
Insert into tblCities values ('Nice', 5)

Insert into tblCities values ('Frankfurt', 6)
Insert into tblCities values ('Eutin', 6)
Insert into tblCities values ('Alsfeld', 6)

Insert into tblCities values ('Rosario', 7)
Insert into tblCities values ('Salta', 7)
Insert into tblCities values ('Corrientes', 7)

Insert into tblCities values ('Rio de Janeiro', 8)
Insert into tblCities values ('Salvador', 8)
Insert into tblCities values ('Brasília', 8)

Insert into tblCities values ('Cali', 9)
Insert into tblCities values ('Montería', 9)
Insert into tblCities values ('Bello', 9)

Create procedure spGetContinents
as
Begin
Select ContinentId, ContinentName from tblContinents
End

Create procedure spGetCountriesByContinentId
@ContinentId int
as
Begin
Select CountryId, CountryName from tblCountries 
where ContinentId = @ContinentId
End

Create procedure spGetCitiesByCountryId
@CountryId int
as
Begin
Select CityId, CityName from tblCities
where CountryId = @CountryId
End

Let's understand cascading dropdownlists with an example. The following are the 3 dropsownlist controls, that we will have in our asp.net web application.
1. Continents DropDownList
2. Countries DropDownList
3. Cities DropDownList

When the webform first loads, only the continents dropdownlist should be populated. Countries and Cities dropdownlist should be disabled and should not allow the user to select anything from these 2 dropdownlists. Once, the user makes a selection in the continents dropdownlist, then Countries dropdownlist should be enabled and populated with the countries that belong to the selected continent. The same logic applies for the cities dropdownlist.

To achieve this drag and drop 3 dropdownlist controls onto the webform. The HTML of the Webform should be as shown below.
<asp:DropDownList ID="ddlContinents" Width="200px" DataTextField="ContinentName" 
    DataValueField="ContinentId" runat="server" AutoPostBack="True" 
    onselectedindexchanged="ddlContinents_SelectedIndexChanged">
</asp:DropDownList>
<br /><br />
<asp:DropDownList ID="ddlCountries" DataValueField="CountryId" 
    DataTextField="CountryName" Width="200px" runat="server" AutoPostBack="True" 
    onselectedindexchanged="ddlCountries_SelectedIndexChanged">
</asp:DropDownList>
<br /><br />
<asp:DropDownList ID="ddlCities" Width="200px" DataTextField="CityName" 
    DataValueField="CityId" runat="server">
</asp:DropDownList> 

Copy and paste the following code in the code behind page
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        PopulateContinentsDropDownList();
    }
}

private void PopulateContinentsDropDownList()
{
    ddlContinents.DataSource = GetData("spGetContinents", null);
    ddlContinents.DataBind();

    ListItem liContinent = new ListItem("Select Continent", "-1");
    ddlContinents.Items.Insert(0, liContinent);

    ListItem liCountry = new ListItem("Select Country", "-1");
    ddlCountries.Items.Insert(0, liCountry);

    ListItem liCity = new ListItem("Select City", "-1");
    ddlCities.Items.Insert(0, liCity);

    ddlCountries.Enabled = false;
    ddlCities.Enabled = false;
}

private DataSet GetData(string SPName, SqlParameter SPParameter)
{
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    SqlConnection con = new SqlConnection(CS);
    SqlDataAdapter da = new SqlDataAdapter(SPName, con);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;
    if (SPParameter != null)
    {
        da.SelectCommand.Parameters.Add(SPParameter);
    }
    DataSet DS = new DataSet();
    da.Fill(DS);
    return DS;
}

protected void ddlContinents_SelectedIndexChanged(object sender, EventArgs e)
{
    if (ddlContinents.SelectedValue == "-1")
    {
        ddlCities.SelectedIndex = 0;
        ddlCountries.SelectedIndex = 0;
        ddlCities.Enabled = false;
        ddlCountries.Enabled = false;
    }
    else
    {
        ddlCountries.Enabled = true;

        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@ContinentId";
        parameter.Value = ddlContinents.SelectedValue;

        ddlCountries.DataSource = GetData("spGetCountriesByContinentId", parameter);
        ddlCountries.DataBind();

        ListItem liCountry = new ListItem("Select Country", "-1");
        ddlCountries.Items.Insert(0, liCountry);

        ddlCities.SelectedIndex = 0;
        ddlCities.Enabled = false;
    }
}

protected void ddlCountries_SelectedIndexChanged(object sender, EventArgs e)
{
    if (ddlCountries.SelectedValue == "-1")
    {
        ddlCities.SelectedIndex = 0;
        ddlCities.Enabled = false;
    }
    else
    {
        ddlCities.Enabled = true;

        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CountryId";
        parameter.Value = ddlCountries.SelectedValue;

        ddlCities.DataSource = GetData("spGetCitiesByCountryId", parameter);
        ddlCities.DataBind();

        ListItem liCity = new ListItem("Select City", "-1");
        ddlCities.Items.Insert(0, liCity);
    }
}