?
Solved

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

Posted on 2011-02-28
7
Medium Priority
?
696 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
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!

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
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/…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

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