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

Cascading dropdownlist using jquery and asp.net

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.

Cascading dropdownlist using jquery and asp.net

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>

jQuery tutorial for beginners

No comments:

Post a Comment

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.