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.
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<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>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class DropDownlist_Default : System.Web.UI.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["masterConnectionString"].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);
}
}
}
0 comments:
Post a Comment