I am trying to filter my dropdown from previous dropdowns but json is passing the results as null. In the jquery when debugged, it shows the values of the businessunitCd and facilityCd but in the controller it shows up as null.
When I try to debug, it says that my SQLPersistor has an error on this line SqlDataReader dr = cmd.ExecuteReader(); and an error saying
they cannot find
@FAC_CD
My controller:
public JsonResult GetProcessShown(string businessUnitCd, string facilityCd)
{
IEnumerable<SelectListItem> processListItems = new List<SelectListItem>();
SQLPersistor p = new SQLPersistor(EADConnString);
List<ProcessShown> processes = p.GetProcessShown(businessUnitCd, facilityCd);
ProcessShown defaultProcessShown = new ProcessShown();
defaultProcessShown.ProcessCd = string.Empty;
defaultProcessShown.ProcessDesc = "Select Process...";
processes.Insert(0, defaultProcessShown);
processListItems = (from pr in processes
select new SelectListItem
{
Text = pr.ProcessDesc,
Value = pr.ProcessCd
});
return Json(processListItems, JsonRequestBehavior.AllowGet);
}
my SQLPersistor :
public List<ProcessShown> GetProcessShown(string businessUnitCd, string facilityCd)
{
List<ProcessShown> Processes = new List<ProcessShown>();
if (businessUnitCd != string.Empty && facilityCd != string.Empty)
{
using (SqlConnection cnn = new SqlConnection(connString))
{
cnn.Open();
string sql = "[Environmental].[GetProcessShown]";
using (SqlCommand cmd = new SqlCommand(sql, cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@BUSUNIT", SqlDbType.VarChar).Value = businessUnitCd;
cmd.Parameters.Add("@FAC_CD", SqlDbType.VarChar).Value = facilityCd;
SqlDataReader dr = cmd.ExecuteReader();
using (DataTable dt = new DataTable())
{
dt.Load(dr);
foreach (DataRow row in dt.Rows)
{
ProcessShown ps = new ProcessShown(row["PROCESS_CD"].ToString(), !Convert.IsDBNull(row["PROCESS_NAME"]) ? row["PROCESS_NAME"].ToString() : string.Empty);
Processes.Add(ps);
}
}
}
}
}
return Processes;
}
My jquery:
$("#Facility").change(function () {
$("#ProcessShown").empty();
$("#Aspects").empty();
$("#AspectsCategory").empty();
$("#Activity").empty();
//document.getElementById("OnCallContactMessage").style.display = "none";
// Incase theyve double clicked a cause and changed their mind
document.getElementById("BusinessUnit").required = true;
document.getElementById("Facility").required = true;
document.getElementById("ProcessShown").required = true;
document.getElementById("Aspects").required = true;
document.getElementById("AspectCategoryDiv").required = true;
document.getElementById("ActivityName").required = true;
var ProcessOptions = {};
ProcessOptions.url = $("ProcessDiv").data('url');
ProcessOptions.type = "POST";
ProcessOptions.data = JSON.stringify({ businessUnitCd: $("#BusinessUnit").val(), facilityCd: $("#Facility").val() });
ProcessOptions.datatype = "json";
ProcessOptions.contentType = "application/json";
ProcessOptions.success = function (processes) {
if (processes.length > 0) {
for (var i = 0; i < processes.length; i++) {
$("#ProcessShown").append(new Option(processes[i].Text, processes[i].Value));
}
}
};
ProcessOptions.error = function () { alert("Error occurred while getting Processes.") };
$.ajax(ProcessOptions);
});
My view:
@* Processes Shown - Dropdown *@
<div class="row" style="padding-top:10px">
<div class="col-xs-3" style="padding-top:8px">
@Html.LabelFor(model => model.ProcessShown, new { @class = "group-label" })
</div>
<div class="col-xs-8" id="ProcessDiv" data-url="@Url.Action("GetProcessShown", "Aspects")">
@Html.DropDownListFor(model => model.ProcessShown, Model.ProcessList, new { @class = "form-control req-field", @required = "required" })
</div>
</div>
My stored procedure:
ALTER PROCEDURE [Environmental].[GetProcessShown]
@FAC_CD VarChar(255),
@BUSUNIT VarChar(255)
AS
BEGIN
SET NOCOUNT ON;
SELECT CDTBL_PROCESSES.PROCESS_CD, PROCESS_NAME
FROM(SELECT DISTINCT PROCESS_CD FROM ENVIRN_BU_PROCESS_FAC WHERE FAC_CD = @FAC_CD AND BUS_UNIT_CD = @BUSUNIT) PROCESSES
JOIN CDTBL_PROCESSES
ON CDTBL_PROCESSES.PROCESS_CD = PROCESSES.PROCESS_CD
AND OBSLT_EST IS NULL
END

[HttpPost]attribute to your controller?ProcessOptions.type = "POST"