How to Populate Cascading Dropdownlist with AJAX in ASP.NET

How to Populate Cascading Dropdownlist with AJAX in ASP.NET

Cascading dropdownlist are those that depend on the selected value of other dropdownlist. Here, when a value is selected on a dropdownlist, the depending dropdownlist is populated with another set of values. A classic example of cascading dropdownlist is of “Countries & Cities”. One dropdownlist carries Country names and other the Cities of the Selected Country.

When a user selects a country (example United States) in the Country dropdownlist then the City dropdownlist is populated with the cities of that country (example NYC, Albany, San Francisco, Washington DC, Chicago, Los Angeles).

Page Setup

Create a page and name it “index.aspx”. Add 2 dropdownlist to this page, one for categories and other for products, and a loading.gif image.

This image will tell users whenever the AJAX request is going on.

<asp:DropDownList ID="categoryDropDownList" runat="server" />
<asp:DropDownList ID="productDropDownList" runat="server" />
<div class="imageDiv">
    <img src="loading.gif" />
</div>

cascading dropdownlist

Add the jQuery code for the change event of category dropdownlist control. This event will be called whenever a user selects a category.

Then the jQuery AJAX code inside this event will be executed. This code will call a C# function “bindproduct” on the .cs page and passes the selected CategoryId to its parameter.

$("#categoryDropDownList").change(function (e) {
    $.ajax({
        type: "POST",
        url: "index.aspx/bindproduct",
        contentType: "application/json; charset=utf-8",
        data: '{"categoryId":"' + $(this).val() + '"}',
        dataType: "json",
        success: function (result, status, xhr) {
            $("#productDropDownList").html(result.d);
        },
        error: function (xhr, status, error) {
            alert("Result: " + status + " " + error + " " + xhr.status + " " + xhr.statusText)
        }
    });
});
$(document).ajaxStart(function () {
    $("img").show();
});

$(document).ajaxStop(function () {
    $("img").hide();
});
Now moving to the “index.aspx.cs”

I am calling the “BindCategory()” function in the Page_Load event. This function will populate the Category dropdownlist control with values from the database.

For the purpose of this tutorial I am using NORTHWND database. The “Categories” table will fill the Category dropdownlist while the “Products” table will fill the Product dropdownlist.

categories table

Products table

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindCategory();
    }
}

void BindCategory()
{
    string query = "SELECT CATEGORYID,CATEGORYNAME FROM CATEGORIES";

    SqlConnection conn = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    DataTable dataTable = new DataTable();
    conn.ConnectionString = configurationManager.ConnectionStrings["CS"].ConnectionString;
    cmd.Connection = conn;

    cmd.CommandType = CommandType.Text;
    cmd.CommandText = query;
    cmd.Connection = conn;

    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
    sqlDataAdapter.SelectCommand = cmd;
    sqlDataAdapter.Fill(dataTable);

    categoryDropDownList.DataSource = dataTable;
    categoryDropDownList.DataTextField = "CategoryName";
    categoryDropDownList.DataValueField = "CategoryID";
    categoryDropDownList.DataBind();
    categoryDropDownList.Items.Insert(0, new ListItem("Select", "0"));
    productDropDownList.Items.Insert(0, new ListItem("Select", "0"));
}

[WebMethod]
public static string bindproduct(string categoryId)
{
    string query = "SELECT PRODUCTID,PRODUCTNAME FROM PRODUCTS WHERE CATEGORYID=" + categoryId;

    SqlConnection conn = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    DataTable dataTable = new DataTable();
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
    cmd.Connection = conn;

    cmd.CommandType = CommandType.Text;
    cmd.CommandText = query;
    cmd.Connection = conn;

    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
    sqlDataAdapter.SelectCommand = cmd;

    sqlDataAdapter.Fill(dataTable);
    StringBuilder stringBuilder = new StringBuilder();
    stringBuilder.Append("<option value=\"0\">Select</option>");
    foreach (DataRow dr in dataTable.Rows)
        stringBuilder.Append("<option value=\"" + dr["ProductID"] + "\">" + dr["ProductName"] + "</option>");
    return stringBuilder.ToString();
}

The most important part of the above code is the “bindproduct()” function. I have made it a “WebMethod” because I will be called from the jQuery AJAX code.

  • This function receives the selected categoryId, of the Category dropdownlist, to its parameter.
  • Then I am fetching all the products that are contained in this categoryId.
  • I am looping through the products and creating “select options” from them (‘<option value=”ProductID”>ProductName</option>’).
  • Finally returning them to the jQuery AJAX function which in turns added them to the Product dropdownlist.
  • Hence the Product dropdownlist is populated.

DEMO DOWNLOAD

In this way you can Populate Cascading Dropdownlist with jQuery AJAX and no Page PostBack happens.

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "WordPress, SEO, jQuery, HTML" and more.