Cascading dropdowns using MVC Razor Entity Framework and stored procedures

Question

I am new to MVC and Razor and have looked at so many examples and tutorials of this but have not been able to get it working. It seems so simple but I think the fact that I am trying to use stored procedures complicates everything.

I am trying to create a few simple cascading dropdowns in MVC using stored procedures and entity framework.

My entity consists of the following stored procedures:

exec prcGetMakes
exec prcGetModels 'BMW'
exec prcGetVariants '1 SERIES 5-DOOR'

I created a view model class called VehicleViewModel:

public class VehicleViewModel
{
    [Display(Name = "Manufacturer")]
    public string BrandName { get; set; }
    public string BrandID { get; set; }

    [Display(Name = "Model")]
    public string ModelName { get; set; }
    public string ModelID { get; set; }

    [Display(Name = "Variant")]
    public string VariantName { get; set; }

    public IEnumerable<SelectListItem> Manufacturer { get; set; }
    public IEnumerable<SelectListItem> Model { get; set; }
    public IEnumerable<SelectListItem> Variant { get; set; }
}

I'm using the default controller class called HomeController.cs:

public class HomeController : Controller
{
    VehicleInfoEntities db = new VehicleInfoEntities();

    public ActionResult Index()
    {
        var app = new VehicleViewModel
        {
            Manufacturer = GetMakes()
        };

        return View(app);
    }


    private IEnumerable<SelectListItem> GetMakes()
    {
        var list = new VehicleViewModel();
        IEnumerable<SelectListItem> manufacturer = from s in db.prcGetMakes(null)
                                            select new SelectListItem
                                            {
                                                Selected = s.ToString() == "Active",
                                                Text = s.BrandName,
                                                Value = s.BrandID
                                            };
        return manufacturer;

    }

    private IEnumerable<SelectListItem> GetModels(string brandName)
    {
        var list = new VehicleViewModel();
        IEnumerable<SelectListItem> models = from s in db.prcGetModels(brandName)
                                            select new SelectListItem
                                            {
                                                Selected = s.ToString() == "Active",
                                                Text = s.ModelName,
                                                Value = s.ModelID
                                            };
        return models;

    }

    private IEnumerable<SelectListItem> GetVariants(string modelName)
    {
        var list = new VehicleViewModel();
        IEnumerable<SelectListItem> variants = from s in db.prcGetVariants(modelName)
                                             select new SelectListItem
                                             {
                                                 Selected = s.ToString() == "Active",
                                                 Text = s.VariantName,
                                                 Value = s.VariantName
                                             };
        return variants;

    }
}

This is my view

@model WebApplication12.Models.VehicleViewModel

@{
    ViewBag.Title = "Home Page";
}
@using (Html.BeginForm("Index", "Sample", FormMethod.Post))
{
@Html.AntiForgeryToken()

@Html.ValidationSummary()
<div class="form-group" style="text-align:left;">
    @Html.Label("Manufacturer:", new { @class = "col-md-2 control-label" })
    <div class="col-md-10">
        @Html.DropDownList("Manufacturer", ViewData["Manufacturer"] as SelectList, new { @class = "form-control" })
    </div>
</div><br />

}

The desired outcome is

I would just like to know now how to use JQuery to make the cascading DropDowns work and filter on selection of each DropDown.

Answer 1

Compared to thick client apps, web cascading drop down lists are convoluted and complex, but that's web programming! Keep in mind that javascript doesn't care about your MVC stuff at all. You just need a web method (something that responds to web URL) that takes a parameter and returns the correct list in the correct format. Whatever method it uses internally (EF stored procedure, hard coded list, PHP reading a text file) doesn't make any difference

To do what you want, you need to add an "event handler" to your drop down which calls some javascript which calls your method to look up the correct list, then populates the target drop down with that list.

I'll put some sample code here but

• there is a degree of 'wiring up' required (using the correct names of things)

• I'm going to make some assumptions

• I learnt this from the web and some syntax may be incorrect

So first add an onchange event handler to your drop down so that when somehing is changed it fires some javascript code

@Html.DropDownList("Manufacturer", 
                   ViewData["Manufacturer"] as SelectList, 
                   htmlAttributes: 
                         new { @class = "form-control" , 
                               @onchange = "refreshModelFromManufacturer()"
                             }
                   )

When the drop down value is changed it will call javascript function refreshModelFromManufacturer()

Here is the javascript function. Hopefully you know how to add this. It requires you to also have a drop down with name "Model" (which you don't have in your view yet so add it):

function refreshModelFromManufacturer()() {
    // get references to the source and target drop downs html controls
    // These are jquery searches to find the drop down controls

    // find a control with id=Manufacturer
    src = $("#Manufacturer");

    // find a control with id=Model (you need to add this to your view)
    tgt = $("#Model");

    // clear drop down
    tgt.empty();

    // Get new model dataset via ajax 
    // based on manufacturer
    // The url parameter points at your web method
    $.ajax({
        type: 'GET',
        url: 'Home/GetModels',
        dataType: 'json',
        data: { brandName: src.val() },
        // success is called when dataset returns
        success: function (p) {
                // Populate with each returned member
                $.each(p, function (i, pr) {
                    tgt.append(
                        '<option value="' + pr.Value + '">' +
                        pr.Text + '</option>'
                        );
                })
            }
        });
    }

This is the most likely place that things will go wrong. Javascript is unforgiving, case sensitive, and has waaaaay too many brackets that are easily messed up. Make sure you are using source control so you can roll back because it can be impossible to work out a misplaced bracket sometimes because the code fails somewhere deep inside the jquery library, giving you no clue

The last thing is that your web method needs to be of type Json so that the ajax call works

public JsonResult GetModels(string brandName)
{
    var list = db.prcGetModels(brandName)

    return Json(new SelectList(list, "your id column","your label column"));
}

The only other difference in my code is that actually define a drop down just like this:

@Html.DropDownList("Customer_ID", null, htmlAttributes: new { @class = "form-
control", @onchange = "refreshProjectFromClient()" })

I think that if your ViewBag name is the same as your drop down, then you don't need to explicitly define the viewbag (my second parameter is null)

For debugging (because this won't work first time), make sure you know how to use the F12 debugger to debug javascript as well as observe internal web calls (the network tab). For example when you run this, you should see calls to the Home/GetModels method in the network tab