Issue with pagination using sql server and JQgrid,
I have he following code where from the query i am trying to get the first 500 records and then next 500 and so on
Here is my code, i am stuck as to what and where i should make the change to make it work
but it is query only 500 rows and that's it, no more pagination happening in the jqgrid
Here is my code, i am stuck as to what and where i should make the change to make it work
<cffunction name="queryConvertForJQGRID" access="package" returntype="struct" output="no">
<cfargument name="q" type="query" required="yes">
<cfargument name="page" type="numeric" required="no" default="1">
<cfargument name="rows" type="numeric" required="no" default="10">
<cfset var result = structnew()>
<cfset var rowStruct = structnew()>
<cfset var col = "">
<cfset result["page"] = arguments.page>
<cfset result["total"] = ceiling(arguments.q.recordcount/arguments.rows)>
<cfset result["records"] = arguments.q.recordcount>
<cfset result["rows"] = arraynew(1)>
<cfoutput query="arguments.q" startrow="#(arguments.page-1)*arguments.rows+1#" maxrows="#arguments.rows#">
<cfset rowStruct = structnew()>
<cfset rowStruct['viewed'] = viewed>
<cfset rowStruct['id'] = q['id'][currentrow]>
<cfset rowStruct['status'] = q['status'][currentrow]>
<cfset rowStruct['template'] = q['template'][currentrow]>
<cfset rowStruct['line'] = q['line'][currentrow]>
<cfset rowStruct['error_message'] = q['error_message'][currentrow]>
<cfset rowStruct['error_type'] = q['error_type'][currentrow]>
<cfset rowStruct['datein'] = q['datein'][currentrow]>
<cfset arrayappend(result.rows, rowStruct)>
</cfoutput>
<cfreturn result />
</cffunction>
<cffunction name="getgrouped" returntype="any" access="remote" output="false">
<cfargument name="table" required="true" default="errors">
<cfargument name="usedsn" default="" required="true">
<cfargument name="callback" default="" required="false">
<cfargument name="page" required="no" default="1">
<cfargument name="rows" required="no" default="500">
<cfargument name="sidx" required="no" default="">
<cfargument name="sord" required="no" default="ASC">
<cfargument name="acelem" required="no" type="any">
<cfset var dsn = dsnSettings(arguments.usedsn)>
<cfset var groupedget = "">
<cfset var result = structnew()>
<cfset var rowStruct = structnew()>
<cfset var col = "">
<cfset result["rows"] = arraynew(1)>
<cfquery name="groupedget" datasource="#dsn#">
WITH List AS
(SELECT row_number() over(ORDER BY mi.id) AS rowNum ,
viewed,
status,
mi.errorid,
FORMAT(datein, 'MMMM d, yyyy h:mm:ss tt PST', 'en-US') AS datein,
mi.error_message,
upper(mi.error_type) AS error_type,
mi.line,
mi.template,
mi.id
FROM #arguments.table# mi),
ListRecordCount AS
(SELECT *
FROM List ,
(SELECT MAX(rowNum) AS recordCount
FROM List) AS recordCount)
SELECT *
FROM ListRecordCount
WHERE rowNum BETWEEN #page# AND #rows#
</cfquery>
<cfset q = groupedget>
<cfreturn queryConvertForJQGRID(q, arguments.page, arguments.rows)>
</cffunction>but it is query only 500 rows and that's it, no more pagination happening in the jqgrid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you try what I had proposed? It could help readers to know, either way.
Also, had you tried to work with CF's built-in queryconvertforgrid function? and are you 100% sure you need to use this old cflib one (from cf11) instead? (And if the answer is yes, have you confirmed that the code you're running, in that function, is identical to the latest offered on the cflib site for that function, https://cflib.org/udf/queryConvertForjqGrid? I had only a couple of minutes to answer this so I have not checked.)