Introduction
This is a very easy and user friendly technique for web applications to implement cascading menus without page refresh.
You do not have to know about AJAX functions, just download the AJAX Control Toolkit on CodePlex and follow the steps described in this article. When you have two dropdownlists and both of them are related to each other, such as Country and City in a registration page where you want to save information about the user's hometown. When you choose a certain row from the first dropdownlist (Country), you will expect the second dropdownlist (City) to be filtered according to the chosen row without refreshing the page. This article shows a very easy and user friendly solution that can be used in web applications.
Background: What is the AJAX Control Toolkit?
The ASP.NET AJAX Control Toolkit is an Open-Source project built on top of the Microsoft ASP.NET AJAX framework, and contains more than 30 controls that enable you to easily create rich, interactive web pages. If you want to know more about it, visit here.
Using the code
The first step is to download the AJAX Control Toolkit from here for .NET 3.5 or here for .NET 4.0.
Copy the AJAX Control Toolkit to the Bin folder and right click on Solution, choose Add Reference, in the Browse tab, double click on the Bin folder, and double click on AJAX Control Toolkit, then on the Build menu, click Rebuild.
Database
Create a database and name it "Db". Here is the query to create the required tables:
CREATE TABLE [dbo].[tblCountry](
[Country] [nvarchar](50) NULL,
[IDC] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
insert into dbo.tblCountry(Country) values('United States')
insert into dbo.tblCountry(Country) values('United Kingdom')
insert into dbo.tblCountry(Country) values('Spain')
insert into dbo.tblCountry(Country) values('France')
insert into dbo.tblCountry(Country) values('Norway')
CREATE TABLE [dbo].[tblCity](
[City] [nvarchar](50) NULL,
[CountryID] [int] Not NULL,
[ID] [int] IDENTITY(1,1) NOT NULL
)
ON [PRIMARY]
insert into dbo.tblCity(City,CountryID) values('Michigan',1)
insert into dbo.tblCity(City,CountryID) values('New York',1)
insert into dbo.tblCity(City,CountryID) values('London',2)
insert into dbo.tblCity(City,CountryID) values('Barcelona',3)
insert into dbo.tblCity(City,CountryID) values('Madrid',3)
insert into dbo.tblCity(City,CountryID) values('Paris',4)
insert into dbo.tblCity(City,CountryID) values('Kristiansand',5)
insert into dbo.tblCity(City,CountryID) values('Oslo',5)
Visual Studio 2008 - .NET 3.5
Create a website and name it Cascading menus. Create a Web Form and name it CascadingDropDown.aspx. In the HTML view, write the code below. The code is a little different between C# and VB in this section. If you are a VB coder, modify two sections in the page tag in the first line:
- language="VB"
- CodeFile="CascadingDropDown.aspx.vb"
<%@ Page Language="C#" AutoEventWireup="false"
CodeFile="CascadingDropDown.aspx.cs" Inherits="CascadingDropDown" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit"
TagPrefix="ajaxToolkit" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>CascadingDropDown</title>
</head>
<body>
<form id="form1" runat="server">
<ajaxToolkit:ToolkitScriptManager ID="ScriptManager1" runat="server" >
</ajaxToolkit:ToolkitScriptManager>
<div>
<asp:DropDownList ID="ddListCountry" runat="server" Width="170" />
<asp:DropDownList ID="ddListCity" runat="server" Width="170" />
<ajaxToolkit:CascadingDropDown
ID="CascadingCountry"
runat="server"
TargetControlID="ddListCountry"
Category="Country"
PromptText="Please select a Country"
LoadingText="[Loading Country...]"
ServicePath="Cascading.asmx"
ServiceMethod="GetCountries" >
</ajaxToolkit:CascadingDropDown>
<ajaxToolkit:CascadingDropDown
ID="CascadingCity"
runat="server"
TargetControlID="ddListCity"
Category="City"
PromptText="Please select a City"
LoadingText="[Loading City...]"
ServicePath="Cascading.asmx"
ServiceMethod="GetCities"
ParentControlID="ddListCountry" >
</ajaxToolkit:CascadingDropDown>
</div>
</form>
</body>
</html>
For VB: Create Web Service: Solution > right click > Add New Item > Web Service >. Name: Cascading.asmx. Language: Visual Basic. Go To > App_Code > Cascading.vb.
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Collections.Generic
Imports AjaxControlToolkit
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.Collections.Specialized
Imports System.Xml
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class Cascading
Inherits System.Web.Services.WebService
Dim cn As New SqlClient.SqlConnection()
Dim ds As New DataSet
Dim dt As New DataTable
<WebMethod()> _
Public Function GetCountries(ByVal knownCategoryValues As String, _
ByVal category As String) As CascadingDropDownNameValue()
Dim strCn As String = "data source=.;Initial Catalog=Db;Integrated Security=True"
cn.ConnectionString = strCn
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = "select * from tblCountry"
Try
cn.Open()
cmd.ExecuteNonQuery()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
Catch ex As Exception
Finally
cn.Close()
End Try
dt = ds.Tables(0)
Dim CountryValues As New List(Of CascadingDropDownNameValue)()
For Each row As DataRow In dt.Rows
CountryValues.Add(New CascadingDropDownNameValue(row("Country").ToString(), _
row("IDC").ToString()))
Next
Return CountryValues.ToArray()
End Function
<WebMethod()> _
Public Function GetCities(ByVal knownCategoryValues As String, _
ByVal category As String) As CascadingDropDownNameValue()
Dim kv As StringDictionary = _
CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
Dim countryId As Integer
If ((Not kv.ContainsKey("Country")) Or _
(Not Int32.TryParse(kv("Country"), countryId))) Then
Return Nothing
End If
Dim strCn As String = "data source=.;Initial Catalog=Db;Integrated Security=True"
cn.ConnectionString = strCn
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandText = "select * from tblCity where CountryID=@myParameter Order by City"
cmd.Parameters.AddWithValue("@myParameter", countryId.ToString())
Try
cn.Open()
cmd.ExecuteNonQuery()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
Catch ex As Exception
Finally
cn.Close()
End Try
dt = ds.Tables(0)
Dim CityValues As New List(Of CascadingDropDownNameValue)()
For Each row As DataRow In dt.Rows
CityValues.Add(New CascadingDropDownNameValue(row("City").ToString(), _
row("ID").ToString()))
Next
Return CityValues.ToArray()
End Function
End Class
For C#: Web Service: Solution > right click > Add New Item > Web Service >. Name: Cascading.asmx. Language: C#. Go To > App_Code > Cascading.cs.
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Collections.Generic;
using AjaxControlToolkit;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
using System.Xml;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class Cascading : System.Web.Services.WebService {
public Cascading () {
}
[WebMethod]
public CascadingDropDownNameValue[] GetCountries(string knownCategoryValues,
string category)
{
SqlConnection cn =new SqlConnection();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string strCn = "data source=.;Initial Catalog=Db;Integrated Security=True";
cn.ConnectionString = strCn;
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from tblCountry";
try
{
cn.Open();
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch
{
}
finally
{
cn.Close();
}
dt = ds.Tables[0];
List<CascadingDropDownNameValue> CountryValues =
new List<CascadingDropDownNameValue>();
foreach (DataRow row in dt.Rows)
{
CountryValues.Add(new CascadingDropDownNameValue(
row["Country"].ToString(), row["IDC"].ToString()));
}
return CountryValues.ToArray();
}
[WebMethod]
public CascadingDropDownNameValue[] GetCities(string knownCategoryValues,
string category)
{
StringDictionary kv =
CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
int countryId;
countryId = System.Convert.ToInt32(kv["Country"]);
SqlConnection cn = new SqlConnection();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string strCn =
"data source=.;Initial Catalog=Db;Integrated Security=True";
cn.ConnectionString = strCn;
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from tblCity where CountryID=@myParameter Order by City";
cmd.Parameters.AddWithValue("@myParameter", countryId.ToString());
try
{
cn.Open();
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch
{
}
finally
{
cn.Close();
}
dt = ds.Tables[0];
List<CascadingDropDownNameValue> CityValues =
new List<CascadingDropDownNameValue>();
foreach (DataRow row in dt.Rows)
{
CityValues.Add(new CascadingDropDownNameValue(
row["City"].ToString(), row["ID"].ToString()));
}
return CityValues.ToArray();
}
}
GetCountries
and GetCities
are two functions that get two string arguments: knownCategoryValues
and category
. They have an output which is an array of strings: CascadingDropDownNameValue
. In the GetCountries
function, the Country dropdownlist is filled: we connect to the database and execute a query using ADO.NET, then in the foreach
loop, fill the dropdownlist. When you select a certain row (in the above example, a country such as United States), knownCategoryValues
will be equal to "country:1", which means category
is country and knownCategoryValues
is "1". 1 is the ID for United States in the database. In the GetCities
function, the City dropdownlist is filled and filtered by knownCategoryValues
, which is "1" in this case. In the Web Form, I have specified ParentControlID="ddListCountry"
in CascadingCity
, so the parent is country and the city will be filtered by the country ID. Additionally, I defined a parameter instead of passing value directly, to prevent SQL Injection.
Trying the demo step by step
- Go here for .NET 3.5 or here for .NET 4.0 and download the AJAX Control Toolkit file.
- Copy the folder "AjaxControlToolkit.Dll" and all its dependencies, they are 18 objects, to your web site in the Bin folder (C:\Cascading\Bin).
- Right click on Solution, choose Refresh, then right click again and click Add reference. Then in the Browse tab, double click on the Bin folder and double click on ajaxcontroltoolkit. On the Build menu > click Rebuild.
- Create the database and tables like above, and add some rows which have common words.
- Create a Web Form and name it CascadingDropDown.aspx. In the HTML view, write some code like above. (This should be exactly like my code because this section is case sensitive.)
- Create a Web Service: Solution > right click > Add New Item > Web Service > Name: Cascading.asmx. Language: C# or VB. Go to > App_Code > Cascading.cs or Cascading.vb.
- If you are a VB coder, use the VB sample, otherwise use the C# sample.
- Run the program, select a country such as United States, and you will see a list of cities in that country such as Detroit or New York City.
Feedback
Feel free to leave any feedback on this article; it is a pleasure to see your opinions and vote about this code. If you have any questions, please do not hesitate to ask me here.
I have been working with different technologies and data more than 10 years.
I`d like to challenge with complex problem, then make it easy for using everyone. This is the best joy.
ICT Master in Norway 2013
Doctorandin at Technische Universität Berlin in Data Scientist ( currently )
-------------------------------------------------------------
Diamond is nothing except the pieces of the coal which have continued their activities finally they have become Diamond.
http://www.repocomp.com/