pagination not working properly

pagination not working properly

maniyamaniya Posts: 49Questions: 11Answers: 0

Here i9s the code i am using for my server side pagination
My Stored procedure Call

```CREATE OR ALTER PROCEDURE [dbo].[users]
@SEARCH VARCHAR(100)='', -- Gloabl filter
@PageNumber INT,
@PageSize INT,
@SortOrder VARCHAR(10),
@SortColumn INT
AS

BEGIN
SET NOCOUNT ON
BEGIN TRY
DECLARE @RecordFrom INT;
SET @RecordFrom = (@PageNumber-1)*@PageSize;

    ;WITH CTE_Result
    (
        id,name,email,department 
    )
    AS
    (
        SELECT * 
            FROM dbo.users
        WHERE 1=1 
        AND (@SEARCH = '' OR (@SEARCH != '' AND (
                                name LIKE '%'+@SEARCH+'%'

                            )))
    ), CTE_Count AS (SELECT COUNT(id) AS TotalRecords FROM CTE_Result)

    SELECT *
        FROM CTE_Result,CTE_Count
        ORDER BY
            CASE WHEN  @SortColumn=1 AND @SortOrder='asc'  THEN id END ASC,


            CASE WHEN  @SortColumn=1 AND @SortOrder='desc'  THEN id END DESC,



        OFFSET @RecordFrom ROWS 
        FETCH NEXT @PageSize ROWS ONLY

END TRY
BEGIN CATCH
    THROW;
END CATCH
SET NOCOUNT OFF

END```

and now the Coldfusion Code i am using

```<cfparam name="section" DEFAULT="">
<cfparam name="draw" DEFAULT="1" TYPE="integer" />
<cfparam name="form.start" DEFAULT="1" TYPE="integer" />
<cfparam name="form.length" DEFAULT="10" TYPE="integer" />
<cfparam name="form.search" DEFAULT="" TYPE="string" />

<cfif form.SEARCH neq '' AND len(form["search[value]"]) gt 0>
<cfset form.SEARCH=form["search[value]"]>
</cfif>

<cfset form.searchColumn = structKeyExists(form,"order[0][column]") AND form["order[0][column]"] neq 0 ? form["order[0][column]"] : 1>
<cfset form.searchOrder = form["order[0][dir]"]>

<!--- Data set after filtering --->
<cfswitch expression="#section#">
<cfcase VALUE="users">
<cfset qFiltered = getUsers(form.SEARCH,1,LENGTH,form.searchColumn,form.searchOrder)>
</cfcase>
</cfswitch>
<!--- Total data set length --->
<cfset qCount = qFiltered.TotalRecords>

<cfset resData = []>
<cfset qFiltered.each(FUNCTION(ROW){
resData.append(ROW);
})>
<cfoutput>
{
"draw": #val(draw)#,
"recordsTotal": #recordsTotal#,
"recordsFiltered":#qFiltered.recordCount#,
"data": #serializeJSON(resData)#
}
</cfoutput>```

and here is my JS Code

$(".table").DataTable({ serverSide: TRUE, processing: TRUE, ajax: { url: "dtAjaxData.cfm?section=users", TYPE: "post" }, LANGUAGE: { infoEmpty: "No records available" }, pagingType: 'full_numbers' })

but it is only displaying only 10 records out of 7000 records, it does not show pagination at all.

is there anything wrong i am doing here

Sign In or Register to comment.