2

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

EDIT: If it helps, the error that shows when debugging is enter image description here

5
  • Does it help when you add [HttpPost] attribute to your controller? Commented Feb 16, 2018 at 20:34
  • @LennartStoop Huh!? Why [HttpPost]? OP is "GET'ting" a resource. Commented Feb 16, 2018 at 21:12
  • @TezWingfield I might be a bit sleepy but the ajax request clearly states ProcessOptions.type = "POST" Commented Feb 16, 2018 at 21:57
  • @LennartStoop I may have been a bit sleepy haha. I was focusing on the C#, touché Commented Feb 17, 2018 at 16:57
  • I tried [HttpPost] and it didnt work. @LennartStoop Commented Feb 20, 2018 at 18:17

3 Answers 3

1

The order of parameters is important ,when you are using stored procedure

Your SP first parameter is @FAC_CD. So you should change cmd.Parameters.Add order of parameters.

like this

cmd.Parameters.Add("@FAC_CD", SqlDbType.VarChar).Value = facilityCd;
cmd.Parameters.Add("@BUSUNIT", SqlDbType.VarChar).Value = businessUnitCd;
Sign up to request clarification or add additional context in comments.

1 Comment

Named parameters can be passed in any order!
0

It most likely because of the way you pass the parameters in ajax.data section. There were many questions regarding this issue before. For a start you may see this link :

Ajax Post Parameter is Always Null in MVC app

In short, try one of these :

  • don't use JSON.stringify. Just pass the data as is, OR
  • construct the URL more or less like this : var targeturl = '@Url.Action("Test", "Controller")?id=' + ID;, with GET method, OR
  • change the contentType to "application/x-www-form-urlencoded", if you use POST method

However, I've tried your original codes above and had no problem at all.

Hope it helps, anyway.

2 Comments

I tried these methods and it still doesn't seem to work. It seems that the url is the problem when trying these methods.
I'm curious, have you tried to invoke the method by using Fiddler?
0

The data your are sending is actually a composite object and not two independent strings. You could match this by creating a model on the server side:

public class ProcessRequest
{
    public string Facility { get; set; }
    public string BusinessUnit { get; set; }
}

The controller method signature changes accordingly and must have an HttpPost attribute added since you are sending the HTTP request via POST.

[HttpPost]
public JsonResult GetProcessShown(ProcessRequest requestData) {}

As for the Sql exception, the error is triggered when a NULL value is passed in as a parameter in the stored procedure.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.