Suggested Videos
Part 101 - jquery background color animate
Part 102 - jQuery class transition animation
Part 103 - jQuery autocomplete with images and text
In this video we will discuss how to create a cascading dropdownlist using jQuery and ASP.NET.
We want all the data for the cascading dropdownlists to be coming from the database.
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.
Step 1 : Create the required tables and stored procedures
Step 2 : Create new asp.net web application project. Name it Demo.
Step 3 : Include a connection string in the web.config file to your database.
Step 4 : Add a class file to the project. Name it Continent.cs. Copy and paste the following code.
Step 5 : Add a class file to the project. Name it Country.cs. Copy and paste the following code.
Step 6 : Add a class file to the project. Name it City.cs. Copy and paste the following code.
Step 7 : Add a WebService (ASMX) to the project. Name it DataService.asmx. Copy and paste the following code.
Step 8 : Add a WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code.
Part 101 - jquery background color animate
Part 102 - jQuery class transition animation
Part 103 - jQuery autocomplete with images and text
In this video we will discuss how to create a cascading dropdownlist using jQuery and ASP.NET.
We want all the data for the cascading dropdownlists to be coming from the database.
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.
Step 1 : Create the required tables and stored procedures
Create Table tblContinents
(
Id int identity primary key,
Name nvarchar(50)
)
Go
Insert into tblContinents values ('Asia')
Insert into tblContinents values ('Europe')
Insert into tblContinents values ('South America')
Go
Create Table tblCountries
(
Id int identity primary key,
Name nvarchar(50),
ContinentId int
foreign key references dbo.tblContinents(Id)
)
Go
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)
Go
Create Table tblCities
(
Id int identity primary key,
Name nvarchar(50),
CountryId int foreign key references dbo.tblCountries(Id)
)
Go
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)
Go
Create procedure spGetContinents
as
Begin
Select Id, Name from
tblContinents
End
Go
Create procedure spGetCountriesByContinentId
@ContinentId int
as
Begin
Select Id, Name, ContinentId from tblCountries
where
ContinentId = @ContinentId
End
Go
Create procedure spGetCitiesByCountryId
@CountryId int
as
Begin
Select Id, Name, CountryId from tblCities
where
CountryId = @CountryId
End
Go
Step 2 : Create new asp.net web application project. Name it Demo.
Step 3 : Include a connection string in the web.config file to your database.
<add name="DBCS"
connectionString="server=.;database=SampleDB;integrated security=SSPI"/>
Step 4 : Add a class file to the project. Name it Continent.cs. Copy and paste the following code.
namespace Demo
{
public class Continent
{
public int Id { get; set; }
public string Name { get; set; }
}
}
Step 5 : Add a class file to the project. Name it Country.cs. Copy and paste the following code.
namespace Demo
{
public class Country
{
public int Id { get; set; }
public string Name { get; set; }
public int ContinentId { get; set; }
}
}
Step 6 : Add a class file to the project. Name it City.cs. Copy and paste the following code.
namespace Demo
{
public class City
{
public int Id { get; set; }
public string Name { get; set; }
public int CountryId { get; set; }
}
}
Step 7 : Add a WebService (ASMX) to the project. Name it DataService.asmx. Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
namespace Demo
{
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class DataService : System.Web.Services.WebService
{
[WebMethod]
public void GetContinents()
{
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
List<Continent> continents = new List<Continent>();
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetContinents", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Continent continent = new Continent();
continent.Id = Convert.ToInt32(rdr["Id"]);
continent.Name = rdr["Name"].ToString();
continents.Add(continent);
}
}
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(continents));
}
[WebMethod]
public void GetCountriesByContinentId(int ContinentId)
{
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
List<Country> countries = new List<Country>();
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetCountriesByContinentId", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter()
{
ParameterName = "@ContinentId",
Value = ContinentId
};
cmd.Parameters.Add(param);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Country country = new Country();
country.Id = Convert.ToInt32(rdr["Id"]);
country.Name = rdr["Name"].ToString();
country.ContinentId = Convert.ToInt32(rdr["ContinentId"]);
countries.Add(country);
}
}
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(countries));
}
[WebMethod]
public void GetCitiesByCountryId(int CountryId)
{
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
List<City> cities = new List<City>();
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetCitiesByCountryId", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter()
{
ParameterName = "@CountryId",
Value = CountryId
};
cmd.Parameters.Add(param);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
City city = new City();
city.Id = Convert.ToInt32(rdr["Id"]);
city.Name = rdr["Name"].ToString();
city.CountryId = Convert.ToInt32(rdr["CountryId"]);
cities.Add(city);
}
}
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(cities));
}
}
}
Step 8 : Add a WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code.
<%@ Page Language="C#" AutoEventWireup="true"
CodeBehind="WebForm1.aspx.cs" Inherits="Demo.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="jquery-1.11.2.js"></script>
<script type="text/javascript">
$(document).ready(function () {
var continentsDDL = $('#continents');
var countriesDDL = $('#countries');
var citiesDDL = $('#cities');
$.ajax({
url: 'DataService.asmx/GetContinents',
method: 'post',
dataType: 'json',
success: function (data) {
continentsDDL.append($('<option/>', {
value: -1, text: 'Select Continent' }));
countriesDDL.append($('<option/>', {
value: -1, text: 'Select Country' }));
citiesDDL.append($('<option/>', {
value: -1, text: 'Select City' }));
countriesDDL.prop('disabled', true);
citiesDDL.prop('disabled', true);
$(data).each(function (index, item) {
continentsDDL.append($('<option/>', {
value: item.Id, text: item.Name }));
});
},
error: function (err) {
alert(err);
}
});
continentsDDL.change(function () {
if ($(this).val() == "-1") {
countriesDDL.empty();
citiesDDL.empty();
countriesDDL.append($('<option/>', {
value: -1, text: 'Select Country' }));
citiesDDL.append($('<option/>', {
value: -1, text: 'Select City' }));
countriesDDL.val('-1');
citiesDDL.val('-1');
countriesDDL.prop('disabled', true);
citiesDDL.prop('disabled', true);
}
else {
citiesDDL.val('-1');
citiesDDL.prop('disabled', true);
$.ajax({
url: 'DataService.asmx/GetCountriesByContinentId',
method: 'post',
dataType: 'json',
data: { ContinentId: $(this).val() },
success: function (data) {
countriesDDL.empty();
countriesDDL.append($('<option/>', { value: -1, text: 'Select
Country' }));
$(data).each(function (index, item) {
countriesDDL.append($('<option/>', { value: item.Id, text: item.Name }));
});
countriesDDL.val('-1');
countriesDDL.prop('disabled', false);
},
error: function (err) {
alert(err);
}
});
}
});
countriesDDL.change(function () {
if ($(this).val() == "-1") {
citiesDDL.empty();
citiesDDL.append($('<option/>', {
value: -1, text: 'Select City' }));
citiesDDL.val('-1');
citiesDDL.prop('disabled', true);
}
else {
$.ajax({
url: 'DataService.asmx/GetCitiesByCountryId',
method: 'post',
dataType: 'json',
data: { CountryId: $(this).val() },
success: function (data) {
citiesDDL.empty();
citiesDDL.append($('<option/>', {
value: -1, text: 'Select City' }));
$(data).each(function (index, item) {
citiesDDL.append($('<option/>', { value: item.Id, text: item.Name }));
});
citiesDDL.val('-1');
citiesDDL.prop('disabled', false);
},
error: function (err) {
alert(err);
}
});
}
});
});
</script>
<style>
select {
width: 150px;
}
</style>
</head>
<body style="font-family:
Arial">
<form id="form1" runat="server">
<table>
<tr>
<td>Continent
</td>
<td>
<select id="continents">
</select>
</td>
</tr>
<tr>
<td>Country</td>
<td>
<select id="countries">
</select>
</td>
</tr>
<tr>
<td>City</td>
<td>
<select id="cities">
</select>
</td>
</tr>
</table>
</form>
</body>
</html>
What if it doesnt show the data values?
ReplyDeleteI get an error in SSMS:
Msg 2714, Level 16, State 3, Procedure spGetAfdelingen, Line 1 [Batch Start Line 0]
There is already an object named 'spGetAfdelingen' in the database.
Msg 2714, Level 16, State 3, Procedure spGetOpleidingenByAfdelingId, Line 2 [Batch Start Line 6]
There is already an object named 'spGetOpleidingenByAfdelingId' in the database.
Is it because of that or because I am missing certain files or codes?
please can you explain how to cascade dropdownlist using
ReplyDeletejson in asp.net mvc..
first of all thank you venkat, your all videos helpful for me,
ReplyDeleteI implement cascading drop down list in asp.net c#, I add to all DropDownList AutoPostBack, if i select a option of dropdownlist
my page is reloaded help me venkat