Skip to main content
October 11, 2007
Answered

PAGINATION ERROR

  • October 11, 2007
  • 2 replies
  • 1099 views
Hi,
I'm trying to get this pagination code functioning but I keep getting an error on my query. The error says: Microsoft Access Driver] Syntax error (missing operator) in query expression 'titles LIMIT 0'
and I'm completely stumped, I pasted my coded on this page if anyone could help or point me in the right direction I would appreciate it. Thanks.


<cfparam name="url.page" default="1">

<cfset page_links_shown = 5>

<cfset records_per_page = 5>

<cfset start_record = url.page * records_per_page - records_per_page>

<cfquery name="get_count" datasource="dgr">
SELECT COUNT(nid) AS records
FROM news
</cfquery>




<cfquery name="get_names" datasource="dgr" result="get_data">
SELECT titles
FROM news
ORDER BY titles
LIMIT #start_record#, #records_per_page#
</cfquery>




<cfset total_pages = ceiling(get_count.records / records_per_page)>

<cfoutput>

<cfloop query="get_names">
#start_record + currentrow#. #names#<br/>
</cfloop>
<hr>

<cfif url.page EQ 1>
Prev Page
<cfelse>
<a href="pages.cfm?page=#url.page-1#">Prev Page</a>
</cfif>
<hr>

<cfif url.page * records_per_page LT get_count.records>
<a href="pages.cfm?page=#url.page+1#">Next Page</a>
<cfelse>
Next Page
</cfif>
<hr>

<cfparam name="start_page" default="1">


<cfparam name="show_pages" default="#min(page_links_shown,total_pages)#">


<cfif url.page + int(show_pages / 2) - 1 GTE total_pages>
<cfset start_page = total_pages - show_pages + 1>
<cfelseif url.page + 1 GT show_pages>
<cfset start_page = url.page - int(show_pages / 2)>
</cfif>

<cfset end_page = start_page + show_pages - 1>

<cfloop from="#start_page#" to="#end_page#" index="i">
<cfif url.page EQ i>
#i#
<cfelse>
<a href="pages.cfm?page=#i#">#i#</a>
</cfif>
</cfloop>
<hr>

<a href="pages.cfm?page=1">First Page</a>
<hr>

<a href="pages.cfm?page=#total_pages#">Last Page</a>
<hr>

</cfoutput>
This topic has been closed for replies.
Correct answer Newsgroup_User
LIMIT is a MS SQL/mysql operator and is not supported by ms access.

access uses TOP instead, and differently from LIMIT:

SELECT TOP n ....
FROM....

--

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

2 replies

Inspiring
January 30, 2018

I am using this same pagination code and was having the same issue with Limit. I tried this, but it still doesn't work:

<cfquery name="get_names" datasource="#application.DataSource#" result="get_data">

SELECT Top id

FROM pages_names

ORDER BY id DESC

</cfquery>

I was able to get 5 results per page by putting maxrows="5" in the query tag above, but the names field does not update when I click onto page 2 or 3. What am I doing wrong? Can anyone help me please?

Andy

Newsgroup_UserCorrect answer
Inspiring
October 11, 2007
LIMIT is a MS SQL/mysql operator and is not supported by ms access.

access uses TOP instead, and differently from LIMIT:

SELECT TOP n ....
FROM....

--

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com