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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.