How to filter database records based on static dropdownlist in mvc 5

Question

i want to filter database record based on database column QtyRecieved,QtyRecievedand Void using static html dropdwonlist

QtyRecieved,QtyRecieved is decimal and void is boolean

here is what i have tried

@Html.DropDownList("Filter", new List<SelectListItem>

           {
              new SelectListItem{ Text="Open", Value = "0" },
              new SelectListItem{ Text="Partial", Value = "1" },
              new SelectListItem{ Text="All", Value = "2" }
           })

i have used ajax to send the request to controller

$("#Filter").change(function () {
    var listval = $("select option:selected").text();
        $.ajax({
            type: "GET",
            url: "@Url.Action("Index", "MaterialRequest")",
            data: { id: listval }
        });
    });

In my controller i have put else if condition to display records i want, when Open is selected it show records where QtyRecieved == QtyRequested

when Partial is selected it show records where QtyRecieved < QtyRequested and Void = True

and

when All is selected then it will show all records

Please help me with the query to filter records or show any alternate way to do so

public ActionResult Index(string listval)
        {
            if (listval == "Open")
            {
                ViewBag.Items = db.Query<MaterialDeptItemVw>("Select mt.MaterialRequestId, mt.TDate, d.DepartmentName, it.ItemName, mt.QtyRequested, mt.Comment, mt.RecievedDateTime , u.UnitName from MaterialRequest mt INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId INNER JOIN Items it ON mt.ItemId = it.ItemId INNER JOIN Units u ON it.UnitId = u.UnitId where QtyRecieved = QtyRequested");
            }
            else if (listval == "Partial")
            {
                ViewBag.Items = db.Query<MaterialDeptItemVw>("Select mt.MaterialRequestId, mt.TDate, d.DepartmentName, it.ItemName, mt.QtyRequested, mt.Comment, mt.RecievedDateTime , u.UnitName from MaterialRequest mt INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId INNER JOIN Items it ON mt.ItemId = it.ItemId INNER JOIN Units u ON it.UnitId = u.UnitId where QtyRecieved < QtyRequested and Void = 0");
            }
            else if (listval == "All")
            {
                ViewBag.Items = db.Query<MaterialDeptItemVw>("Select mt.MaterialRequestId, mt.TDate, d.DepartmentName, it.ItemName, mt.QtyRequested, mt.Comment, mt.RecievedDateTime , u.UnitName from MaterialRequest mt INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId INNER JOIN Items it ON mt.ItemId = it.ItemId INNER JOIN Units u ON it.UnitId = u.UnitId");
            }

            return View();
        }

Answer 1

You have several issues in the code:

1) The action method declared as public ActionResult Index(string listval) while in AJAX callback you have data: { id: listval } parameter, hence the AJAX call never reached the controller action because it called with different parameter name.

2) return View() is not applicable while using AJAX, it is necessary to return JSON data or partial view and update target DOM element from AJAX result.

Therefore, you should change parameter name to exactly matches with AJAX data parameter and use return Json():

public ActionResult Index(string id)
{
    // using DRY principle, just write the same part of the query in a string variable
    // and add another part depending on case value inside switch block below
    string baseQuery = @"Select mt.MaterialRequestId, mt.TDate, 
                         d.DepartmentName, it.ItemName, 
                         mt.QtyRequested, mt.Comment, 
                         mt.RecievedDateTime , u.UnitName from MaterialRequest mt 
                         INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId 
                         INNER JOIN Items it ON mt.ItemId = it.ItemId 
                         INNER JOIN Units u ON it.UnitId = u.UnitId";

    switch (id)
    {
        case "Open":
            baseQuery += " where QtyRecieved = QtyRequested";
            break;

        case "Partial":
            baseQuery += " where QtyRecieved < QtyRequested and Void = 0";
            break;

        case "All":
            break; // not doing anything

        default: goto case "All";
    }

    // create a list of object from query results
    var items = db.Query<MaterialDeptItemVw>(baseQuery).ToList();

    // return JSON data to populate target element
    return Json(items, JsonRequestBehavior.AllowGet);
}

Then modify AJAX call to update target DOM element based on returned data:

$("#Filter").change(function () {
    var listval = $("select option:selected").text();
    $.ajax({
        type: "GET",
        url: '@Url.Action("Index", "MaterialRequest")',
        data: { id: listval },
        success: function (result) {
           // an example to update target element
           $('#targetElementID').html(result);
        },
        error: function (xhr, status, err) {
           // error handling
        }
    });
});