Solved

Can you help with a Super Simple "Pagination" example for Classic ASP MS Access

Posted on 2011-02-28
7
650 Views
Last Modified: 2012-05-11
Thanks for reading!

I merely want to display my table in "pages"

e.g., given the following URLs

pagetest.asp?getpage=1
pagetest.asp?getpage=2
pagetest.asp?getpage=3
pagetest.asp?getpage=4

I'd like to display records 1-10, for the first URL, and 11-20 for the next, etc...

I saw some examples online but I'm only a beginner and couldn't make any work.

: (

Can you check out my below code and tell me what i can do to make this a super simple paging ASP page?

Thanks!

<%

Dim PageNumberToGet
PageNumberToGet = Request.QueryString("getpage")		
    
Dim Conn
Dim RS
Dim SQL

Set Conn = Server.CreateObject("ADODB.connection") 
Set RS = Server.CreateObject("ADODB.recordset") 

SQL = "SELECT * FROM tableitems"

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Server.MapPath("fpdb/artisan.accdb")
RS.Open SQL, Conn

Do while not RS.Eof

    response.write RS.fields("itemid")
    response.write "<br>"
	
    RS.MoveNext
	
Loop 

RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing 

%>

Open in new window

0
Comment
Question by:bobbysdog
  • 3
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34995181
for "paging" to work, you need row numbers ... which means also, implicitly that you need an "ORDER BY", otherwise, rows might be returned in any order (even if normally you won't see differences...)

using ADO, you can use the pagesize property (value=10 for you):
http://www.w3schools.com/ADO/prop_rs_pagesize.asp

after that, you set the AbsolutePage property based on the argument:
http://www.w3schools.com/ado/prop_rs_absolute.asp

and then you process up to 10 (pagesize) records. stopping on eof latest
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 34995219
try this code:-

<%

Dim PageNumberToGet
Dim IndexNo as Integer=0
Dim NoofRecords as Integer=10
PageNumberToGet = Request.QueryString("getpage")		
    
Dim Conn
Dim RS
Dim SQL

Set Conn = Server.CreateObject("ADODB.connection") 
Set RS = Server.CreateObject("ADODB.recordset") 

SQL = "SELECT * FROM tableitems"

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Server.MapPath("fpdb/artisan.accdb")
RS.Open SQL, Conn

Do while not RS.Eof
    indexno=indexno+1

  if indexno<=(PageNumberToGet* NoofRecords)  and  indexno>((PageNumberToGet-1)* NoofRecords) then

    response.write RS.fields("itemid")
    response.write "<br>"
end if
	
    RS.MoveNext
	
Loop 

RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing 

%>

Open in new window


by changing the value of NoofRecords you can display as many records as want on one page
0
 

Author Comment

by:bobbysdog
ID: 34995280
Ah...

A while loop method.

:)

Well that indeed works. Thanks!

I was kinda hoping for a more elegant SQL solution

regrettably, I am not smart enough to understand angelIII's methods....

god i feel dumb

; (
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34995675
ok, let me put this into code:
<%

Dim PageNumberToGet
Dim IndexNo as Integer=0
Dim NoofRecords as Integer=10
PageNumberToGet = Request.QueryString("getpage")                
    
Dim Conn
Dim RS
Dim SQL

Set Conn = Server.CreateObject("ADODB.connection") 
Set RS = Server.CreateObject("ADODB.recordset") 

SQL = "SELECT * FROM tableitems"

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Server.MapPath("fpdb/artisan.accdb")
RS.Open SQL, Conn
RS.PageSize = NoofRecords 
RS.AbsolutePage = PageNumberToGet


Do while not RS.Eof AND NoofRecords > 0
  response.write RS.fields("itemid")
  response.write "<br>"
        
  RS.MoveNext
  NoofRecords = NoofRecords -1
Loop 

RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing 

%>

Open in new window

0
 

Author Comment

by:bobbysdog
ID: 34995991
Thanks for helping me woo hooooooooooo

I did have to change a few things as outlined below.
Particularly, the RS.Open SQL, Conn,1,2

Else, I get an error:

Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.

Do you think I chose the right locktype 1,2

i dunno...

Eitherway thanks so much for your help.

I can't quote figure out how to print the TOTAL record count with this code. I'd like to print that at the bottom of the screen (in the same query if possible...)  Thanks!

<%

Dim PageNumberToGet
Dim IndexNo
Dim NoofRecords

IndexNo = 0
NoofRecords = 10

PageNumberToGet = Request.QueryString("getpage")                
    
Dim Conn
Dim RS
Dim SQL

Set Conn = Server.CreateObject("ADODB.connection") 
Set RS = Server.CreateObject("ADODB.recordset") 

SQL = "SELECT * FROM tableitems"

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Server.MapPath("fpdb/artisan.accdb")


RS.Open SQL, Conn,1,2
RS.PageSize = NoofRecords 
RS.AbsolutePage = PageNumberToGet


Do while not RS.Eof AND NoofRecords > 0
  response.write RS.fields("itemid")
  response.write "<br>"
        
  RS.MoveNext
  NoofRecords = NoofRecords - 1
Loop 

RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing 

%>

Open in new window




0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34996108
>TOTAL record count with this code.

you have pagecount and recordcount properties ...
0
 

Author Comment

by:bobbysdog
ID: 34996276
oh RS.recordcount

duh

thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 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

23 Experts available now in Live!

Get 1:1 Help Now