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

12 comments:

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

    ReplyDelete
    Replies
    1. you have to set DataTextField and DataValueField property at design time by right clicking on control >properties>DataTextField is set to ContinentName and DataValueField is set to ContinentId

      Delete
  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
  5. Hi sir, I am getting one problem here in inserting that "An explicit value for the identity column in table 'Continent' can only be specified when a column list is used and IDENTITY_INSERT is ON." that is the solution of this.

    ReplyDelete
    Replies
    1. Don't Insert the 'ContinentId' Explicitly (by yourself). As you have enabled the "identity specification / is identity" property to Yes, it will automatically add and increment the 'ContinentId' every time you insert a 'ContinentName'.
      e.g; Insert into tblContinents values ('Asia').

      Delete
  6. SIR I HAVE A PROBLEM USING VENKAT SIR METHOD IT IS FINE FOR WORK
    AND I SAVED A student DATA like this
    SID NAME Continentsid Countriesid Citiesid
    1 ajay 1 1 2
    2 vikash 2 1 3
    now i have records like this in tblstudentrecord
    and also i have table
    1. tblContinents
    2. tblCountries
    3. tblCities

    now i am select SID FROM tblstudentrecord and on the basis of sid i want to update records when i retrieve data from database tables, name and ContinentsName is cumming perfectly but CountriesName and
    CitiesName not come?
    why ->i know region ContinentsName is come from database. and countryname is come in selected index change of ddlContinents
    but when data comes from database selected index is not changed


    how solve this problem????

    ReplyDelete
  7. is it possible to bind multiple dropdown list in asp.net without using foreign key .....and use iner join to bind the value of countries and cities

    ReplyDelete
  8. Gracias. Excelente aportación. Clara y funcional!!

    ReplyDelete
  9. C# cascading drop down list store only ids in the database

    ReplyDelete

It would be great if you can help share these free resources