[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cfquery and LIMIT error

Posted on 2009-04-30
8
Medium Priority
?
519 Views
Last Modified: 2012-05-06
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
Comment
Question by:dudeatwork
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24273142
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
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24273146
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 24273217
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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 36

Expert Comment

by:SidFishes
ID: 24273234
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
 

Author Comment

by:dudeatwork
ID: 24273542
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 24273868
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 24286607
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
 

Author Closing Comment

by:dudeatwork
ID: 31576593
As always thanks a million...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question