Paging not showing correct number of pages

bceder

I am doing a server side data fetch which is returning the correct number of rows and totalPages, however, the number of buttons in the pagination seems to be unlimited even though I have my pageLength set to 5 and I am getting back 12 records.

Is there something that I am missing here?

$(function () {
    $('#table').DataTable({
        bServerSide: true,
        bProcessing: true,
        bRetrieve: true,
        sPagingType: 'simple',
        pageLength: 5,
        sDom: 'Brtp',
        paging: true,
        bRetrieve: false,
        bSearching: false,
        bSearchable: false,
        bLengthChange: false,
        bSort: false,
        ajax: function (data, refreshDataTableCallback) {
            $.ajax({
                url: '/Search/Get/',
                type: 'POST',
                contentType: 'application/json',
                data: JSON.stringify(jsonObject),
                datatype: 'json',
                success: function (response) {
                    refreshDataTableCallback(response);
                }
            });
        },
        columns: [
            {
                data: null,
                render: function (full) {
                    return '<div class="item">' +full + '</div>';
                }
            }
        ]
    });
});

kthorngren

You have sPagingType: 'simple', which should only show Previous and Next buttons, like this example:
http://live.datatables.net/godicoxi/1/edit

the number of buttons in the pagination seems to be unlimited

I'm not sure what you mean by this. Can you post a screenshot? Or better provide a link to your page or a test case replicating the issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

bceder

kthorngren

I have my pageLength set to 5 and I am getting back 12 records.

Look at the JSON response to see how many records it is stating are available. What is the recordsTotal value? This doc explains the expected response parameters:
https://datatables.net/manual/server-side#Returned-data

You have sPagingType: 'simple', which is not a valid option. The current option is pagingType. You are trying to use the legacy version of the option which is sPaginationType not sPagingType. You can use the legacy versions but its recommended to use the current. This Conversion Guide provides a matrix you can use to convert between the two.

Kevin

bceder

data: (5) [{…}, {…}, {…}, {…}, {…}]
message: null
pageNumber: 1
totalAmount: 0
totalPages: 4
totalRows: 16
value: 0

I updated the options but I am getting the same results.

'dom': 'Brtp',
        'lengthChange': false,
        'ordering': false,
        'pageLength': 5,
        'paging': true,
        'pagingType': 'simple_numbers',
        'processing': true,
        'retrieve': true,
        'serverSide': true,
        'columnDefs': [
            { 'searchable': false }
        ]

kthorngren

The parameters that are being returned don't match the expected parameters for server side processing. Again refer to this doc:
https://datatables.net/manual/server-side#Returned-data

You could use the xhr to map the parameters returned to what Datatables expects.

Kevin

bceder

I tried each of your suggestions but the data being returned hasn't changed.

kthorngren

What are you using for your server script?

Did you try the xhr event to modify the parameters? Is so what does your code look like?

EDIT: Do you need to use server side processing? How many records to you expect?

Kevin

bceder

I do need to use server side processing since my records are coming from a database.

kthorngren

Actually you don't need to enable server side processing to use a database. Server side processing is a mode Datatables uses to help with large amounts of data. Its more complex and the expectation is the server script is responsible for sorting, searching, etc. You can use "client side processing" and still retrieve data via ajax. With client side processing the sorting and searching take place with the data in the client.

Please see this FAQ. Client side processing is the preferred method unless you have large amounts of data.

Comment out 'serverSide': true, and see what happens.

Kevin

bceder

Actually, the database is setup to handle all the data retrieval as far as number of records and sorting order. It is required.

kthorngren

What are you using for your server script? Is it something provided by Datatables?

Kevin

bceder

No, its something created by my DBA and it is being used in several other applications but, for whatever reason, the one that I am working on, the paging doesn't work correctly. I was able to handle removing the extra buttons with javascript.

kthorngren

Here is an example I created for another thread that shows using the xhr event to modify the parameters needed by Datatables to work properly with server side processing. It simply sets the records total to 300 but you could do something like json.recordsTotal = json.totalAmount. You just need to map your parameters to those used by Datatables.

http://live.datatables.net/koyalulo/1/edit

Kevin

bceder

Still no change.

kthorngren

Can you post a link to your page so we can look?

What does the xhr event look like?

have you tried using console log statements inside the xrh event to see what is happening?

Kevin

bceder

Unfortunately, the app site isn't publicly accessible yet as were still in development.

I added this line but the result is undefined.

        .on('xhr.dt', function (e, settings, json, xhr) {
            json.recordsTotal = 300;
            console.log(xhr);
        })

kthorngren

Try console.log(json);.

With json.recordsTotal = 300; does the Datatable show you have 300 records?

I added this line but the result is undefined.

Might be due to the way your are using the ajax function. I'll need to set it up to take a look to see how what you have behaves.

Kevin

bceder

recordsTotal: 300

If I change it to

json.recordsTotal = json.totalAmount;

recordsTotal: 0

kthorngren

data: (5) [{…}, {…}, {…}, {…}, {…}]
message: null
pageNumber: 1
totalAmount: 0
totalPages: 4
totalRows: 16
value: 0

recordsTotal: 0

That makes sense since the return value is 0. Maybe it should be totalRows.

Datatables expects to see the following parameters in the response:

data
recordsTotal
recordsFiltered
draw

It looks like you can map recordsTotal to totalRows. Not sure what totalAmount is. Doesn't look like you have a parameter that can be used for recordsFiltered. More importantly you need something to handle the draw parameter. This is a sequence number that Datatables uses to match request to response. See the doc I linked to for more details.

Can your server respond with something that can be used for recordsFiltered and draw?

Kevin

bceder

We added this to the end of the ajax call since it wasn't getting set in the initial data set:

e.iTotalRecords = e.totalRows;
e.iTotalDisplayRecords = e.totalRows;

All works as it should now.