c#ajaxasp.net-mvcdatatabledatatables
C# ASP.net MVC Ajax MySQL DATATABLES Column searching can't pass search value to Controller
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
Solution
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>
- Understanding the difference in timing of two functions that increment each element of an integer array
- Why MSVC generates warning C4127 when constant is used in "while" - C
- Executing a user-space function from the kernel space
- Why must the variable used to hold getchar's return value be declared as int?
- mpirun -np 4 ./a.out doesn't use all my cores (ubuntu 24.04LTS)
- Shadowing an iterator inside a for loop has undefined (?) behaviour in C
- Sleep for N seconds and wait for keypress
- Dereference twice in gdb
- Is this declaration UB?
- return type defaults to 'int' [-Wimplicit-int]
- What is the scope of `fesetround()`?
- Which specific optimization flag causes libm functions to be treated as pure?
- How to render text in SDL2?
- Why would you use 'extern "C++"'?
- Strange Behavior Compiler Ignoring NULL Check Unless I Print Something in the if Statement
- Fast inverse square root using fixed point instead of floating point
- What is the const qualifier attached to in C: the memory area or the pointer?
- GCC options for strictest C code?
- How to do an explicit fall-through in C
- How do compilers treat CONST qualifier when the pointer points to a memory location obtained with malloc()?
- C: cmocka headers - how to unittest?
- Why in C when I print a double with a one decimal it round it to the next number
- Android C to Java SWIG unable to compile: incompatible types: byte cannot be converted to SWIGTYPE_p_uint8_t
- GNU Make in Ubuntu giving fatal error: rpc/types.h: No such file or directory
- How can I exclude non-numeric keys? CS50 Caesar Pset2
- How change every struct in an array of pointers?
- Optimized 2x2 matrix multiplication: Slow assembly versus fast SIMD
- Simple frame by frame video decoder library
- GCC no longer implements <varargs.h>
- Contents of IO buffer unknown == unsafe?