server side pagination doesn't seem to be working c# asp.net jquery datatables

pv619

Hi all & Allan,

I am trying to get the pagination to work but it's not working. I have over 90k records in the database but it only displays 50 records as per in the screen shot below:

http://s28.postimg.org/4qznktj59/yig_B7.png

I am returning the below JSON:

return Json(new
        {
            sEcho = param.sEcho,
            iTotalRecords = tracks.Count(),
            iTotalDisplayRecords = filteredTracks.Count(),
            `aaData` = results
        },
            JsonRequestBehavior.AllowGet);
    }

This is the Pagination Controller code:

var displayedTracks = filteredTracks.Skip(param.iDisplayStart).Take(param.iDisplayLength);

Here's the View code of the dataTable:

var oTable = $('#myDataTable').hide(50).fadeIn(10).dataTable({
        "oLanguage": {
            "sSearch": " "
        },
        "bAutoWidth": false,
        "sAjaxSource": "Ajax_DB",
        "deferRender": false,
        "bServerSide": true,
        "bProcessing": true,
        "sScrollY": "333",
        "iDisplayLength": 50,
        "bPaginate": true,
        "bRetrieve": true,
        "bDestroy": true,
        "sPaginationType": "full_numbers",
        "aoColumns": [
    { "mDataProp_": "TrackID", "sWidth": "1%" },
    { "mDataProp_": "AddedDate", "bSortable": false, "sWidth": "1%" },
    { "mDataProp_": "TrackName", "sWidth": "43%" },
    { "mDataProp_": "ArtistName", "sWidth": "30%" },
    { "mDataProp_": "ArtistName", "sWidth": "30%" }
        ]
    });

I don't know what I am doing wrong here, the pagination doesn't seem to work. Could someone please help me on how I can achive this? Thanks in advance.

wjhumphreys

Which version of DataTables are you using?

wjhumphreys

Its hard to say without seeing all your code and what you're trying to do. If I was to take a guess then id say try replacing

iTotalDisplayRecords = filteredTracks.Count(),

with

iTotalDisplayRecords = tracks.Count(),

I would also recommend if this is a new project with the latest DataTables version that you use the latest syntax as it makes the examples easier to follow.

pv619

Hi thanks for your help.. I am using DataTables version 1.9.4 and I have to use this version as it is compatible with the columnFilter plugin. I tried Datatables 1.10 but it's not compatible with the columnFilter plugin.

I have to use filteredTracks.Count() for it to count when the user filters the records. Please let me know if you want me to post my full Controller code. Thanks again :)

NBork

Did you ever solve this issue? I have nearly the identical code and have confirmed that the page event returns data from the server but for whatever reason the DataTable never updates/refreshes it's view, it keeps the rows from Page 1 and keeps the pager control at the bottom as page "1".

allan

@NBork - Can you link to the page so we can take a look please? The most common issue here is that the draw parameter is not being updated (manual).

Allan

NBork

@allan The manual is for 1.10 not 1.9. I assume that "draw" is the same as "sEcho" from 1.9. If so, that was part of the problem and adding "sEcho" and outputting the same value fixed part of the issue.

The other part of the issue was that the original author of the Bootstrap pagination for the template I'm working with (I don't know who initially wrote it) has a bug in their fnUpdate method.

Original code:

$('li:gt(1)', an[i]).filter(':not(.next)').remove();

Fixed code:

$('li:gt(0)', an[i]).filter(':not(.next)').remove();

The plugin in question always puts Prev / Next buttons so index 0 is Prev and index 1 is the first page. The code was always starting AFTER the button for page 1 and removing all buttons that were not the "Next" button.

Better code for this is actually:

$('li', an[i]).filter(':not(.next,.prev)').remove();

I just discovered a similar issue with paging doing the same thing and I need to track that down.

allan

If you want to use the legacy software, the manual for it is still available on the legacy site.

Allan