Getting Error when using Paging Generator

miu · October 4, 2024, 3:54pm

Hello All,

While using Paging Generator, I am getting error when trying to check records on Page 2.

The Page 1 records equaling to 25 are correctly displayed.

However, when I try to click the Page 2 button, it does not show any data. The query also returns below error:

{
    "status": "500",
    "code": "EREQUEST",
    "message": "select [appNo], [appID] from [dbo].[applications] as [apps] where [apps].[appPhaseID] = @p0 and [apps].[statusID] = @p1 offset @p2 rows fetch next @p3 rows only - Invalid usage of the option next in the FETCH statement.",
    "stack": "RequestError: select [appNo], [appID] from [dbo].[applications] as [apps] where [apps].[appPhaseID] = @p0 and [apps].[statusID] = @p1 offset @p2 rows fetch next @p3 rows only - Invalid usage of the option next in the FETCH statement.\n    at Parser.<anonymous>

Requesting your help in getting this resolved.

brad · October 4, 2024, 5:24pm

Hi AJ, what are you using for a source?

Screenshot 2024-10-04 at 10.22.42 AM

Can you post your code for your paging buttons here?

miu · October 5, 2024, 4:19am

Hello Brad,

Here is the code for SC where i pass query manager 'offset' variable. The SC is a very simple query which returns application ID & application No. There are 29 records in the DB.

<dmx-serverconnect id="serverconnect1" url="/api/uniAdmin/manageApps/list_allApps_copy" dmx-param:offset="query1.offset"></dmx-serverconnect>

Here is the code for the paging buttons. For the page source, i have used the same SC which i have use to generate the table, as per the documentation.

<ul class="pagination" dmx-populate="serverconnect1.data.query_allApps" dmx-state="query1" dmx-offset="offset" dmx-generator="bs5paging">
                                <li class="page-item" dmx-class:disabled="serverconnect1.data.query_allApps.page.current == 1" aria-label="First">
                                    <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',serverconnect1.data.query_allApps.page.offset.first)"><span aria-hidden="true">&lsaquo;&lsaquo;</span></a>
                                </li>
                                <li class="page-item" dmx-class:disabled="serverconnect1.data.query_allApps.page.current == 1" aria-label="Previous">
                                    <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',serverconnect1.data.query_allApps.page.offset.prev)"><span aria-hidden="true">&lsaquo;</span></a>
                                </li>
                                <li class="page-item" dmx-class:active="title == serverconnect1.data.query_allApps.page.current" dmx-class:disabled="!active" dmx-repeat="serverconnect1.data.query_allApps.getServerConnectPagination(2,1,'...')">
                                    <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',(page-1)*serverconnect1.data.query_allApps.limit)">{{title}}</a>
                                </li>
                                <li class="page-item" dmx-class:disabled="serverconnect1.data.query_allApps.page.current ==  serverconnect1.data.query_allApps.page.total" aria-label="Next">
                                    <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',serverconnect1.data.query_allApps.page.offset.next)"><span aria-hidden="true">&rsaquo;</span></a>
                                </li>
                                <li class="page-item" dmx-class:disabled="serverconnect1.data.query_allApps.page.current ==  serverconnect1.data.query_allApps.page.total" aria-label="Last">
                                    <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',serverconnect1.data.query_allApps.page.offset.last)"><span aria-hidden="true">&rsaquo;&rsaquo;</span></a>
                                </li>
                            </ul>

brad · October 5, 2024, 4:34am

Is your query a paged query? I have hundreds of queries with pagination and have never seen the code above ever in my server connect.

miu · October 5, 2024, 4:51am

Yes, it's a paged query. I don't know why it looks that way, but, if I bind it with the query manager it updates the same way.

Actually, it is working now.
What i did was to:

Create a Dynamic Sortable Table which I didn't create previously. I just created a paging generator without passing the 'sort' & 'dir' variables.
While binding the Query Manager (with id=query1) variables: dir, sort, offset it default updates as

dmx-param:offset="query1.offset" 
dmx-param:sort="query1.sort"
dmx-param:dir="query1.dir"

which i changed to

dmx-param:offset="query.offset" 
dmx-param:sort="query.sort"
dmx-param:dir="query.dir"

I think that solved the issue.

Thanks for your help @brad

miu · October 5, 2024, 5:38am

Hello @brad
Sorry for bothering you again.
The joy of seeing it work was short lived.

The paging generator is working only when I first sort the table. If I click the page buttons without having sorted the table, it does not show the next page.

Do you any idea as to what the reason might be. Is it a bug or there is something wrong in the code.

franse · October 5, 2024, 12:47pm

On your server connect database paged query, do you have a defined limit value?

miu · October 5, 2024, 12:58pm

No, the limit input parameter is blank.

franse · October 5, 2024, 1:03pm

Actually I was asking about the API, what do you have there?

miu · October 5, 2024, 1:10pm

No, even in the API, i have not used the limit value.

franse · October 5, 2024, 1:17pm

But it's {{$_GET.limit}}?

Are you following this?

miu · October 5, 2024, 1:25pm

I am following the same tutorial for Paging Generator.
Even in the tutorial, the 'limit' parameter is kept blank which I have also done. Only 'offset' variable from query manager is assigned to the Input Parameter.

Is it necessary to use the {{$_GET.limit}} while creating the API?
Sorry, i am a bit confused about this.

franse · October 5, 2024, 1:50pm

I don't really know, but when you create a database paged query you have those values there, so maybe it worth the try:

I just created a new page with database paged query and it's working as expected..

<!-- Wappler include head-page="layouts/main" fontawesome_5="cdn" bootstrap5="local" is="dmx-app" id="pagedquery" appConnect="local" components="{dmxStateManagement:{},dmxBootstrap5PagingGenerator:{},dmxBootstrap5TableGenerator:{}}" -->
<meta name="ac:route" content="/wapplercommunity/pagedquery">
<dmx-query-manager id="query1"></dmx-query-manager>
<dmx-serverconnect id="serverconnect1" url="/api/wapplercommunity/pagedquery" dmx-param:offset="content.query1.data.offset"></dmx-serverconnect>


<table class="table">
    <thead>
        <tr>
            <th>Id online</th>
            <th>Userid online</th>
        </tr>
    </thead>
    <tbody is="dmx-repeat" dmx-generator="bs5table" dmx-bind:repeat="serverconnect1.data.query.data" id="tableRepeat1">
        <tr>
            <td dmx-text="id_online"></td>
            <td dmx-text="userid_online"></td>
        </tr>
    </tbody>
</table>
<ul class="pagination" dmx-populate="serverconnect1.data.query" dmx-state="query1" dmx-offset="offset" dmx-generator="bs5paging">
    <li class="page-item" dmx-class:disabled="serverconnect1.data.query.page.current == 1" aria-label="First">
        <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',serverconnect1.data.query.page.offset.first)"><span aria-hidden="true">&lsaquo;&lsaquo;</span></a>
    </li>
    <li class="page-item" dmx-class:disabled="serverconnect1.data.query.page.current == 1" aria-label="Previous">
        <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',serverconnect1.data.query.page.offset.prev)"><span aria-hidden="true">&lsaquo;</span></a>
    </li>
    <li class="page-item" dmx-class:active="title == serverconnect1.data.query.page.current" dmx-class:disabled="!active" dmx-repeat="serverconnect1.data.query.getServerConnectPagination(2,1,'...')">
        <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',(page-1)*serverconnect1.data.query.limit)">{{title}}</a>
    </li>
    <li class="page-item" dmx-class:disabled="serverconnect1.data.query.page.current ==  serverconnect1.data.query.page.total" aria-label="Next">
        <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',serverconnect1.data.query.page.offset.next)"><span aria-hidden="true">&rsaquo;</span></a>
    </li>
    <li class="page-item" dmx-class:disabled="serverconnect1.data.query.page.current ==  serverconnect1.data.query.page.total" aria-label="Last">
        <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',serverconnect1.data.query.page.offset.last)"><span aria-hidden="true">&rsaquo;&rsaquo;</span></a>
    </li>
</ul>

Can you show the full code?

miu · October 5, 2024, 2:23pm

This is the full code:

<dmx-query-manager id="query1"></dmx-query-manager>
<dmx-serverconnect id="sc_qry_allApps" url="/api/uniAdmin/manageApps/qry_allApps" dmx-param:offset="query1.data.offset" dmx-param:sort="query1.data.sort" dmx-param:dir="query1.data.dir"></dmx-serverconnect>
<div class="container">
    <div class="row">
        <div class="col">
            <table class="table table-bordered table-sm">
                <thead>
                    <tr>
                        <th class="sorting" dmx-on:click="query1.set('sort','appID');query1.set('dir',query1.data.dir == 'desc' ? 'asc' : 'desc')" dmx-class:sorting_asc="query1.data.sort=='appID' && query1.data.dir == 'asc'" dmx-class:sorting_desc="query1.data.sort=='appID' && query1.data.dir == 'desc'">App ID</th>
                        <th class="sorting" dmx-on:click="query1.set('sort','appNo');query1.set('dir',query1.data.dir == 'desc' ? 'asc' : 'desc')" dmx-class:sorting_asc="query1.data.sort=='appNo' && query1.data.dir == 'asc'" dmx-class:sorting_desc="query1.data.sort=='appNo' && query1.data.dir == 'desc'">App no</th>
                    </tr>
                </thead>
                <tbody is="dmx-repeat" dmx-generator="bs5table" dmx-bind:repeat="sc_qry_allApps.data.query_allApps.data" id="tableRepeat2" dmx-state="query1" dmx-sort="sort" dmx-order="dir">
                    <tr>
                        <td dmx-text="appID"></td>
                        <td dmx-text="appNo"></td>
                    </tr>
                </tbody>
            </table>
            <ul class="pagination" dmx-populate="sc_qry_allApps.data.query_allApps" dmx-state="query1" dmx-offset="offset" dmx-generator="bs5paging">
                <li class="page-item" dmx-class:disabled="sc_qry_allApps.data.query_allApps.page.current == 1" aria-label="First">
                    <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',sc_qry_allApps.data.query_allApps.page.offset.first)"><span aria-hidden="true">&lsaquo;&lsaquo;</span></a>
                </li>
                <li class="page-item" dmx-class:disabled="sc_qry_allApps.data.query_allApps.page.current == 1" aria-label="Previous">
                    <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',sc_qry_allApps.data.query_allApps.page.offset.prev)"><span aria-hidden="true">&lsaquo;</span></a>
                </li>
                <li class="page-item" dmx-class:active="title == sc_qry_allApps.data.query_allApps.page.current" dmx-class:disabled="!active" dmx-repeat="sc_qry_allApps.data.query_allApps.getServerConnectPagination(2,1,'...')">
                    <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',(page-1)*sc_qry_allApps.data.query_allApps.limit)">{{title}}</a>
                </li>
                <li class="page-item" dmx-class:disabled="sc_qry_allApps.data.query_allApps.page.current ==  sc_qry_allApps.data.query_allApps.page.total" aria-label="Next">
                    <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',sc_qry_allApps.data.query_allApps.page.offset.next)"><span aria-hidden="true">&rsaquo;</span></a>
                </li>
                <li class="page-item" dmx-class:disabled="sc_qry_allApps.data.query_allApps.page.current ==  sc_qry_allApps.data.query_allApps.page.total" aria-label="Last">
                    <a href="javascript:void(0)" class="page-link" dmx-on:click="query1.set('offset',sc_qry_allApps.data.query_allApps.page.offset.last)"><span aria-hidden="true">&rsaquo;&rsaquo;</span></a>
                </li>
            </ul>
        </div>
    </div>
</div>

franse · October 5, 2024, 2:34pm

I don't see anything wrong.
It's a node project? Can you check the main layout if dmxStateManagement.js is there?

And in your first line of the content page:

Then save, and restart the server..

miu · October 5, 2024, 2:47pm

I have those components on both the pages. I tried restarting server & wappler, however the issue still persists.

Unless I click the table header to trigger sort/dir, the paging does not work.

If I click Page 2, the URL changes to ?offset=25 but nothing happens.

http://localhost:3000/university/online-screening/paging-gen?offset=25

When I click the table header, the URL changes to this & the paging effect triggers showing the next page

http://localhost:3000/university/online-screening/paging-gen?offset=25&sort=appID&dir=desc

franse · October 5, 2024, 3:29pm

I just recreated all again using your code, just copied and pasted:

Everything works here

How is the first line of your content page?
What do you have on your layout?
Is the content page correctly linked to that layout and not other one?

miu · October 5, 2024, 4:01pm

Can you share the full code of this table

miu · October 5, 2024, 4:11pm

Yeah i checked, the content page is correctly linked to the proper layout.

I am sharing the full head code of the layout page:

<head>
  <base href="/">
  <script src="/dmxAppConnect/dmxAppConnect.js"></script>
  <meta charset="UTF-8">
  <title>Untitled Document</title>

  <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.15.4/css/all.css" integrity="sha384-DyZ88mC6Up2uqS4h/KRgHuoeGwBcD4Ng9SiP4dIRy0EXTlnuz47vAwmeGwVChigm" crossorigin="anonymous" />
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <link rel="stylesheet" href="/bootstrap/5/css/bootstrap.min.css" />
  <link rel="stylesheet" href="/css/style.css" />
  <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
  <script src="/dmxAppConnect/dmxBootstrap5Navigation/dmxBootstrap5Navigation.js" defer></script>
  <script src="/dmxAppConnect/dmxRouting/dmxRouting.js" defer></script>
  <script src="/dmxAppConnect/dmxBrowser/dmxBrowser.js" defer></script>
  <link rel="stylesheet" href="/dmxAppConnect/dmxBootstrap5TableGenerator/dmxBootstrap5TableGenerator.css" />
  <script src="/dmxAppConnect/dmxBootstrap5Offcanvas/dmxBootstrap5Offcanvas.js" defer></script>
  <script src="/dmxAppConnect/dmxBootstrap5Collapse/dmxBootstrap5Collapse.js" defer></script>
  <link rel="stylesheet" href="/dmxAppConnect/dmxValidator/dmxValidator.css" />
  <script src="/dmxAppConnect/dmxValidator/dmxValidator.js" defer></script>
  <link rel="stylesheet" href="/dmxAppConnect/dmxNotifications/dmxNotifications.css" />
  <script src="/dmxAppConnect/dmxNotifications/dmxNotifications.js" defer></script>
  <script src="/dmxAppConnect/dmxFormatter/dmxFormatter.js" defer></script>
  <script src="/dmxAppConnect/dmxBootstrap5Popovers/dmxBootstrap5Popovers.js" defer></script>
  <script src="/dmxAppConnect/dmxBootstrap5Modal/dmxBootstrap5Modal.js" defer></script>
  <script src="/dmxAppConnect/dmxFormRepeat/dmxFormRepeat.js" defer></script>
  <script src="/dmxAppConnect/dmxDataTraversal/dmxDataTraversal.js" defer></script>
  <link rel="stylesheet" href="/dmxAppConnect/dmxDropzone/dmxDropzone.css" />
  <script src="/dmxAppConnect/dmxDropzone/dmxDropzone.js" defer></script>
  <script src="/dmxAppConnect/dmxBootbox5/bootstrap-modbox.min.js" defer></script>
  <script src="/dmxAppConnect/dmxBootbox5/dmxBootbox5.js" defer></script>
  <script src="/dmxAppConnect/dmxBootstrap5Tooltips/dmxBootstrap5Tooltips.js" defer></script>
  <script src="/dmxAppConnect/dmxStateManagement/dmxStateManagement.js" defer></script>
  <link rel="stylesheet" href="/dmxAppConnect/dmxMediumEditor/dmxMediumEditor.css" />
  <script src="/dmxAppConnect/dmxMediumEditor/dmxMediumEditor.js" defer></script>
  <link rel="stylesheet" href="/dmxAppConnect/dmxMediumEditor/medium-editor.css" />
  <script src="/dmxAppConnect/dmxMediumEditor/medium-editor.js" defer></script>
  <link rel="stylesheet" href="/dmxAppConnect/dmxMediumEditor/themes/default.css" />
  <link rel="stylesheet" href="/dmxAppConnect/dmxMediumEditorImageUpload/dmxMediumEditorImageUpload.css" />
  <script src="/dmxAppConnect/dmxMediumEditorImageUpload/dmxMediumEditorImageUpload.js" defer></script>
  <script src="/dmxAppConnect/dmxPdfCreator/dmxPdfCreator.js" defer></script>
  <script src="https://cdn.jsdelivr.net/npm/pdfmake@0.2.9/build/pdfmake.min.js" defer></script>
  <script src="https://cdn.jsdelivr.net/npm/pdfmake@0.2.9/build/vfs_fonts.min.js" defer></script>
  <script src="https://cdn.jsdelivr.net/npm/html-to-pdfmake@2.5.2/browser.min.js" defer></script>
  <script src="/dmxAppConnect/dmxTyped/dmxTyped.js" defer></script>
  <script src="/dmxAppConnect/dmxTyped/typed.min.js" defer></script>
  <script src="https://cdn.jsdelivr.net/npm/@yaireo/tagify@4.17.7/dist/tagify.min.js" defer></script>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@yaireo/tagify@4.17.7/dist/tagify.css" />
  <script src="/dmxAppConnect/dmxTagify/dmxTagify.js" defer></script>
  <link rel="stylesheet" href="/dmxAppConnect/dmxSummernote/summernote-bs5.min.css" />
  <script src="/dmxAppConnect/dmxSummernote/summernote-bs5.min.js" defer></script>

  <script src="/dmxAppConnect/dmxSummernote/dmxSummernote.js" defer></script>
  <script src="/dmxAppConnect/dmxSummernoteFileUpload/summernote-upload.js" defer></script>
  <script src="/dmxAppConnect/dmxBootstrap5PagingGenerator/dmxBootstrap5PagingGenerator.js" defer></script>
  <script src="/dmxAppConnect/dmxMasonry/dmxMasonry.js" defer></script>
</head>

This is the first lines from the content page:

<!-- Wappler include head-page="layouts/university" fontawesome_5="cdn" bootstrap5="local" is="dmx-app" id="paginggen" appConnect="local" components="{dmxStateManagement:{},dmxBootstrap5TableGenerator:{},dmxBootstrap5PagingGenerator:{}}" -->
<dmx-query-manager id="query1"></dmx-query-manager>
<meta name="ac:route" content="/university/online-screening/paging-gen">
<dmx-serverconnect id="sc_qry_facility" url="/api/uniAdmin/manageApps/qry_facility_paging_gen" dmx-param:offset="query1.data.offset" dmx-param:sort="query1.data.sort" dmx-param:dir="query1.data.dir"></dmx-serverconnect>

George · October 5, 2024, 7:37pm

Are you using Microsoft SQL Server and which version is it?