In this article I will show how to implement jQuery cascading dropdownlist using jQuery and ASP.NET. Cascading drodpdownlist means when one dropdownlist is dependent on other dropdownlist. Let's take a common example of Country, State and Metro. Each country has its own states and each state has its own metros. So in many occasions we need three dropdownlists for this where on selecting a particular country list of states is populated in the state dropdown and on selecting a particular state list of metros are populated. The next target is to save all those selections in the database. Below is the screenshot of how the cascading dropdownlist will look:
Let's start with database side by creating three tables (for country, state and metro) and then poplulating them with some sample data.
CREATE TABLE [Countries] ( [CountryID] INT IDENTITY , [CountryName] NVARCHAR(100) ) GO INSERT INTO [Countries] SELECT 'Country1' UNION ALL SELECT 'Country2' UNION ALL SELECT 'Country3' GO CREATE TABLE [States] ( [StateID] INT IDENTITY , [CountryID] INT , [StateName] NVARCHAR(100) ) GO INSERT INTO [States] SELECT 1 , 'Country1-State1' UNION ALL SELECT 1 , 'Country1-State2' UNION ALL SELECT 1 , 'Country1-State3' UNION ALL SELECT 2 , 'Country2-State1' UNION ALL SELECT 2 , 'Country2-State2' UNION ALL SELECT 3 , 'Country3-State1' UNION ALL SELECT 3 , 'Country3-State2' UNION ALL SELECT 3 , 'Country3-State3' GO CREATE TABLE [Metros] ( [MetroID] INT IDENTITY , [StateID] INT , [MetroName] NVARCHAR(100) ) GO INSERT INTO [Metros] SELECT 1 , 'Country1-State1-Metro1' UNION ALL SELECT 1 , 'Country1-State1-Metro2' UNION ALL SELECT 1 , 'Country1-State1-Metro3' UNION ALL SELECT 1 , 'Country1-State1-Metro4' UNION ALL SELECT 2 , 'Country1-State2-Metro1' UNION ALL SELECT 2 , 'Country1-State2-Metro2' UNION ALL SELECT 2 , 'Country1-State2-Metro3' UNION ALL SELECT 3 , 'Country1-State3-Metro1' UNION ALL SELECT 4 , 'Country2-State1-Metro1' UNION ALL SELECT 4 , 'Country2-State1-Metro2' UNION ALL SELECT 5 , 'Country2-State2-Metro1' UNION ALL SELECT 6 , 'Country3-State1-Metro1' UNION ALL SELECT 6 , 'Country3-State1-Metro2' UNION ALL SELECT 7 , 'Country3-State2-Metro1' UNION ALL SELECT 7 , 'Country3-State2-Metro2' UNION ALL SELECT 8 , 'Country3-State3-Metro1' GO
Let's create some stored procedures to work with these tables. We will create three sps for selecting the respective entity (Country, State or Metro).
GO CREATE PROC SelectAllCountries AS BEGIN SELECT [CountryID] , [CountryName] FROM [Countries] END GO CREATE PROC SelectStates @CountryID INT AS BEGIN SELECT [StateID] , [StateName] , [CountryID] FROM [States] WHERE [CountryID] = @CountryID END GO CREATE PROC SelectMetros @StateID INT AS BEGIN SELECT [MetroID] , [MetroName] , [StateID] FROM [Metros] WHERE [StateID] = @StateID END
That was of the database part. Now let's come to front end portion. Start with making a business class for location. This will have following properties: CountryID, CountryName, StateID, StateName, MetroID and MetroName. Mark this class as Serializable.
[Serializable]public class Location { public int CountryID { get; set; } public string CountryName { get; set; } public int StateID { get; set; } public string StateName { get; set; } public int MetroID { get; set; } public string MetroName { get; set; } }
Now we are going to make a webservice(.asmx) which will act as the middle man between the client and the database. Let's say the name of the service is Sample.asmx. Note the webservice should be marked with [System.Web.Script.Services.ScriptService] attribute. Below is the complete code of webservice.
using System; using System.Collections.Generic; using System.Collections.ObjectModel; using System.Data; using System.Data.SqlClient; using System.Web.Services; using TestProject; /// <summary> /// Summary description for Sample /// </summary> [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] [System.ComponentModel.ToolboxItem(false)] // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. [System.Web.Script.Services.ScriptService] public class Sample : WebService { [WebMethod] public IEnumerable<Location> FindAllCountries() { Collection<Location> locations = new Collection<Location>(); string connectionString = "Data Source=YourServerName; Initial Catalog=YourDatabase; User ID=YourUserName; Password=YourPassword"; using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandText = "SelectAllCountries"; command.CommandType = CommandType.StoredProcedure; connection.Open(); using (SqlDataReader dataReader = command.ExecuteReader()) { Location location; if (dataReader != null) while (dataReader.Read()) { location = new Location(); location.CountryID = (int)dataReader["CountryID"]; location.CountryName = Convert.ToString(dataReader["CountryName"]); locations.Add(location); } } } } return locations; } [WebMethod] public IEnumerable<Location> FindStates(int countryID) { Collection<Location> locations = new Collection<Location>(); string connectionString = "Data Source=YourServerName; Initial Catalog=YourDatabase; User ID=YourUserName; Password=YourPassword"; using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandText = "SelectStates"; command.CommandType = CommandType.StoredProcedure; SqlParameter prmCountryID = new SqlParameter("@CountryID", SqlDbType.Int); prmCountryID.Value = countryID; command.Parameters.Add(prmCountryID); connection.Open(); using (SqlDataReader dataReader = command.ExecuteReader()) { Location location; if (dataReader != null) while (dataReader.Read()) { location = new Location(); location.CountryID = (int)dataReader["CountryID"]; location.StateID = (int)dataReader["StateID"]; location.StateName = Convert.ToString(dataReader["StateName"]); locations.Add(location); } } } } return locations; } [WebMethod] public IEnumerable<Location> FindMetros(int stateID) { Collection<Location> locations = new Collection<Location>(); string connectionString = "Data Source=YourServerName; Initial Catalog=YourDatabase; User ID=YourUserName; Password=YourPassword"; using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandText = "SelectMetros"; command.CommandType = CommandType.StoredProcedure; SqlParameter prmStateID = new SqlParameter("@StateID", SqlDbType.Int); prmStateID.Value = stateID; command.Parameters.Add(prmStateID); connection.Open(); using (SqlDataReader dataReader = command.ExecuteReader()) { Location location; if (dataReader != null) while (dataReader.Read()) { location = new Location(); location.MetroID = (int)dataReader["MetroID"]; location.StateID = (int)dataReader["StateID"]; location.MetroName = Convert.ToString(dataReader["MetroName"]); locations.Add(location); } } } } return locations; } }
Above three methods are implemented one for selecting countries, the second one for selecting states and the third one for metros. Each of the method is calling it's respective stored procedure from the database and getting the data correspondingly. Now let's come to the caller page, the client side, and let's say it's name is Sample.aspx. On this page we will be using three dropdownlists, two hidden controls, a submit button and a label control. Why I have taken submit button ? The answer is to do the postback and show the values in a label control what was the selected in the dropdownlists before postback. The two hidden controls are there for taking the values of two dropdownlists ( Metro + State) to the server side during postback. Why? because we are going to change the options of states and metros with client side so their values won't be fetched from server side. Below is the complete code: Sample.ASPX:
<%@ Page Language="C#" EnableEventValidation="false" %> <!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 id="Head1" runat="server"> <title></title> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js" type="text/javascript"></script> <script type="text/javascript"> $(function () { //Set controls instances var ddlParent = $('#<%= ddlParent.ClientID %>'); var ddlChild1 = $('#<%= ddlChild1.ClientID %>'); var ddlChild2 = $('#<%= ddlChild2.ClientID %>'); var hidChild1 = $('#<%= hidChild1.ClientID %>'); var hidChild2 = $('#<%= hidChild2.ClientID %>'); var ajaxUrlForChild1 = 'Sample.asmx/FindStates'; var ajaxUrlForChild2 = 'Sample.asmx/FindMetros'; //Bind events ddlParent.bind('change', ddlParentChange); ddlChild1.bind('change', ddlChild1Change); ddlChild2.bind('change', ddlChild2Change); //Events handlers function ddlParentChange() { hidChild1.val(''); hidChild2.val(''); doAjaxCall(ajaxUrlForChild1, '{countryID: ' + ddlParent.val() + '}', child1SuccessHandler); } function ddlChild1Change() { hidChild1.val(this.value); hidChild2.val(''); doAjaxCall(ajaxUrlForChild2, '{stateID: ' + ddlChild1.val() + '}', child2SuccessHandler); } function ddlChild2Change() { hidChild2.val(this.value); } //Disabled them initially ddlChild1.attr('disabled', 'disabled'); ddlChild2.attr('disabled', 'disabled'); //Populate child1 dropdown if the parent has some selected value if (ddlParent.val() != 0) { doAjaxCall(ajaxUrlForChild1, '{countryID: ' + ddlParent.val() + '}', child1SuccessHandler); } function doAjaxCall(url, data, successHandler) { $.ajax({ type: 'POST', url: url, data: data, contentType: 'application/json; charset=utf-8', dataType: 'json', success: function (response) { successHandler(response); } }); } function child1SuccessHandler(response) { var states = (typeof response.d) == 'string' ? eval('(' + response.d + ')') : response.d; //Remove all the options from all the child dropdowns ddlChild1.find('option').remove(); ddlChild2.find('option').remove(); //Append default option ddlChild1.attr('disabled', false).append($('<option></option>'). attr('value', 0).text('--Please Select--')); var doc = $('<div></div>'); for (var i = 0; i < states.length; i++) { doc.append($('<option></option>'). attr('value', states[i].StateID).text(states[i].StateName) ); } ddlChild1.append(doc.html()); doc.remove(); //Set selected value if there is any value in hidden field ddlChild1.val(hidChild1.val()); //Populate child2 dropdown if the child1 has some selected value if (ddlChild1.val() != 0) { doAjaxCall(ajaxUrlForChild2, '{stateID: ' + ddlChild1.val() + '}', child2SuccessHandler); } } function child2SuccessHandler(response) { var metros = (typeof response.d) == 'string' ? eval('(' + response.d + ')') : response.d; ddlChild2.find('option').remove(); //Append default option ddlChild2.attr('disabled', false).append($('<option></option>'). attr('value', 0).text('--Please Select--')); var doc = $('<div></div>'); for (var i = 0; i < metros.length; i++) { doc.append($('<option></option>'). attr('value', metros[i].MetroID).text(metros[i].MetroName)); } ddlChild2.append(doc.html()); doc.remove(); //Set selected value if there is any value in hidden field ddlChild2.val(hidChild2.val()); } }); </script> </head> <body> <form id="form1" runat="server"> <table> <tr> <td> Country: </td> <td> <asp:DropDownList ID="ddlParent" runat="server"> </asp:DropDownList> </td> </tr> <tr> <td> State: </td> <td> <asp:DropDownList ID="ddlChild1" runat="server"> </asp:DropDownList> </td> </tr> <tr> <td> Metro: </td> <td> <asp:DropDownList ID="ddlChild2" runat="server"> </asp:DropDownList> </td> </tr> </table> <br /> <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" /> <asp:Label ID="lblMessage" runat="server" Text=""></asp:Label> <asp:HiddenField runat="server" ID="hidChild1" /> <asp:HiddenField runat="server" ID="hidChild2" /> </form> </body> </html> <script runat="server"> protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Sample service = new Sample(); ddlParent.DataSource = service.FindAllCountries(); ddlParent.DataTextField = "CountryName"; ddlParent.DataValueField = "CountryID"; ddlParent.DataBind(); ddlParent.Items.Insert(0, new ListItem("--Please Select--", "0")); } } protected void btnSubmit_Click(object sender, EventArgs e) { lblMessage.Text = "You selected: CountryID=" + ddlParent.SelectedValue + ", StateID=" + hidChild1.Value + ", MetroID=" + hidChild2.Value; } </script>
Above the first dropdownlist, the country one, which is the parent one will be populated from server side in the page load event :
Sample service = new Sample(); ddlParent.DataSource = service.FindAllCountries(); ddlParent.DataTextField = "CountryName"; ddlParent.DataValueField = "CountryID"; ddlParent.DataBind(); ddlParent.Items.Insert(0, new ListItem("--Please Select--", "0"));
The next two dropdownlists (state and metro) will be populated through client side. When the country dropdownlist selection is changed an ajax request is made to the webservice and states of that country will be fetched. Below is the screen shot of the data fetched.
When the state dropdownlist selection is changed an ajax request is made to the webservice and metros of that state will be fetched. Below is the screen shot of the data fetched.
Now come to this code:
protected void btnSubmit_Click(object sender, EventArgs e) { lblMessage.Text = "You selected: CountryID=" + ddlParent.SelectedValue + ", StateID=" + hidChild1.Value + ", MetroID=" + hidChild2.Value; }
This code is to show you that we are able to get the selections in server side during postback.
That's it, above you saw how to implement cascading dropdownlist using jQuery and ASP.NET. I have given the example for three dropdownlists, if you have only two for your case then you can just remove the code of third dropdownlist from everywhere.Do let me know your feedback, comments
Great information about making drop down list using CSS and J query.
[url=https://codeasp.net/articles/asp-net/60/cascading-dropdownlist-using-jquery-and-asp-net]code[/url]