Solved

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

Posted on 2011-02-28
7
658 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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