Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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);
    }
}

5 comments:

  1. spGetContinents stored procedure works seperately but in continent dropdown its showing System.Data.DataRowView

    ReplyDelete
  2. Hiii, I have problem with the Cascading drop down.
    Cascading drop down is getting refreshed infinetly. in Chrome and safari browser.
    This issue is not appearing in IE and firefox. I have tried adding Microsoft ajax scripts in the script manager and added the browser compatability code but nothing worked for me. Please let me know if there is any solution regarding this issue.
    My email id is phaniraj1987@gmail.com

    ReplyDelete
  3. Respected Venkat,
    This method of cascading dropdownlists I tried works perfectly, but the problem is using cascading dropdown lists inside GridView which does not work in that case using editing, updating and updating data inside sql database.
    please help me and email me at khalidkhans55@gmail.com

    Many Thanks and respect for you
    Khalid Khan
    Islamabad - Pakistan

    ReplyDelete
  4. Hai sir the procedure for getting the names of countries showing the error like must declare the scalar variable

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.