Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Paging with coldfusion and MS SQL

Posted on 2012-04-12
4
Medium Priority
?
396 Views
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# 
</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.
0
Comment
Question by:earwig75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 37840552
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
 
LVL 36

Expert Comment

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

<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
 

Author Closing Comment

by:earwig75
ID: 37856237
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
 
LVL 52

Expert Comment

by:_agx_
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.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

618 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