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

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

bobbysdogAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
mayank_joshiCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
bobbysdogAuthor Commented:
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
 
bobbysdogAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>TOTAL record count with this code.

you have pagecount and recordcount properties ...
0
 
bobbysdogAuthor Commented:
oh RS.recordcount

duh

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