Paging with coldfusion and MS SQL

Posted on 2012-04-12
Last Modified: 2012-04-18
I am trying to create a page that will allow users to page through records. I'd like to only query the necessary records for each page rather than capture the entire query first. I am using Microsoft SQL so I cannot use LIMIT in my query. I am working with this example but cannot replicate it without the LIMIT command. I need to figure out how to write a query with replacing the 2 variables after the LIMIT

<cfquery name="get_names" datasource="#dsn#" result="get_data">
   SELECT names
   FROM pages_names
   ORDER BY names
   LIMIT #start_record#, #records_per_page# 

Open in new window

Does anyone have an example of paging in this manner? I have some examples that seem to accomplish what I want on the SQL site but I can't get coldfusion code to work properly with it - so basically I'm fishing for some examples that you may have used. Thank you.
Question by:earwig75
LVL 37

Accepted Solution

momi_sabag earned 500 total points
ID: 37840552

select names from (
SELECT names, row_number() over(order by names asc) rown
   FROM pages_names
 where rown between  #start_record# and   #start_record#+#records_per_page#
LVL 36

Expert Comment

ID: 37842865
THis should do the trick for you don't need to add any logic to your query, you just use record position. You can change displayPerPage to whatever


<!--- query simulator --->
<cfset yourquery= querynew("")>
<cfset queryaddcolumn  (yourquery, "Col1", "CF_SQL_integer", ListToArray("1,2,3,4,5,6"))>
<cfset queryaddcolumn  (yourquery, "Col2", "cf_sql_varchar", ListToArray("here, there, everywhere, nowhere, anywhere,somewhere"))>
 <!--- end QS --->

<cfparam name="URL.PageIndex" default="0">
<cfset DisplayPerPage = 3>
<cfset TotalPages = (yourquery.Recordcount/DisplayPerPage)-1>
<cfset StartRow = (URL.PageIndex*DisplayPerPage)+1>
<cfset EndRow = StartRow+DisplayPerPage-1>

<cfloop query ="yourquery">
<cfif CurrentRow gte StartRow >


   <cfif CurrentRow eq EndRow>
     <cfloop index="Pages" from="0" to="#TotalPages#">
         <cfset DisplayPgNo = Pages+1>
         <cfif URL.PageIndex eq pages>
            <a href="index.cfm?PageIndex=#Pages#">#DisplayPgNo#</a>



Author Closing Comment

ID: 37856237
by: momi_sabagPosted on 2012-04-12 at 17:07:10ID: 37840552

I was able to work with this with some tweaking. I ended up using something like this:

      Person, Started, IDNumber
      FROM md.mytbl
      WHERE Person='#arguments.person#'
      AND Created BETWEEN '#variables.begindt#' AND '#variables.enddt# 23:59:59'
) as TBL
WHERE numrow > #start_record# and numrow <= #end_record#
LVL 52

Expert Comment

ID: 37861923
WHERE numrow > #start_record# and numrow <= #end_record#

Please start using cfqueryparam on all query parameters :)  because that is just a sql injection attack waiting to happen.

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
datetime in sql 6 29
TSQL DateADD update Question 4 29
Sql Count with Select Distinct 4 27
Help Parsing a String with SQL Syntax 23 28
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them ( . I did keep the main js functions but made sever…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now