Why is the datatable search label not working?

Lorenzo00

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

allan

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

Lorenzo00

@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.

kthorngren

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:

{
  "draw": 1,
  "recordsTotal": 57,
  "recordsFiltered": 57,
  "data": [...]
}

Kevin

Lorenzo00

@kthorngren

It looks okay to me, doesn't it?

kthorngren

Nope You have a d object 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 using ajax.dataSrc as a function to return the contents of the d object. Something like this:

    "dataSrc": function ( json ) {
       return json.d;
    }

Kevin

Lorenzo00

@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.

kthorngren

I did a little experiment with ajax.dataSrc in 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 draw and other parameters to be at the root of the returned JSON. Also the xhr event executes before dataSrc. I think you will need to manipulate the JSON in xhr to move the draw, recordsTotal and recordsFiltered to the root of the object. Maybe something like this:

$('#example')
    .on('xhr.dt', function ( e, settings, json, xhr ) {
        json.recordsTotal = json.d.recordsTotal;
        json.recordsFiltered = json.d.recordsFiltered;
        json.draw= json.d.draw;
    } )

And still use "dataSrc": "d.data".

Kevin

Lorenzo00

@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.

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": function (data) {
            return data.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' }
    ]
}).on('xhr.dt', function (e, settings, json, xhr) {
    json.recordsTotal = json.d.recordsTotal;
    json.recordsFiltered = json.d.recordsFiltered;
    json.draw = json.d.draw;
    json.data = json.d.data;
});

allan

What code are you using on the server side script to do the search and sort?

Allan

Lorenzo00

@allan above I posted my backend C#, once the data is received, shouldn't the search and sort functionality do it datatable automatically?

allan

Sorry yes, I see it above now.

once the data is received, shouldn't the search and sort functionality do it datatable automatically?

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:

• Client-side processing - the full data set is loaded up-front and data processing is done in the browser.
• Server-side processing - an Ajax request is made for every table redraw, with only the data required for each display returned. The data processing is performed on the server.

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

Lorenzo00

Ok Thanks @allan .