WebMatrix - jQuery Cascading Dropdown Lists

I have looked at cascading dropdown lists with WebMatrix previously, but the approach I featured demonstrated the use of jQuery Templates. At the time, the jQuery Templates project looked promising, but since then, the jQuery team have decided not to take them beyond beta stage. So the reason for this article is to illustrate a more "traditional" jQuery approach to managing cascading dropdown lists when developing ASP.NET Web Pages using WebMatrix.

The article makes use of the ubiquitous Northwind database (SQL Server CE 4.0 version) and initially presents the user with a dropdown containing a list of categories drawn from the database. Once the user has selected a category, a second dropdown list is populated with products within that category without the page being posted back to the server. The article will show how to use jQuery to achieve this, using either standard HTML select elements, or the Web Pages HTML helpers for forms.

The first code sample shows a Razor page that includes a standard HTML select element being populated by the categories extracted from the Northwind database:

@{
    var db = Database.Open("Northwind");
    var categories = db.Query("SELECT CategoryId, CategoryName FROM Categories ORDER BY CategoryName");
}
<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>Cascading Dropdown Lists</title>
        <link href="~/favicon.ico" rel="shortcut icon" type="image/x-icon" />
    </head>
    <body>
        <div>
            <select id="categoryId" name="categoryId">
                <option value="">-- Select Category --</option>
            @foreach(var category in categories){
                <option value="@category.CategoryId">@category.CategoryName</option>
            }
            </select>
        </div>
        <div>
            <select id="productId">
                <option value="">-- Select Product --</option>
            </select>
        </div>
    </body>
</html>

A second select element is added to the form. This will display products from the chosen category. The next code sample shows the same thing using the Web Pages DropDownList helper instead of HTML elements:

@{
    var db = Database.Open("Northwind");
    var data = db.Query("SELECT CategoryId, CategoryName FROM Categories ORDER BY CategoryName");
    var categories = data.Select(item => new SelectListItem {
        Value = item.CategoryId.ToString(), 
        Text = item.CategoryName
    });
}

<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>Cascading Dropdown Lists</title>
    </head>
    <body>
        <div>
            @Html.DropDownList("categoryId", "-- Select Category --", categories)
        </div>
        <div>
            @Html.DropDownList("productId", "-- Select Product --", Enumerable.Empty<SelectListItem>())
        </div>
    </body>
</html>

The data is transformed into a collection of SelectListItem objects once it has been obtained from the database. The second (products) dropdown list has an empty collection of SelectListItem objects assigned to it.

When a category has been selected, a filtered list of products needs to be obtained and made available to the second dropdown. Since jQuery is the engine that will be responsible for requesting that data, it makes sense to return it in a format that jQuery is comfortable working with - and that's JSON. Here is the complete GetProducts.cshtml file that is responsible for processing the request and returning the data:

@{
    Layout = null;
    var categoryId = UrlData[0].IsEmpty() ? 1 : UrlData[0].AsInt();  
    var db = Database.Open("Northwind");
    var sql = "SELECT ProductId, ProductName FROM Products WHERE CategoryId = @0 ORDER BY ProductName";
    var products = db.Query(sql, categoryId);
    Json.Write(products, Response.Output);
}

Since you are returning JSON, you must not allow other characters to be included in the response. For that reason, all the default markup has been removed from the file. Layout has been set to null at the top of the file. This acts to override any possibilty that a Layout page has been set in a PageStart file, or might be set in one in the future which would result in extra characters interferring with the generated JSON. In this particular example, I am expecting the CategoryID value to be available in UrlData. If there isn't one, I have decided to use 1 as a default value for demo purposes. Once the data has been extracted from the database, The Json helper is used to convert it into JSON and return it to the requester.

Finally, here is the client-side script that manages the process. It is exactly the same regardless whether you are using HTML elements or HTML helpers:

<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.1.min.js" type="text/javascript"></script>
<script>
    $(function () {
        var productsSelect = $('#productId');
        productsSelect.attr('disabled', true);
        $('#categoryId').change(function () {
            var categoryId = $(this).val();
            $.getJSON('/GetProducts/' + categoryId, function (products) {
                productsSelect.attr('disabled', false);
                productsSelect.empty();
                productsSelect.append(
                        $('<option/>')
                            .attr('value', '')
                            .text('-- Select Product --'));
                $.each(products, function (index, product) {
                    productsSelect.append(
                        $('<option/>')
                            .attr('value', product.ProductId)
                            .text(product.ProductName)
                    );
                });
            });
        });
    });
</script>

You need to include a reference to the jQuery library. I have linked to a CDN-hosted copy of jQuery. The script block initially identifies the dropdown list that the products will appear in, and disables it. Then an event handler is registered that executes when the selected value in the categories dropdown list is changed. The selected value (categoryId) is passed to a getJSON command that requests the GetProducts page. The categoryId value is added to the Url as UrlData. The callback function enables the dropdown list, then clears it of any existing option elements. Then it adds a default option ( -- Select Product -- ) and iterates the products returned in the JSON response and adds those to individual option elements.

A GitHub repo is available which includes a sample site featuring the HTML element approach.

 

Date Posted: Saturday, September 8, 2012 9:56 AM
Last Updated: Friday, December 14, 2012 9:23 PM
Posted by: Mikesdotnetting
Total Views to date: 18304

6 Comments

Saturday, September 8, 2012 6:07 PM - Mauro Otoni

Very good article. I would like to see an example of how popular a JQGrid.
Thanks again

Monday, February 11, 2013 1:26 PM - marcelle

This was very helpful. But how do i save the value selected if it is inside a form

Saturday, November 9, 2013 7:03 PM - Serhat Albayoglu

Thank you for providing the above. However when I tried the above code with MVC 4 + Razor, it fails from the start. The part

@Html.DropDownList("categoryId", "-- Select Category --", categories)

complains that it does not have a definition for DropDownList. I am new to MVC and just wondering if it is worthwhile to continue with the learning curve. These as you rightly stated could be done so simply with .NET ASP web pages as you describe here. But when it is MVC the easiest things can become so complicated and I still have to find a sample that works. Any suggestions ?

Saturday, November 9, 2013 7:22 PM - Mike

@Serhat,

The MVC dropdownlist helper is not the same as the one featured here for Web Pages. Having said that, I have no idea why you are getting the error that you report. You should post more details in a question to forums.asp.net

Thursday, May 15, 2014 4:45 PM - Chris

$.getJSON('/GetProducts/'....
Mike please, where did the GetProducts come from. I am new to JSON

Thursday, May 15, 2014 5:13 PM - Mike

@Chris,

GetProducts is GetProducts.cshtml. The contents of the file are included in the article, and you can also have a look at it in the download. The getJSON method makes n AJAX request to the file.
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.