Paging with coldfusion and MS SQL

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# 
</cfquery>

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.
earwig75Asked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
try

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#
0
 
SidFishesCommented:
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

<cfoutput>

<!--- 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>


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

#yourquery.col2#<br>

</cfif>
   <cfif CurrentRow eq EndRow>
      <cfbreak>
   </cfif>
   </cfloop>
</cfoutput>
     
     <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/>
 

</cfoutput>
0
 
earwig75Author Commented:
by: momi_sabagPosted on 2012-04-12 at 17:07:10ID: 37840552

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

SELECT * FROM
(
      SELECT ROW_NUMBER() OVER(ORDER BY IDNumber) AS numrow,
      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#
0
 
_agx_Commented:
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.
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.

All Courses

From novice to tech pro — start learning today.