Last page pagination problem - if the data row count is less than the page length

marcin

Hello,

I've just started working on my first JQuery/DataTables and everything has been going smoothly so far until I've encountered the following issue with pagination on the last page of the data set. For example, if I have 57 records to be displayed, and page length is set to 10, when I try to go to the last page, the 7 records to be displayed on the last page are never shown and the "processing" message is displayed. As soon as I click the "previous" button, the previous 10 records are displayed successfully. Further, this issue never occurs on the last page whenever there's a number of records to be displayed there that is equal to the page length (if say I have 50 records to be displayed in total, and page length is set to 10)... I've tried debugging this problem using Developer Tools in Chrome, but I feel like I'm not getting anywhere with it and running out of ideas... It doesn't seem like it is a very trivial problem to solve (although I might be wrong, as JQuery/DataTables are all brand new worlds to me)... Perhaps others may see something that I cannot at this point...
Below is a copy of my test code code containing the table initialization... Any suggestions would be really appreciated...
[code]

$(document).ready(function() {
//var oTable = $('#displayData').dataTable({
$('#go').click( function(){
var oTable = $('#displayData').dataTable({
//"bJQueryUI": true,
//"iDisplayStart": 0,
//"bDeferRender": true, //defers rendering of records not displayed on the current on the page until needed (supposedly makes tables faster)
"bProcessing": true,
"bStateSave": true,
"bServerSide": true,
"bDestroy": true, //added so that the table gets re-created every time user tries to use the drop-down search
"sAjaxSource": "jquerytesthandler.cfm",
"aoColumns": [
{"sName": "Vendor_Number", "sTitle": "Vendor Number", "sWidth": "10%", "bSortable": "true"},
{"sName": "Vendor_Name", "sTitle": "Vendor Name", "sWidth": "20%", "bSortable": "true"},
{"sName": "Invoice_Number", "sTitle": "Invoice Number", "sWidth": "10%", "bSortable": "true"},
{"sName": "Invoice_Date", "sTitle": "Invoice Date", "sWidth": "10%", "bSortable": "true"},
{"sName": "Invoice_Amount", "sTitle": "Invoice Amount", "sWidth": "10%", "bSortable": "true"}
],
"sPaginationType": "full_numbers",
"aaSorting": [[1,'asc']],
"oLanguage": {
"sLengthMenu": "Page length: _MENU_",
//"sSearch": "Filter (NOTE: filter is case sensitive):",
"sZeroRecords": "No matching records found",
},
//"sDom": 'T<"clear">lfrtip', /* this part is for displaying the table tools */
"sDom": 'T<"clear">lrtip', //removed the filter by deleting 'f'
"oTableTools": {
"sSwfPath": "/jquery/DataTables-1.8.1/extras/TableTools/media/swf/copy_cvs_xls_pdf.swf"
},
"fnServerData": function ( sSource, aoData, fnCallback ) {
aoData.push(
{ "name": "query", "value": "accountPayableRecords" },
{ "name": "sql", "value": "SELECT [Vendor_Number], [Vendor_Name], [Invoice_Number], [Invoice_Date], [Invoice_Amount]" },
{ "name": "filterVal", "value": document.getElementById('filterVal').value },
{ "name": "filterOption", "value": document.getElementById('filterOption').options[document.getElementById('filterOption').selectedIndex].value }
);

$.ajax( {"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback} );
}
});
oTable.fnDraw(); //redraw the table and reset pagination when "Go" button is clicked
new FixedHeader(oTable); //fix the header so it is always visible when scrolling down the table
});
});



Accounts Payable Example (using Data Tables JQuery plugin)
This is the front end template for a data Tables example. It is handling the data(Json) from an AJAX post, and displaying it in a tabular view below.
All changes are made inline, so there are no refreshes.


Vendor NumberVendor NameInvoice NumberInvoice DateInvoice Amount



Vendor Number
Vendor Name
Invoice Number
Invoice Date
Invoice Amount




Loading data from server





[/code]

marcin

also here's the code for my ColdFusion test handler (It couldn't fit in my original post, due to the post size limitations....

[code]















<!--- this comes from the AJAX script in the template --->





<!--- strip off the comma if it is the last element --->

<!--- last char is a comma --->






obj = CreateObject("component","jquerytest");
accountPayableRecords = obj.createAccountPayableRecordsList(xml,variables.filterOption,variables.filterVal);





#preservesinglequotes(form.sql)#
FROM #form.query#

WHERE 1 = 1

AND (

#listGetAt(variables.fieldlist, variables.index,',')# LIKE '%#form.sSearch#%' OR

)



ORDER BY

#listGetAt(variables.fieldlist,form["iSortCol_#variables.i#"]+1)# #form["sSortDir_#variables.i#"]# ,




<!--- strip off the table name from the values, otherwise it will break making the json --->


<!--- create the JSON response --->
{
"sEcho": #form.sEcho#,
"iTotalRecords": #theRecordCount#,
"iTotalDisplayRecords": #rResult.recordcount#,
"aaData": [

[
<!--- custom translations --->
"#rResult[variables.i][rResult.currentRow]#"
<!--- , --->
,
]


<!--- , --->
,

,



]
}


#variables.sOutput#

[/code]

Stephan

Well, i don't have any knowledge about ColdFusion, but it seems, that you always iterate over a number of form.iDisplayLength rows. I guess this throws an error on the last page with less than form.iDisplayLength rows. You should do the limitation in the SQL Query and then iterate over the ResultSet with something like forEach. In coldfusion it might be cfloop without delimiter?!
This loop:
[code]
<!--- create the JSON response --->
{
"sEcho": #form.sEcho#,
"iTotalRecords": #theRecordCount#,
"iTotalDisplayRecords": #rResult.recordcount#,
"aaData": [

[
[/code]