• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

Cfquery and LIMIT error

I keep getting the following error using LIMIT in cfquery. I am trying to to build a pagination...
<< 12345>>
I dont want to show more than 5 links at a time but I am stuck on the LIMIT error when cfquery queries the access mdb

Error:
Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'f_id = '1' LIMIT 0, 25'.  

I have tried it by moving the LIMIT above and below the ORDER BY and same error. What am I doing wrong here?


Code:
<cfquery name="gethist" datasource="#dsn#" result="get_data">
                                  SELECT tid
                                   FROM timecard
                                   where f_id = '#f_id#'
                                   LIMIT #start_record#, #records_per_page#
                                   ORDER BY tid
                              </cfquery>
0
dudeatwork
Asked:
dudeatwork
  • 3
  • 2
  • 2
  • +1
1 Solution
 
erikTsomikSystem Architect, CF programmer Commented:
can you just say

<cfquery name="gethist" datasource="#dsn#" result="get_data">
                                  SELECT  top 25 tid
                                   FROM timecard
                                   where f_id = '#f_id#'
                                   LIMIT #start_record#, #records_per_page#
                                   ORDER BY tid
                              </cfquery>
0
 
erikTsomikSystem Architect, CF programmer Commented:
sirry typo
<cfquery name="gethist" datasource="#dsn#" result="get_data">
                                  SELECT top 25  tid
                                   FROM timecard
                                   where f_id = '#f_id#'
                           
                                   ORDER BY tid
                              </cfquery>
0
 
SidFishesCommented:
if it's a display issue use the maxrows and startrow attributes of cfoutput


there's a good, simple tut on pagination here
http://tutorial20.easycfm.com/ 
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
SidFishesCommented:
btw... Limit is not valid in msaccess, only in mssql

so Erik is right in saying use the TOP n if you want to "limit" the results returned by a query

0
 
dudeatworkAuthor Commented:
I have the next x of x from the tutorial you listed. That works fine, always has. I want to display the pages...
Example:
Prev       1 2 3 4 5        Next

Say you click '5' I want to see
Prev       5 6 7 8 9        Next

I have thousands of records being returned in any one instance and dont want to click "Next" a hundred times.

Here is the code I am working with:

<cfparam name="url.page" default="1">
<cfset page_links_shown = 5>
<cfset records_per_page = 25>
<cfset start_record = url.page * records_per_page - records_per_page>

<cfquery name="get_count" datasource="#dsn#">
      SELECT COUNT(tid) AS records FROM timecard
      where f_id = '#f_id#'
</cfquery>
<cfquery name="gethist" datasource="#dsn#">
      SELECT tid
      FROM timecard
      where f_id = '#f_id#'
      ORDER BY tid
      LIMIT #start_record#, #records_per_page#
</cfquery>
<cfset total_pages = ceiling(get_count.records / records_per_page)>
<cfoutput query="gethist" >
 #start_record + currentrow#<br>
</cfoutput>
<cfif url.page EQ 1>
     Prev Page
<cfelse>
     <a href="index.cfm?mode=history&f_id=#f_id#&page=#url.page-1#">Prev Page</a>
</cfif>
<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>
             <b>#i#</b>
      <cfelse>
              <a href="pages.cfm?page=#i#">#i#</a>
      </cfif>
</cfloop>
<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>
0
 
SidFishesCommented:
ahh...google type pagination

here's some skeleton code


<cfquery name="myQ"
             datasource="mydata"
             >
        SELECT   * from tbl_products
 
</cfquery>
 
<cfparam name="URL.PageIndex" default="0">
<cfset RecordsPerPage = 3>
<cfset TotalPages = (myQ.Recordcount/RecordsPerPage)-1>
<cfset StartRow = (URL.PageIndex*RecordsPerPage)+1>
<cfset EndRow = StartRow+RecordsPerPage-1>
 
<table width=75% border=0 align="center">
         <tr>
          <td align="center">
               <br/>
               Results<br/>
               <br/>
       </td>
     </tr>
     
     <tr>
       <td>
                    
<cfoutput>
<cfloop query ="myQ">
<cfif CurrentRow gte StartRow >
<table width=100% border=0 align="center" >
	<tr>
		<td>#productidupc#</td>
		<td align=right>#proddescenglish#</td>
	</tr>
</table>
</cfif>
   <cfif CurrentRow eq EndRow>
      <cfbreak>
   </cfif>
   </cfloop>
</cfoutput>
         </td>
     </tr>
    <tr>
     <td align="center">
     <br/>
     <cfloop index="Pages" from="0" to="#TotalPages#">
      <cfoutput>
         |
         <cfset DisplayPgNo = Pages+1>
         <cfif URL.PageIndex eq pages>
            <strong>#DisplayPgNo#</strong>
         <cfelse>
            <a href="index.cfm?PageIndex=#Pages#">#DisplayPgNo#</a>
         </cfif>
         
      </cfoutput>
      </cfloop>
 
       <br/>
     </td>
     </tr>
  
</table>

Open in new window

0
 
gdemariaCommented:
Here's another example of paging.   It's similar to Sid's but may be good for larger record sets.  

The difference is that Sid's loops through all records and only shows the ones that are on that page.  
The one below fetches all IDs once, and then picks-out a page worth of IDs and queries the details of only the records that belong on the page.  
<!--- get the page number from the URL or form variable, otherwise, start at 1 ---->
<cfif isDefined('url.page') and val(url.page)>
  <cfset variables.pageNumber = url.page>
<cfelseif isDefined('form.page') and val(form.page)>
  <cfset variables.pageNumber = form.page>
<cfelse>
  <cfset variables.pageNumber = 1>
</cfif>
 
 
<cfset variables.items_per_page = 25> <!---- define how many records to show on a page ---->
 
<!--- this query fetches ALL records that match the criteria,
      alter this query with the correct where clause and order by 
      to get the right records in the right order. 
      To Keep the fetch small, we get ONLY the ID from this query
       --------->
<cfquery NAME="AllRecords" DATASOURCE="#request.datasource#">
  select ID --- fetch only the ID
  from MyTable
  where someFields = #variables.fitler#
  and   otherFields = #variables.otherFields#
  ORDER BY WHATEVER
</cfquery>
 
<!--- do a little math to figure out the max number of pages ---->
<cfset variables.numberOfPages = ceiling(AllRecords.recordCount / variables.items_per_page)>
 
<!---- make sure the page number requested is within the range of pages that we have ---->
<cfif variables.PageNumber le 0> <!---- if page number is too low, set it to first page --->
    <cfset variables.PageNumber = 1>
<cfelseif variables.pageNumber gt AllRecords.recordCount>  <!--- if page number passed is too high, set it to last page ---->
    <cfset variables.PageNumber = variables.numberOfPages>
</cfif>
<!---- based on the page number and number of items on a page, determine the start and end record for the page ---->
<!---- for example, page 3 with 25 items per page will show records 51 through 75, page 1 shows records 1-25 ---->
<cfset variables.startRecord   = ((variables.PageNumber - 1) * variables.items_per_page) + 1>
<cfset variables.endRecord     = variables.startRecord + items_per_page - 1>
 
 
<!---- Now we have the starting record number and the ending one, 
         so we are going to build a little list of the IDs for those records.
         If we show page 3, we will get the ID for records 51 through 76 ----->
<cfset variables.PageIDList = "">
<cfloop index="ii" from="#variables.startRecord#" to="#variables.endRecord#">
  <cfset variables.PageIDList = listAppend(variables.PageIDList, AllRecords['ID'][ii])>
</cfloop>
 
<!----- pageIDList now holds a list of IDS; such as 56,12,98,45,12,5,8, etc.
          These IDs are the IDs we need for this page.  Now fetch again to grab
          all the data we need about these;  So we SELECT * FROM... 
          Be sure to use the Same ORDER BY Clause as in AllRecords Query --------->
<cfquery NAME="thisPage" DATASOURCE="#request.datasource#">
  select * 
  from MyTable
  where ID in (#variables.PageIDList#)
  ORDER BY WHATEVER
</cfquery>
 
 
<!---- now you have the records you need for this page, use the thisPage
       query in a simple cfoutput to display the records ---->
<cfoutput query="thisPage">
 #thisPage.column1# #thisPage.column2# #thisPage.column3#<br>
</cfoutput>
 
<!---- now show the page list ---->
<cfloop index="ii" from="1" to="#variables.numberOfPages">
  <cfif variables.PageNumber is ii>
   #ii#
  <cfelse>
   <a href="/index.cfm?page=#ii#">#ii#</A>
  </cfif>
</cfloop>

Open in new window

0
 
dudeatworkAuthor Commented:
As always thanks a million...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now