Saturday, 18 January 2014

jQuery Cascading Dropdown List in Asp.net with Example

Here I will explain cascading dropdown list using jQuery in asp.net example or jQuery cascading dropdown example in asp.net using c#, vb.net.

Description:

In my previous article I explained how to populate dropdown based on other dropdown using asp.net, Ajax cascading dropdown with database in asp.net, Bind asp.net dropdown in jQuery. Now I will explain how to implement cascading dropdown list using jQuery in asp.net.

Here I will explain jQuery cascading dropdown example with three dropdowns Country dropdown, State dropdown, Region dropdown. Here we need to populate states dropdown based on country dropdown and region dropdown based on states dropdown selection for that we need to design three tables in SQL Server like as shown below

Country Table

Column Name
Data Type
Allow Nulls
CountryId
int(set identity property=true)
No
CountryName
varchar(50)
Yes
State Table

Column Name
Data Type
Allow Nulls
StateId
int(set identity property=true)
No
StateName
varchar(50)
Yes
CountryId
int
Yes
Region Table

Column Name
Data Type
Allow Nulls
RegionId
int(set identity property=true)
No
RegionName
varchar(50)
Yes
StateId
int
Yes
Once we create above tables we need to insert some dummy data like as shown below 

Country Table
State Table
 Region Table 
Now we need to write the code like as shown below in your 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('');
$('#<%=ddlcity.ClientID %>').empty().append('');
$('#<%=ddlcountries.ClientID %>').change(function() {
var country = $('#<%=ddlcountries.ClientID%>').val()
$('#<%=ddlstate.ClientID %>').removeAttr("disabled");
$('#<%=ddlcity.ClientID %>').empty().append('');
$('#<%=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 += ' + j[i].optionDisplay + '
'
}
$('#<%=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 += ' + j[i].optionDisplay + '
'
}
$('#<%=ddlcity.ClientID %>').html(options)
},
error: function(data) {
alert('Something Went Wrong')
}
});
})
})
</script>
</body>
</html>
Now add following namespaces in code behind

C# Code


using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web.Services;
using System.Web.UI.WebControls;
Once we add namespaces need write the code like as shown below


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.NET 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
_
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

_
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

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)