C# ASP.net MVC Ajax MySQL DATATABLES Column searching can't pass search value to Controller

Question

Trying to Achieve

Using Datatable, I am trying to implement column searching. I wish to pass search[value] and column index to Controller where I have a filter method that will filter and load the list.

What I have tried

With the following code, I bind keyup change to each column. During keyup change, I managed to get valueData and column index from the table

    table.columns().eq(0).each(function (colIdx) {

        $('input', $('.filters td')[colIdx]).bind('keyup change', function () {
            var searchColIndex = $(this).parent().index();
            //=== During keyup or change, it will go through each column
            var columnIndex = colIdx;

            //=== When the searchCol matches the columnIndex
            if (searchColIndex == columnIndex) {
                //=== Ideally will call Draw and pass the value to Controller
                var valuedata = $(this).val();
                //table.column(0).search($(this).val()).draw();
                table
                 .column(colIdx)
                 .search(valuedata)
                 .draw();
            }

        });
    });

Controllers could not manage to get the search value or column index

But if I search at the top right hand corner search box, Controllers able to request the value

Code https://github.com/BROMVC5/BROSTANDARD.git

Suggested by Murat

    var table = $('#tablePassword').DataTable({

        "paging": true,
        "ordering": true,
        "processing": true, // control the processing indicator.
        "serverSide": true, // recommended to use serverSide when data is more than 10000 rows for performance reasons
        "info": true,   // control table information display field
        "lengthChange": true,
        "lengthMenu": [[5, 20, 50, -1], [5, 20, 50, "All"]],    // use the first inner array as the page length values and the second inner array as the displayed options
        "colReorder": true,
        "orderMulti": true, // for disable multiple column at once

        "language": {
            searchPlaceholder: "Search records"
        },

        "order": [["3", "desc"]], //=== Not working during stateSave

        //"ajax":{
        //    "url": "/Password/AjaxGetJsonData",
        //    "type": "POST"
        //},
        "AjaxSource": "/Password/AjaxGetJsonData",
        "fnServerData": function (sSource, aoData, fnCallback) {
            aoData.push({ "name": "all", "value": true });
            $.getJSON(sSource, aoData, function (json) {
                fnCallback(json)
            });
        },

        //*** Added the following code ****
        "columnDefs": [
            {
                "width": "5%", "targets": 'NumCol', "data": "id",
                "orderable": false,
                "render": function (data, type, row, meta) {
                    return meta.row + meta.settings._iDisplayStart + 1;
                }
            },
            {
                "targets": 'LoginCol', 
                "data": "LoginID", "orderable": true
            },
            {
                "targets": 'NameCol', 
                "data": "Name", "orderable": true
            },
            {
                "targets": 'DtCreatedCol', 
                "data": "DateCreated", "orderable": true
            },
            {
                "targets": 'EditCol', // The third column
                "className": "text-center",
                "orderable": false,
                "render": function (data, type, full, meta) {
                    return '<a href="/Password/PasswordDet/' + full.AutoINC + '"><img src="../../Content/myPics/edit-2-24.png" ></a>';
                }
            }
        ],

   });


   $('body').on('click', '#btnGet', function () {

        //to get currently clicked row object
        var row = $(this).parents('tr')[0];

        //to get currently clicked row data
        var rowData = table.row(row).data();

        //to get currently clicked row id (first column value (at index [0])
        var rowId = rowData[0];

        var row = $(this).closest("tr"),            // Finds the closest row <tr>
        rowId = row.find("td:nth-child(1)").text();
    });

Suggested by Murat Controller is not called and hang

are you getting any data in the Request.QueryString? and what HttpVerb are you using on AjaxGetJsonData Get or Post? — vikscool
– vikscool, Commented May 2, 2019 at 10:03
@vikscool I am using Get I have posted the Ajax code and additional stuff. — Hanz Cheah
– Hanz Cheah, Commented May 3, 2019 at 1:50
The reason why you are getting blank/empty string for search[value] is you have enabled "filter": true which enabled a search box on the top right corner of your table. And you are trying to call the search from the individual search boxes for each column but the value that you are reading is from the main Search box. So, change the read logic to be something like: columns[index][search][value] to make the search work. — vikscool
– vikscool, Commented May 3, 2019 at 4:31

Murat Yıldız · Accepted Answer · 2023-02-07 20:58:37Z

Try this:

var table;
$(document).ready(function () {
    table = $('#tablePassword).DataTable();
});

$('body').on('click', '#btnGet', function () {
    
    //to get currently clicked row object
    var row = $(this).parents('tr')[0];
 
    //to get currently clicked row data
    var rowData = table.row(row).data();
 
    //to get currently clicked row id (first column value (at index [0])
    var rowId = rowData[0];
});

In order to post extra parameters in jQuery DataTables, use fnServerData method besides ajaxSource as shown below (do not forget to use the same parameter names on the Controller method):

// code omitted for brevity

"serverSide": true,
"ajaxSource": "/Password/AjaxGetJsonData", 
 
//fnServerData used to inject the parameters into the AJAX call sent to the server-side
"fnServerData": function (sSource, aoData, fnCallback) {
    aoData.push({ "name": "all", "value": true });
    $.getJSON(sSource, aoData, function (json) {
        fnCallback(json)
    });
},

Update: Please do not forget to use the same parameter names on the Controller method. You can also use the following approach in order to get row values from DataTable:

var row = $(this).closest("tr"),            // Finds the closest row <tr>
rowId = row.find("td:nth-child(1)").text(); // Finds the 1st <td> element (Id)

Here is the Controller and Model parts for this solution:

Controller:

/// <summary>
/// Returns data by the criterion
/// </summary>
/// <param name="param">Request sent by DataTables plugin</param>
/// <returns>JSON text used to display data
/// <list type="">
/// <item>sEcho - same value as in the input parameter</item>
/// <item>iTotalRecords - Total number of unfiltered data. This value is used in the message: 
/// "Showing *start* to *end* of *iTotalDisplayRecords* entries (filtered from *iTotalDisplayRecords* total entries)
/// </item>
/// <item>iTotalDisplayRecords - Total number of filtered data. This value is used in the message: 
/// "Showing *start* to *end* of *iTotalDisplayRecords* entries (filtered from *iTotalDisplayRecords* total entries)
/// </item>
/// <item>aoData - Twodimensional array of values that will be displayed in table. 
/// Number of columns must match the number of columns in table and number of rows is equal to the number of records that should be displayed in the table</item>
/// </list>
/// </returns>
[Authorize(Roles = "CanViewLab")]
public ActionResult GetLabs(JQueryDataTableParamModel param, bool isAll)
{          
    var allRecords = repository.Labs; //All records
    if (!isAll)
    {
        allRecords = allRecords.Where(m => m.StatusId == Status.Active); //Only active records
    };
    IEnumerable<Lab> filteredRecords;

    //Check whether the users should be filtered by keyword
    if (!string.IsNullOrEmpty(param.sSearch))
    {
        //Used if particulare columns are filtered 
        var nameFilter = Convert.ToString(Request["sSearch_1"]);
        var placeFilter = Convert.ToString(Request["sSearch_2"]);

        //Optionally check whether the columns are searchable at all 
        var isNameSearchable = Convert.ToBoolean(Request["bSearchable_1"]);
        var isPlaceSearchable = Convert.ToBoolean(Request["bSearchable_2"]);

        //filteredRecords = repository.Labs
        filteredRecords = allRecords //For including "isAll" parameter to the "filteredRecords" as "allRecords" parameter
                .Where(c => isNameSearchable && c.Name.ToLower().Contains(param.sSearch.ToLower())
                            ||
                            isPlaceSearchable && c.Place.ToLower().Contains(param.sSearch.ToLower()));
    }
    else
    {
        filteredRecords = allRecords;
    }

    //!!! The number of request variables (bSortable_X) is equal to the iColumns variable.
    var isNameSortable = Convert.ToBoolean(Request["bSortable_1"]);
    var isCodeSortable = Convert.ToBoolean(Request["bSortable_2"]);
    var isStatusNameSortable = Convert.ToBoolean(Request["bSortable_3"]);
    var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);

    Func<Lab, string> orderingFunction = (c => sortColumnIndex == 1 && isNameSortable ? c.Name :
                                                    sortColumnIndex == 2 && isCodeSortable ? c.Place :
                                                    sortColumnIndex == 3 && isStatusNameSortable ? c.StatusName :
                                                "");

    var sortDirection = Request["sSortDir_0"]; // asc or desc
    if (sortDirection == "asc")
        filteredRecords = filteredRecords.OrderBy(orderingFunction);
    else
        filteredRecords = filteredRecords.OrderByDescending(orderingFunction);

    var displayedRecords = filteredRecords.Skip(param.iDisplayStart).Take(param.iDisplayLength);
    var result = from c in displayedRecords select new[] { Convert.ToString(c.Id), c.Name, c.Place, c.StatusName };
    return Json(new
    {
        sEcho = param.sEcho,
        iTotalRecords = allRecords.Count(),
        iTotalDisplayRecords = filteredRecords.Count(),
        aaData = result
    },
    JsonRequestBehavior.AllowGet);
}

Model:

/// <summary>
/// Class that encapsulates most common parameters sent by DataTables plugin
/// </summary>
public class JQueryDataTableParamModel
{
    /// <summary>
    /// Request sequence number sent by DataTable, same value must be returned in response
    /// </summary>       
    public string sEcho{ get; set; }

    /// <summary>
    /// Text used for filtering
    /// </summary>
    public string sSearch{ get; set; }

    /// <summary>
    /// Number of records that should be shown in table
    /// </summary>
    public int iDisplayLength{ get; set; }

    /// <summary>
    /// First record that should be shown(used for paging)
    /// </summary>
    public int iDisplayStart{ get; set; }

    /// <summary>
    /// Number of columns in table
    /// </summary>
    public int iColumns{ get; set; }

    /// <summary>
    /// Number of columns that are used in sorting
    /// </summary>
    public int iSortingCols{ get; set; }

    /// <summary>
    /// Comma separated list of column names
    /// </summary>
    public string sColumns{ get; set; }
}

And here is how I retrieve and list the data:

View:

<div class="portlet-body form">
    <div class="form-body">
        <table id="dtbLab" class="table table-striped table-bordered table-hover table-checkable order-column">
            <thead>
                <tr>
                    <th> Id </th>
                    <th> Name </th>                             
                    <th> Place </th>
                    <th> Status </th>
                    <!-- (!!! "columnDefs" width SHOULD BE set up in <th> section of column header) -->
                </tr>
            </thead>
            <tbody></tbody>
        </table>
    </div>
</div>
        
<script>
var table;
$(document).ready(function () {
    table = $('#dtbLab')
        .DataTable({                
            tableTools: {
            "sRowSelect": "single",
            "aButtons": []
            },
            buttons: [],
        "autoWidth": false,
        "serverSide": true,
        "ajaxSource": "/Lab/GetLabs",
        "fnServerData": function (sSource, aoData, fnCallback) {
            aoData.push({ "name": "isAll", "value": isAllChecked }); // Add some extra data to the sender
            $.getJSON(sSource, aoData, function (json) {
                /* Do whatever additional processing you want on the callback, then tell DataTables */
                fnCallback(json);
            });
        },
        "processing": false, //Enable or disable the display of a 'processing' indicator when the table is being processed (e.g. a sort)
        "columns": [
                        {
                            "name": "Id",
                            "width": '5%', /* '5px' */
                            "searchable": false,
                            "sortable": false,
                            "fnRender": function (oObj) {
                                return '<a href=\"Controller/Action/' + oObj.aData[0] + '\">View</a>';
                            }
                        },
                        { "name": "Name", "width": '45%' },
                        { "name": "Code", "width": '45%' },
                        { "name": "Status", "width": '10%' }
        ]       

    });
});
</script>

Yildiz, thanks for your suggested answer but I think it is not what I want. I can't seem to load my list when I use fnServerData Moreover, how do I pass column index and their respective search[value]? Please elaborate if you can
I tried your code, but can you elaborate more about the Controllers When initial loading the page with fnServerData I am unable to load my list into the `Datatables
First of all, thanks for your help but I actually haven't found a solution. The problem is when I follow your suggestions. The page will not load as stated above. If you go to GitHub and follow my code. I am using a CreateData method which I will call a MySQL query and populate into List<DataItem> For your example, I am not sure how you populate the DB. By the way when changing to POST, the original Search at the left hand corner and Sort by 1 column won't work. Your help is gladly appreciated.
Does the request hits to the Controller method when you debug your code? If so where it ends and what is the error? On the other hand I think you comment out [Authorize(Roles = "")] line that may cause the problem.

Collectives™ on Stack Overflow

C# ASP.net MVC Ajax MySQL DATATABLES Column searching can't pass search value to Controller

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related