Why is the datatable search label not working?
Why is the datatable search label not working?
Lorenzo00
Posts: 12Questions: 2Answers: 0
Link to test case: No link, because it only works locally
Error messages shown: No Error
Description of problem: The problem is that the datatable's search label does not filter the results of the query and the number of records is not shown in the lower left corner.
My frontend ASPX:
var pageUrl = '<%=ResolveUrl("~/Sph_table_00.aspx/GetDataForDataTable")%>';
var dataTable;
dataTable = $('#example').DataTable({
"processing": true,
"serverSide": true,
"searching": true,
"ordering": false,
"scroller": true,
"info": true,
"paging": true,
"scrollX": true,
"scrollY": 500,
"pageLength": 10,
"lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
"ajax": {
"url": pageUrl,
"type": "POST",
"contentType": "application/json; charset=utf-8",
"dataType": "json",
"data": function (d) {
return JSON.stringify(d);
},
"dataSrc": "d.data"
},
"columns": [
{ data: 'cododl', name: 'CODODL' },
{ data: 'flagaperto', name: 'FLAGAPERTO' },
{ data: 'datainizio', name: 'DATAINIZIO' },
{ data: 'orainizio', name: 'ORAINIZIO' },
{ data: 'progressivo', name: 'PROGRESSIVO' },
{ data: 'datafine', name: 'DATAFINE' },
{ data: 'orafine', name: 'ORAFINE' },
{ data: 'durata', name: 'DURATA' },
{ data: 'codcdl', name: 'CODCDL' },
{ data: 'turno', name: 'TURNO' }
]
});
My Backend C#:
[WebMethod]
public static object GetDataForDataTable(int draw, int start, int length)
{
OleDbConnection OLEConnectionSPHERA = new OleDbConnection();
//Open connection
OLEConnectionSPHERA = Common.openConnOLEADODB(OLEConnectionSPHERA);
//Get recordsTotal
string countQuery = "SELECT COUNT(*) FROM S_ODL";
int recordsTotal = Common.executeScalar(countQuery, OLEConnectionSPHERA);
//SQL Query
string s_odlQuery = $"SELECT CODODL, FLAGAPERTO, DATAINIZIO, ORAINIZIO, PROGRESSIVO, DATAFINE, ORAFINE, DURATA, CODCDL, TURNO FROM S_ODL ORDER BY IDREC OFFSET {start} ROWS FETCH NEXT {length} ROWS ONLY";
OleDbDataReader s_odlRD = Common.executeQuery(s_odlQuery, parameters, OLEConnectionSPHERA);
List<Oggetto> dati = new List<Oggetto>();
//Save the data
while (s_odlRD.Read())
{
try
{
Oggetto obj = new Oggetto();
obj.cododl = s_odlRD["CODODL"].ToString();
obj.flagaperto = s_odlRD["FLAGAPERTO"].ToString();
obj.datainizio = s_odlRD["DATAINIZIO"].ToString();
obj.orainizio = s_odlRD["ORAINIZIO"].ToString();
obj.progressivo = s_odlRD["PROGRESSIVO"].ToString();
obj.datafine = s_odlRD["DATAFINE"].ToString();
obj.orafine = s_odlRD["ORAFINE"].ToString();
obj.durata = s_odlRD["DURATA"].ToString();
obj.codcdl = s_odlRD["CODCDL"].ToString();
obj.turno = s_odlRD["TURNO"].ToString();
dati.Add(obj);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
}
//Close connection
Common.closeConnOLEADODB(OLEConnectionSPHERA);
//Fill jsonData object to render DataTable.
var jsonData = new
{
draw = draw,
recordsTotal = recordsTotal,
recordsFiltered = recordsTotal,
data = dati
};
return jsonData;
}
Edited by Kevin: Syntax highlighting. Details on how to highlight code using markdown can be found in this guide
This question has an accepted answers - jump to answer
Answers
You have server-side processing enabled. That means the search must be done by your server-side script. It sounds like it isn't.
The info won't be updating because the JSON return from the server is not including what DataTables expects.
Allan
@allan Thank you for your response. I checked the answer but I seem to get from backend everything DataTable needs (draw, recordsTotal, recordsFiltered, data).
This is how the jsonData object is seen in debug by Visual Studio before return:
{ draw = 1, recordsTotal = 1048793, recordsFiltered = 1048793, data = Count = 10 }
The data I would say are there.
That doesn't look like properly formatted JSON data. Use the browser's network inspector to see what is returned. It should look something like this:
Kevin
@kthorngren
It looks okay to me, doesn't it?
Nope
You have a
dobject containing the data for Datatables. I see you have"dataSrc": "d.data"which isn't working. You could try"dataSrc": "d". If that doesn't work try usingajax.dataSrcas a function to return the contents of thedobject. Something like this:Kevin
@kthorngren I tried to edit the code and now it doesn't even show me the data
and I tried in debug from inspector tool to view data.d looks correct to me.
I did a little experiment with
ajax.dataSrcin this test case:https://live.datatables.net/qoliyehi/98/edit
It looks like you can point to where the row data is but it expects the
drawand other parameters to be at the root of the returned JSON. Also thexhrevent executes beforedataSrc. I think you will need to manipulate the JSON inxhrto move thedraw,recordsTotalandrecordsFilteredto the root of the object. Maybe something like this:And still use
"dataSrc": "d.data".Kevin
@kthorngren thanks, ok now it works. i just need to get the search and table sorting working which doesn't seem to be going, since you were quick to respond, would you give me one last help to solve these problems? I post below my modified ajax call.
What code are you using on the server side script to do the search and sort?
Allan
@allan above I posted my backend C#, once the data is received, shouldn't the search and sort functionality do it datatable automatically?
Sorry yes, I see it above now.
No - not if you've enabled server-side processing. That tells DataTables that the server-side will do the sorting and filtering. From the manual section on these two processing modes:
So the next question is - do you need server-side processing (there is discussion on that in the following paragraph in the documentation - it basically comes down to how many rows you have)? If you don't then just disable it and let DataTables to the search and sort.
However, it looks like you probably do need it since you have more than a million rows. So, you'll need to implement search and sort into your SQL statements, or use our Editor .NET libraries which provide support for that. See this post for details.
Allan
Ok Thanks @allan .