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

x
?
Solved

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

Posted on 2011-02-28
7
Medium Priority
?
721 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
[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
  • 3
  • 3
7 Comments
 
LVL 143

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 143

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

610 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