Filter jQuery DataTable based on multiple DropDownList selection in ASP.Net MVC

Last Reply 6 months ago By dharmendr

Posted 6 months ago

Bro, i have a small issue, my data table is working fine. I am having actually two dropdown list one is having year and one will be having month name, so if from one dropdown user select only year, datatable will show report according to that year, and if the user select month from dropdown the report will be shown on the basis of that year and month of that particular year. This is the code bro which i am trying kindly help

        public ActionResult Index()
        {

            List<SelectListItem> Monthname = new List<SelectListItem>();
            Monthname.Add(new SelectListItem { Text = "January", Value = "January" });
            Monthname.Add(new SelectListItem { Text = "February", Value = "February" });
            Monthname.Add(new SelectListItem { Text = "March", Value = "March" });
            Monthname.Add(new SelectListItem { Text = "April", Value = "April" });
            Monthname.Add(new SelectListItem { Text = "May", Value = "May" });
            Monthname.Add(new SelectListItem { Text = "June", Value = "June" });
            Monthname.Add(new SelectListItem { Text = "July", Value = "July" });
            Monthname.Add(new SelectListItem { Text = "August", Value = "August" });
            Monthname.Add(new SelectListItem { Text = "September", Value = "September" });
            Monthname.Add(new SelectListItem { Text = "October", Value = "October" });
            Monthname.Add(new SelectListItem { Text = "November", Value = "November" });
            Monthname.Add(new SelectListItem { Text = "December", Value = "December" });
            ViewBag.Month = Monthname;

            ViewBag.years = new SelectList(Enumerable.Range(DateTime.Today.Year, 20).Select(x => new SelectListItem()
            {
                Text = x.ToString(),
                Value = x.ToString()
            }), "Value", "Text");

            return View();

 

        public ActionResult LoadData(string year,string month)
        {
            string st = ConfigurationManager.ConnectionStrings["Con"].ConnectionString;
            List<FeedBack> feedbacks = new List<FeedBack>();
            DHIFeedbackEntities2 db = new DHIFeedbackEntities2();
            using (SqlConnection con = new SqlConnection(st))
            {
                con.Open();
                //using (SqlCommand cmd = new SqlCommand("Select FeedbackUserName,FeedBackUserEmailID,FeedBackComment,Designation,Organization,ContactNo,City FROM [DHIFeedback].[dbo].[FeedBack] where [FeedbackUserName]=@FeedbackUserName or @FeedbackUserName IS NULL", con))
                using (SqlCommand cmd = new SqlCommand("Select FeedbackUserName,FeedBackUserEmailID,FeedBackComment,Designation,Organization,ContactNo,City,convert(varchar(100),[FeedBackDate],103) as Feedback_Date FROM [DHIFeedback].[dbo].[FeedBack] where year([FeedBackDate])=@FeedBackDate and month([FeedBackDate])=@FeedBackMonth or @FeedBackDate IS NULL order by [FeedBackDate] desc ", con))
                {
                    cmd.Parameters.AddWithValue("@FeedBackDate", !string.IsNullOrEmpty(year) ? year : (object)DBNull.Value);
                    cmd.Parameters.AddWithValue("@FeedBackMonth", !string.IsNullOrEmpty(month) ? month : (object)DBNull.Value);
                    //cmd.Parameters.AddWithValue("@FeedBackDate", year);
                    using (SqlDataReader rd = cmd.ExecuteReader())
                    {
                        while (rd.Read())
                        {
                            feedbacks.Add(new FeedBack
                            {
                                FeedbackUserName = rd["FeedbackUserName"].ToString(),
                                FeedBackUserEmailID = rd["FeedBackUserEmailID"].ToString(),
                                FeedBackComment = rd["FeedBackComment"].ToString(),
                                Designation = rd["Designation"].ToString(),
                                Organization = rd["Organization"].ToString(),
                                ContactNo = rd["ContactNo"].ToString(),
                                City = rd["City"].ToString(),
                                Feedback_date = rd["Feedback_Date"].ToString()

                            });
                        }
                    }
                }
                con.Close();
            }
            return Json(new { data = feedbacks }, JsonRequestBehavior.AllowGet);

 

@Html.DropDownListFor(model=>model.SelectedMonth,(IEnumerable<SelectListItem>)ViewBag.month, "--Select Month--", new { @class = "form-control", onchange = "UserChanged1()" })
@Html.DropDownListFor(model=>model.Selectedyear,(IEnumerable<SelectListItem>)ViewBag.years,"--Select Year--",new {@class="form-control", onchange="UserChanged()" })
   

 

        $(function () {
            $(document).on("click", ".opencomment", function () {
                var mycomment = $(this).data('id');
                $(".modal-body #commentdesc").html(mycomment);
            });
            ApplyDataTable("");
        });

            jQuery.extend(jQuery.fn.dataTableExt.oSort, {
                "date-uk-pre": function (a) {
                    var ukDatea = a.split('/');
                    return (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
                },

                "date-uk-asc": function (a, b) {
                    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
                },

                "date-uk-desc": function (a, b) {
                    return ((a < b) ? 1 : ((a > b) ? -1 : 0));
                }
            });


        function ApplyDataTable(year,month) {
            $.ajax({
                url: '/ViewFeedback/LoadData',
                type: 'GET',
                dataType: 'json',
                data: { year: year , month:month},
                success: function (data) {
                    $('#FeedbackDetails').DataTable({
                        "bDestroy": true,
                        "aaData": data.data,
                        "lengthMenu": [[5, 10, 25, 50, 100, -1], [5, 10, 25, 50, 100, "All"]],
                        "autoWidth": true,
                        "responsive": true,
                        "lengthChange": true,
                        "ordering": true,
                        "fnRowCallback": function (nRow, aData, iDisplayIndex) {
                            var oSettings = this.fnSettings();
                            $("td:first", nRow).html(oSettings._iDisplayStart + iDisplayIndex + 1);
                            return nRow;
                        },

                        "columns": [
                            { "data": null, "autoWidth": true },
                            { "data": "FeedbackUserName", "name": "User Name", "autoWidth": true },
                            { "data": "FeedBackUserEmailID", "name": "Email ID", "autoWidth": true },
                            { "data": "FeedBackComment", "name": "Comment", "autoWidth": true },
                            { "data": "Designation", "name": "Designation", "autoWidth": true },
                            { "data": "Organization", "name": "Organization", "autoWidth": true },
                            { "data": "ContactNo", "name": "Contact No", "autoWidth": true },
                            { "data": "City", "name": "City", "autoWidth": true },
                            {
                                "data": "Feedback_date", "sType": "date-uk", "autoWidth": true
                            },
                        ],
                        columnDefs: [{
                            targets: 3,
                            //data:"FeedbackID",
                            render: function (data, type, row, meta) {
                                if (type === 'display' && data.length > 40) {
                                    return '<span title="' + data + '">' + data.substr(0, 38) + '...<a href="" data-id="' + data + '" data-toggle="modal" class="opencomment" data-target="#myModal">Show More</a>';
                                }
                                else {
                                    return data;
                                }
                            }

                        }],

                        "language": {
                            "emptyTable": "No Events Found Related To This Month or Year"
                        },
                    });
                }
            });
        }

        //Apply custom search on jquery DataTable
        function UserChanged() {
            //apply search for Year base report
            var year = $('#Selectedyear option:selected').val();
            //var month = $('#SelectedMonth option:selected').val();
            ApplyDataTable(year);
        }
        function UserChanged1() {
            //apply search for Year base report
            //var year = $('#Selectedyear option:selected').val();
            var month = $('#SelectedMonth option:selected').val();
            ApplyDataTable(month);
        }

 

Posted 6 months ago

Hi sunnyk21,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Namespace

using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;

Model

public class Customer
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string Company { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
    public string Phone { get; set; }
}

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        List<SelectListItem> countries = new List<SelectListItem>();
        countries.Add(new SelectListItem() { Text = "Argentina", Value = "Argentina" });
        countries.Add(new SelectListItem() { Text = "Austria", Value = "Austria" });
        countries.Add(new SelectListItem() { Text = "Belgium", Value = "Belgium" });
        TempData["Country"] = new SelectList(countries, "Value", "Text");

        List<SelectListItem> cities = new List<SelectListItem>();
        cities.Add(new SelectListItem() { Text = "Madrid", Value = "Madrid" });
        cities.Add(new SelectListItem() { Text = "Buenos Aires", Value = "Buenos Aires" });
        cities.Add(new SelectListItem() { Text = "Boise", Value = "Boise" });
        cities.Add(new SelectListItem() { Text = "Graz", Value = "Graz" });
        cities.Add(new SelectListItem() { Text = "Salzburg", Value = "Salzburg" });
        cities.Add(new SelectListItem() { Text = "Bruxelles", Value = "Bruxelles" });
        cities.Add(new SelectListItem() { Text = "Charleroi", Value = "Charleroi" });
        TempData["City"] = new SelectList(cities, "Value", "Text");

        return View();
    }

    public ActionResult LoadData(string country, string city)
    {
        string st = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        List<Customer> customers = new List<Customer>();
        using (SqlConnection con = new SqlConnection(st))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE (Country = @Country OR @Country IS NULL) AND (City = @City  OR @City IS NULL)", con))
            {
                cmd.Parameters.AddWithValue("@Country", !string.IsNullOrEmpty(country) ? country : (object)DBNull.Value);
                cmd.Parameters.AddWithValue("@City", !string.IsNullOrEmpty(city) ? city : (object)DBNull.Value);
                using (SqlDataReader rd = cmd.ExecuteReader())
                {
                    while (rd.Read())
                    {
                        customers.Add(new Customer
                        {
                            Id = rd["CustomerID"].ToString(),
                            Name = rd["ContactName"].ToString(),
                            Address = rd["Address"].ToString(),
                            Company = rd["CompanyName"].ToString(),
                            City = rd["City"].ToString(),
                            Country = rd["Country"].ToString(),
                            Phone = rd["Phone"].ToString()
                        });
                    }
                }
            }
            con.Close();
        }
        return Json(new { data = customers }, JsonRequestBehavior.AllowGet);
    }
}

View

<html>
<head>
    <title>Index</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap.min.css" />
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
    <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap.min.js"></script>
    <link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.2.3/css/responsive.bootstrap.min.css" />
    <script type="text/javascript" src="https://cdn.datatables.net/responsive/2.2.3/js/dataTables.responsive.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/responsive/2.2.3/js/responsive.bootstrap.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $(document).on("click", ".opencomment", function () {
                var mycomment = $(this).data('id');
                $(".modal-body #commentdesc").html(mycomment);
            });
            ApplyDataTable("", "");
        });

        function ApplyDataTable(country, city) {
            $.ajax({
                url: '/Home/LoadData',
                type: 'GET',
                dataType: 'json',
                data: { country: country, city: city },
                success: function (data) {
                    $('#FeedbackDetails').DataTable({
                        "bDestroy": true,
                        "aaData": data.data,
                        "lengthMenu": [[5, 10, 25, 50, 100, -1], [5, 10, 25, 50, 100, "All"]],
                        "autoWidth": true,
                        "responsive": true,
                        "lengthChange": true,
                        "ordering": true,
                        "fnRowCallback": function (nRow, aData, iDisplayIndex) {
                            var oSettings = this.fnSettings();
                            $("td:first", nRow).html(oSettings._iDisplayStart + iDisplayIndex + 1);
                            return nRow;
                        },
                        "columns": [{ "data": null, "autoWidth": true },
                                    { "data": "Name", "autoWidth": true },
                                    { "data": "Address", "autoWidth": true },
                                    { "data": "Company", "autoWidth": true },
                                    { "data": "City", "autoWidth": true },
                                    { "data": "Country", "autoWidth": true },
                                    { "data": "Phone", "autoWidth": true}],
                        columnDefs: [{
                            targets: 3,
                            render: function (data, type, row, meta) {
                                if (type === 'display' && data.length > 15) {
                                    return '<span title="' + data + '">' + data.substr(0, 8) + '...<a href="" data-id="' + data + '" data-toggle="modal" class="opencomment" data-target="#myModal">Show More</a>';
                                }
                                else { return data; }
                            }
                        }],
                        "language": { "emptyTable": "No Events Found Related To This User" }
                    })
                }
            });
        }

        function UserChanged() {
            var country = $('#ddlCountries option:selected').val();
            var city = $('#ddlCities option:selected').val();
            ApplyDataTable(country, city);
        }
    </script>
</head>
<body>
    <div>
        <table class="ui celled table">
            <tr>
                <td>Country</td>
                <td><%:@Html.DropDownList("ddlCountries", (IEnumerable<SelectListItem>)TempData["Country"], "--Select Country--", new { @class = "form-control",onchange = "UserChanged()" })%></td>
            </tr>
            <tr>
                <td>City</td>
                <td><%:@Html.DropDownList("ddlCities", (IEnumerable<SelectListItem>)TempData["City"], "--Select City--", new { @class = "form-control",onchange = "UserChanged()" })%></td>
            </tr>
        </table>
        <div class="container" style="margin-top: 10px">
            <table id="FeedbackDetails" class="ui celled table">
                <thead>
                    <tr>
                        <th>S.No</th>
                        <th>Name</th>
                        <th>Address</th>
                        <th>Company</th>
                        <th>City</th>
                        <th>Country</th>
                        <th>Phone</th>
                    </tr>
                </thead>
            </table>
        </div>
        <div id="myModal" class="modal fade" role="dialog">
            <div class="modal-dialog">
                <div class="modal-content">
                    <div class="modal-header">
                        <h4 class="modal-title">Feedback Comment</h4>
                    </div>
                    <div class="modal-body">
                        <p id="commentdesc"></p>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>

Screenshot