Cascading Drop Downs (Solution)

cbp8092

whitbacon really got me started with his post but I wanted to show an example that is in a MVC project. To hopefully make the next persons life easier :-) There is a lot of stuff going on so don't hesitate to send me a message.

whitbacon post:
https://datatables.net/forums/discussion/15375/ipopts-for-asynchronous-update-on-select.#Item_16

Javascript

[code]
var editor;
var remove;
var initExposure = null;

function loadLobList() {
var dropDownItems = new Array();
$.ajax({
type: "GET",
url: '../ComboBox/GetLobExposure',
async: false,
dataType: 'json',
success: function (json) {
$.each(json, function (index, element) {
var lbl = element.Text;
var val = element.Value;
var arrayItem = { "label": lbl, "value": val };
dropDownItems.push(arrayItem);
});
}
});
return dropDownItems;
}

function loadExposureType(id) {
var dropDownItems = new Array();
$.ajax({
type: "GET",
url: '../ComboBox/GetExposureType',
async: false,
data: "ExposureTypeID=" + id,
dataType: 'json',
success: function (json) {
$.each(json, function (index, element) {
var lbl = element.Text;
var val = element.Value;
var arrayItem = { "label": lbl, "value": val };
dropDownItems.push(arrayItem);
});
}
});
return dropDownItems;
}

var doExposureDatatableStuff = function () {

if (initExposure == null) {

editor = new $.fn.dataTable.Editor({
"ajaxUrl": "UpdateExposure",
"domTable": "#exposureDataTable",
"idSrc": "0",
"events": {
"onInitEdit": function (json, data) {
editor.field('ExposureTypeDescription').update(loadExposureType(editor.get('LOB'))); //Show correct drop down options on edit initialization
},
"onClose": function (json, data) {
editor.field('ExposureTypeDescription').update(loadExposureType(0)); //On close includes selecting the X or Update button. In order to get binding to occur correctly on the next edit you need to load all the options again otherwise it will default to the first value not the value in your grid.
}
},
"fields": [
{
"label": "Line of Business:",
"name": "LOB",
"dataProp": "4",
"type": "select"
}, {
"label": "Exposure Type:",
"dataProp": "5",
"name": "ExposureTypeDescription",
"type": "select"
}, {
"label": "Exposure Amount:",
"dataProp": "3",
"name": "ExposureAmt",
}
]
});

remove = new $.fn.dataTable.Editor({
"ajaxUrl": "RemoveExposure",
"domTable": "#exposureDataTable",
"idSrc": "0"
});

//Building out intelligent drop downs. Accessing fields within the editor grid.
$(editor.field('LOB').update(loadLobList()));

$(editor.field('ExposureTypeDescription').update(loadExposureType(0))); //Load all exposure types on page get. This is necessary for model binding to occur correctly.

$('select', editor.node('LOB')).on('change', function () {
editor.field('ExposureTypeDescription').update(loadExposureType(editor.get('LOB')));
});

$('#exposureDataTable').dataTable({
"sDom": "Tfrtip",
"bServerSide": true,
"sAjaxSource": "ExposureAjaxHandler",
"sPaginationType": "full_numbers",
"aoColumns": [
{
"mData": "0"
, "bVisible": false
},
{
"mData": "1"
},
{
"mData": "2"
},
{
"mData": "3"
, "mRender": function (data, type, full) {
if (type === 'display') {
return addCommas(data, ',', ',');
}
return data;
}
},
{
"mData": "4" //ID_Lob
, "bVisible": false
},
{
"mData": "5" //ID_ExposureType
, "bVisible": false
}
],
"oTableTools": {
"sRowSelect": "single",
"aButtons": [
{
"sExtends": "editor_edit"
, "editor": editor
},
{
"sExtends": "editor_remove"
, "editor": remove
}
]
}
});
initExposure = new Object();
} //if init close
} //doDataTableStuff

function addCommas(nStr, nThSep, nDecSep) {
nStr += '';
x = nStr.split(',');
x1 = x[0];
x2 = x.length > 1 ? nDecSep + x[1] : '';
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) { x1 = x1.replace(rgx, '$1' + nThSep + '$2'); }
return x1 + x2;
}
[/code]

ComboBoxController that is called in the URL. I would recommend you download and use Fiddler to verify your calls. Fast simple and easy to detect any errors. http://fiddler2.com/

[code]
//Coverage Screen Cascading Drop Downs for Exposure
[HttpGet]
public JsonResult GetLobExposure()
{
return Json(new SelectList(BusinessResources.GetLineOfBusinessList(), "Key", "Value"), JsonRequestBehavior.AllowGet);
}

[HttpGet]
public JsonResult GetExposureType(int ExposureTypeID)
{
IQueryable exposureType = db.ExposureType_Custom.AsQueryable();
if (ExposureTypeID > 0)
{
exposureType = db.ExposureType_Custom.Where(od => od.ID_Lob == ExposureTypeID && od.IsDisabled == false);
}
return Json(new SelectList(exposureType, "ID_ExposureType", "ExposureTypeDescription"), JsonRequestBehavior.AllowGet);
}
[/code]

What is this Business Resources class referenced in the controller. I create a class to perform my look ups. Example BusinessResources class with just the methods used in this example.
[code]
public static class BusinessResources
{

public static Dictionary GetLineOfBusinessList()
{
Dictionary lob = new Dictionary();

Capture2Entities db = new Capture2Entities();
lob = db.LineOfBusiness_Custom.Where(x => x.IsDisabled == false)
.OrderBy(x => x.LobName)
.Select(x => new { x.ID_Lob, x.LobName })
.ToDictionary(key => key.ID_Lob.ToString(), val => val.LobName);
return lob;
}

public static Dictionary GetExposureTypeList()
{
Dictionary exposureType = new Dictionary();

Capture2Entities db = new Capture2Entities();
exposureType = db.ExposureType_Custom.Where(x => x.IsDisabled == false)
.OrderBy(x => x.ExposureTypeDescription)
.Select(x => new { x.ID_ExposureType, x.ExposureTypeDescription })
.ToDictionary(key => key.ID_ExposureType.ToString(), val => val.ExposureTypeDescription);
return exposureType;
}

}
[/code]

cbp8092

Update method that posts information to database:

[code]
public ActionResult UpdateExposure(string action, string table, string id)
{
int key = Convert.ToInt32(id);
Exposure_Custom exposure = db.Exposure_Custom.Find(key);

exposure.ID_Lob = Convert.ToInt32(Request["data[LOB]"]);
exposure.ID_ExposureType = Convert.ToInt32(Request["data[ExposureTypeDescription]"]);
exposure.ExposureAmt = Convert.ToDecimal(Request["data[ExposureAmt]"]);

exposure.Update_User = User.Identity.Name;
exposure.Update_Date = DateTime.Now;

db.Entry(exposure).State = System.Data.EntityState.Modified;
db.SaveChanges();

return RedirectToAction("AjaxHandler");
}
[/code]