Pages

Friday 14 February 2014

How To Create Cascading Dropdown List in Asp.net with Example Using Jquery

How To Create Cascading Dropdown List in Asp.net with Example Using Jquery

Country Table



State Table





Region Table





.Aspx Page
 

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>jQuery Cascading Dropdown Example</title>
    <script type="text/javascript" src="http://code.jquery.com/jquery-1.8.2.js"></script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table>
                <tr>
                    <td>Country</td>
                    <td>
                        <asp:dropdownlist id="ddlcountries" runat="server"></asp:dropdownlist>
                    </td>
                </tr>
                <tr>
                    <td>State</td>
                    <td>
                        <asp:dropdownlist id="ddlstate" runat="server"></asp:dropdownlist>
                    </td>
                </tr>
                <tr>
                    <td>Region</td>
                    <td>
                        <asp:dropdownlist id="ddlcity" runat="server"></asp:dropdownlist>
                    </td>
                </tr>
            </table>
        </div>
    </form>
    <script type="text/javascript">
        $(function () {
            $('#<%=ddlstate.ClientID %>').attr('disabled', 'disabled');
            $('#<%=ddlcity.ClientID %>').attr('disabled', 'disabled');
            $('#<%=ddlstate.ClientID %>').append('<option selected="selected" value="0">Select State</option>');
            $('#<%=ddlcity.ClientID %>').empty().append('<option selected="selected" value="0">Select Region</option>');
            $('#<%=ddlcountries.ClientID %>').change(function () {
                var country = $('#<%=ddlcountries.ClientID%>').val()
                $('#<%=ddlstate.ClientID %>').removeAttr("disabled");
                $('#<%=ddlcity.ClientID %>').empty().append('<option selected="selected" value="0">Select Region</option>');
                $('#<%=ddlcity.ClientID %>').attr('disabled', 'disabled');
                $.ajax({
                    type: "POST",
                    url: "jQueryCascadingDropdownExample.aspx/BindStates",
                    data: "{'country':'" + country + "'}",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (msg) {
                        var j = jQuery.parseJSON(msg.d);
                        var options;
                        for (var i = 0; i < j.length; i++) {
                            options += '<option value="' + j[i].optionValue + '">' + j[i].optionDisplay + '</option>'
                        }
                        $('#<%=ddlstate.ClientID %>').html(options)
                    },
                    error: function (data) {
                        alert('Something Went Wrong')
                    }
                });
            });
            $('#<%=ddlstate.ClientID %>').change(function () {
                var stateid = $('#<%=ddlstate.ClientID%>').val()
                $('#<%=ddlcity.ClientID %>').removeAttr("disabled");
                $.ajax({
                    type: "POST",
                    url: "jQueryCascadingDropdownExample.aspx/BindRegion",
                    data: "{'state':'" + stateid + "'}",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (msg) {
                        var j = jQuery.parseJSON(msg.d);
                        var options;
                        for (var i = 0; i < j.length; i++) {
                            options += '<option value="' + j[i].optionValue + '">' + j[i].optionDisplay + '</option>'
                        }
                        $('#<%=ddlcity.ClientID %>').html(options)
                    },
                    error: function (data) {
                        alert('Something Went Wrong')
                    }
                });
            })
        })
    </script>
</body>
</html>



C# Code



 using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web.Services;
using System.Web.UI.WebControls;
 public static string strcon = "Data Source=SureshDasari;Initial Catalog=MySampleDB;Integrated Security=true";
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
BindCountries();
}
}
public void BindCountries()
{
String strQuery = "select CountryID,CountryName from Country";
using (SqlConnection con = new SqlConnection(strcon))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Connection = con;
con.Open();
ddlcountries.DataSource = cmd.ExecuteReader();
ddlcountries.DataTextField = "CountryName";
ddlcountries.DataValueField = "CountryID";
ddlcountries.DataBind();
ddlcountries.Items.Insert(0, new ListItem("Select Country", "0"));
con.Close();
}
}
}
[WebMethod]
public static string BindStates(string country)
{
StringWriter builder = new StringWriter();
String strQuery = "select StateID,StateName from State where CountryID=@CountryID";
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(strcon))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Parameters.AddWithValue("@countryid", country);
cmd.Connection = con;
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
}
}
DataTable dt = ds.Tables[0];
builder.WriteLine("[");
if (dt.Rows.Count > 0)
{
builder.WriteLine("{\"optionDisplay\":\"Select State\",");
builder.WriteLine("\"optionValue\":\"0\"},");
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
builder.WriteLine("{\"optionDisplay\":\"" + dt.Rows[i]["StateName"] + "\",");
builder.WriteLine("\"optionValue\":\"" + dt.Rows[i]["StateID"]+ "\"},");
}
}
else
{
builder.WriteLine("{\"optionDisplay\":\"Select State\",");
builder.WriteLine("\"optionValue\":\"0\"},");
}
string returnjson = builder.ToString().Substring(0, builder.ToString().Length - 3);
returnjson = returnjson + "]";
return returnjson.Replace("\r", "").Replace("\n", "");
}

[WebMethod]
public static string BindRegion(string state)
{
StringWriter builder = new StringWriter();
String strQuery = "select RegionID, RegionName from Region where StateID=@StateID";
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(strcon))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Parameters.AddWithValue("@StateID", state);
cmd.Connection = con;
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
}
}
DataTable dt = ds.Tables[0];
builder.WriteLine("[");
if (dt.Rows.Count > 0)
{
builder.WriteLine("{\"optionDisplay\":\"Select Region\",");
builder.WriteLine("\"optionValue\":\"0\"},");
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
builder.WriteLine("{\"optionDisplay\":\"" + dt.Rows[i]["RegionName"] + "\",");
builder.WriteLine("\"optionValue\":\"" + dt.Rows[i]["RegionID"] + "\"},");
}
}
else
{
builder.WriteLine("{\"optionDisplay\":\"Select Region\",");
builder.WriteLine("\"optionValue\":\"0\"},");
}
string returnjson = builder.ToString().Substring(0, builder.ToString().Length - 3);
returnjson = returnjson + "]";
return returnjson.Replace("\r", "").Replace("\n", "");
}


VB Code


Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Web.Services
Imports System.Web.UI.WebControls

Partial Class VBCode
Inherits System.Web.UI.Page
Public Shared strcon As String = "Data Source=SureshDasari;Initial Catalog=MySampleDB;Integrated Security=true"
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

If Not IsPostBack Then
BindCountries()
End If
End Sub
Public Sub BindCountries()
Dim strQuery As [String] = "select CountryID,CountryName from Country"
Using con As New SqlConnection(strcon)
Using cmd As New SqlCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = strQuery
cmd.Connection = con
con.Open()
ddlcountries.DataSource = cmd.ExecuteReader()
ddlcountries.DataTextField = "CountryName"
ddlcountries.DataValueField = "CountryID"
ddlcountries.DataBind()
ddlcountries.Items.Insert(0, New ListItem("Select Country", "0"))
con.Close()
End Using
End Using
End Sub
<WebMethod()> _
Public Shared Function BindStates(ByVal country As String) As String
Dim builder As New StringWriter()
Dim strQuery As [String] = "select StateID,StateName from State where CountryID=@CountryID"
Dim ds As New DataSet()
Using con As New SqlConnection(strcon)
Using cmd As New SqlCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = strQuery
cmd.Parameters.AddWithValue("@countryid", country)
cmd.Connection = con
con.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
con.Close()
End Using
End Using
Dim dt As DataTable = ds.Tables(0)
builder.WriteLine("[")
If dt.Rows.Count > 0 Then
builder.WriteLine("{""optionDisplay"":""Select State"",")
builder.WriteLine("""optionValue"":""0""},")
For i As Integer = 0 To dt.Rows.Count - 1
builder.WriteLine("{""optionDisplay"":""" & Convert.ToString(dt.Rows(i)("StateName")) & """,")
builder.WriteLine("""optionValue"":""" & Convert.ToString(dt.Rows(i)("StateID")) & """},")
Next
Else
builder.WriteLine("{""optionDisplay"":""Select State"",")
builder.WriteLine("""optionValue"":""0""},")
End If
Dim returnjson As String = builder.ToString().Substring(0, builder.ToString().Length - 3)
returnjson = returnjson & "]"
Return returnjson.Replace(vbCr, "").Replace(vbLf, "")
End Function

<WebMethod()> _
Public Shared Function BindRegion(ByVal state As String) As String
Dim builder As New StringWriter()
Dim strQuery As [String] = "select RegionID, RegionName from Region where StateID=@StateID"
Dim ds As New DataSet()
Using con As New SqlConnection(strcon)
Using cmd As New SqlCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = strQuery
cmd.Parameters.AddWithValue("@StateID", state)
cmd.Connection = con
con.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
con.Close()
End Using
End Using
Dim dt As DataTable = ds.Tables(0)
builder.WriteLine("[")
If dt.Rows.Count > 0 Then
builder.WriteLine("{""optionDisplay"":""Select Region"",")
builder.WriteLine("""optionValue"":""0""},")
For i As Integer = 0 To dt.Rows.Count - 1
builder.WriteLine("{""optionDisplay"":""" & Convert.ToString(dt.Rows(i)("RegionName")) & """,")
builder.WriteLine("""optionValue"":""" & Convert.ToString(dt.Rows(i)("RegionID")) & """},")
Next
Else
builder.WriteLine("{""optionDisplay"":""Select Region"",")
builder.WriteLine("""optionValue"":""0""},")
End If
Dim returnjson As String = builder.ToString().Substring(0, builder.ToString().Length - 3)
returnjson = returnjson & "]"
Return returnjson.Replace(vbCr, "").Replace(vbLf, "")
End Function
End Class


Demo:







No comments:

Post a Comment