ASP: Page Numbering

[Johan]

I need ASP/VBScript guides or sample source code to add page numbering for a SQL/query result (I am NOT using .net). First I will need a simple display x amount of records on a page with next and previous buttons. Then I want to learn how to put an alphabetical bar.

I have searched the web for this basic information but I am struggling and thought that others would benefit from anyone’s insights (lets see of some one can beat putts for a reply)

[rdove]

Since you didn't provide any code that you have been working on then I will just give some help hints and things to research on.

Some attributes with an ADODB recordset to look into:
PageSize
AbsolutePage

An Alphabetical bar isn't hard just list A-Z on a pager and send whatever letter they click back to the pagein the querystring.

[putts]

The best way I've found to do this is....
1. Grab your entire recordset (or just a desired number by using the TOP N SQL command)
2. Loop through that recordset
3. Create a Div at each proper interval (0,10,20 - if showing 10 at a time)
4. Throw 10 items into that div
5. Close the Div and go back to Step 3

Your next and prev buttons just hide the current div and show the next one in the direction you're looking to go.

TOP N Example
SELECT TOP 100 * FROM TABLE_NAME

Because this example builds all the pages instantly, it's initial load time is larger, but it's time to move from page to page is slim to nill and by using TOP N you can decide how many to return initially anyway, so that way you can guage how long the initial load is.

I can go into more detail/example if you need it.

[rickyd]

That is not the best way by any stretch of the imagination, infact that is a very bad way to do it im afraid.

The best way is do do it like this:

1) Do a query the same as you main query but with COUNT(*) as cnt as the selected field so you can get the total number of results. (you need to do this because the RecordCount ADO parameter only seems to work with access databases, so if you want to use mysql this is the best solution)

2) Check for a "page" querystring parameter (which you will have once you move beyond the first page), if there isnt one set your curpage variable to 1 instead of the value of the querystring parameter.

3) Work out the total number of pages which will use the code

nPages = Int(nTotal / nPerPage)
If nTotal Mod nPerpage > 0 Then
nPages = nPages + 1
End If

4) Work out the starting record number on your current page which will use the formula

nStartRec = (nPerPage * nPageNum) - nPerPage

5) Do your main query with code similar to the following (which if you are using MySql you can alter to instead use LIMIT statement.

Rs.Open sSql, db
If Not Rs.EOF Then
i = 0
Do While Not Rs.EOF And i < (nStartRec + nPerPage)
If i >= nStartRec Then
'write out the item
End If
i = i + 1
Rs.MoveNext
Loop
End If
Rs.Close

With MySql LIMIT

Rs.Open "SELECT * FROM table LIMIT " & nStartRec & "," & nPerPage, db
If Not Rs.EOF Then
Do While Not Rs.EOF
'write out the item
Rs.MoveNext
Loop
End If
Rs.Close

6) Then write out your page list with the following

nPgeStart = 0
nPgeEnd = 0
If nPages <= 10 Then
nPgeStart = 1
nPgeEnd = nPages
ElseIf nPage <= 5 Then
nPgeStart = 1
nPgeEnd = 11
ElseIf ((nPages - nPage) < 5) Then
nPgeStart = nPages - 10
nPgeEnd = nPages
Else
nPgeStart = nPage - 5
nPgeEnd = nPage + 5
End If

For i = nPgeStart To nPgeEnd
If i = nPage Then
Response.Write "<B>" & i & "</B>"
Else
Response.Write "<A HREF=""page.asp?page=" & i & """>" & i & "</A>"
End If
Next

That should do it

Rich

[putts]

First off, ADODB.Recordset cause more overhead then they're worth.

Second off, my method, if you dont mind losing the initial load time, is a great time/load saver in the grander scope of your search/report.

Your way, in fact, creates more processing time and load if the user has to go through more than like 2 pages because you are re-creating your ENTIRE recordset each page and looping through part of it. If this is any sort of real search table, we're probably looking at 1000's of records returned everytime you run your query, whereas the Div method only runs that query once.

If you really wanted to do it your way, then you should use a more advanced and efficient technique of querying the database on each page rather than returning a large recordset over and over again.

Either ways are both viable. The div technique has more initial load time, but moving from page to page is effortless. The multiple page techniqe is initially quicker, but the more pages the user goes through, the less efficient it becomes. Also, the multiple page technique's speed is very dependant on the DB Server's quickness and how well the database's tables were set up because of the number of calls made on it.

[Johan]

Its not the best code and using rs.MoveFirst like commands is a little slow but it works. You will need to put the table tags for formating.

ASP WORKING SAMPLE 1:

'// Include the includes
<%
Function pageData()
Dim out, db, Rs, strSql, intID, iPageSize, iPage, bNoRecords, nTotalRecords, iTotalPages
Dim bOnePage, nPageEnd, nPageStart, lCtr, bLastPage, sNextQuery, iCtr, sQuery, sPageQuery, sPrevQuery


iPageSize = 15
If Request.QueryString("Page") <> "" And IsNumeric(Request.QueryString("Page")) Then
iPage = CInt(Request.QueryString("Page"))
Else
iPage = 1
End If

strSql = "SELECT *, Review.ID AS [reviewID] FROM review WHERE Review.Delete<>'Y' ORDER BY Title"
Set db = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
db.Open "DSN=futuremovies"
Rs.Open strSql, db, 1, 3

If Not rs.EOF And Not rs.BOF Then
rs.MoveLast
nTotalRecords = rs.RecordCount

iTotalPages = Int(nTotalRecords / iPageSize)
If nTotalRecords MOD iPageSize <> 0 Then
iTotalPages = iTotalPages + 1
End If

If nTotalRecords <= iPageSize Then
rs.MoveFirst
bOnePage = True
nPageEnd = nTotalRecords
nPageStart = 1
iTotalPages = 1
Else
nPageStart = ((iPage - 1) * iPageSize) + 1
nPageEnd = nPageStart + (iPageSize - 1)

If nPageEnd >= nTotalRecords Then
nPageEnd = nTotalRecords
bLastPage = True
End If

If iPage > 1 Then
rs.AbsolutePosition = ((iPage - 1) * iPageSize) + 1
Else
rs.MoveFirst
End If
End If
Else
bNoRecords = True
End If

If Not bNoRecords Then
out = out & "<P><B>Page " & iPage & " of " & iTotalPages & "</B><P>"
End If

If bNoRecords Then
out = out & "<B>There are no reviews available at the present time</B>"
Else
For lCtr = nPageStart To nPageEnd

'// Start your content code \\

intID = Rs.Fields("reviewID").Value
out = out & "<a href=""/moviesite/review.asp?ID=" & intID & """>"
out = out & Rs.Fields("title").Value
out = out & "</a>"
out = out & "<br>"

'// End your content code \\

rs.movenext
Next
End If

If bOnePage = False And bNoRecords = False Then

If iPage > 1 Then
sPrevQuery = "Page=" & iPage - 1
out = out & "<a href=""page_numbers.asp?" & sPrevQuery & """><B><< Previous Page</B></a>"
Else
out = out & "&nbsp;"
End If

out = out & "<br>"

If bLastPage = False Then

sNextQuery = "Page=" & iPage + 1
out = out & "<a href=""page_numbers.asp?" & sNextQuery & """><B>Next Page >></B></a>"
Else
out = out & "&nbsp;"
End If

out = out & "<br>"

For iCtr = 1 To iTotalPages
sPageQuery = "Page=" & iCtr & sQuery
If iCtr <> iPage Then
out = out & "<a href=""page_numbers.asp?" & sPageQuery & """>"
Else
out = out & "<B>"
End If
out = out & iCtr

If iCtr <> iPage Then
out = out & "</a>"
Else
out = out & "</B>"
End If
If iCtr < iTotalPages Then
out = out & "&nbsp;&nbsp;|&nbsp;&nbsp;"
End If
Next
out = out & "</center>"
End If

out = out & "<br>"

If rs.state <> 0 Then
rs.close
End If

'conn.close
Set rs = Nothing
'Set conn = Nothing

PageData = out
End Function
%>

[putts]

This is just whipped up in 10 minutes, but I tested it and it's working...


<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<script language="VBScript">
Function showPage(intPage)
dim divItem
for each divItem in document.all.tags("DIV")
if divItem.id = cstr("page" & intPage) then
divItem.style.display = ""
else
divItem.style.display = "none"
end if
next
End Function
</script>
<body onLoad="vbscript: showPage(1)">
<%
dim sqlconn 'Connection to SQL Server
set sqlconn = server.CreateObject("ADODB.Connection")
sqlconn.Open "{db connection here}"
strQuery = "SELECT * FROM EMPLOYEES ORDER BY FIRSTNAME"
set recMain = sqlconn.execute(strQuery)

intPerPage = 25
intCurrent = 1
intThisPage = 1
intPages = 1

while not(recMain.eof)
if intThisPage = 1 then%>
<div id="page<%=intPages%>" style="DISPLAY:NONE" style="WIDTH:100%" align="center" >
<table style="WIDTH:75%;BORDER:1px solid #000000;BACKGROUND-COLOR:F1F1F1">
<%
end if%>
<tr>
<td width="10%"><b><%=intCurrent%>.</b></td>
<td width="90%"><%=recMain("FIRSTNAME") & " " & recMain("LASTNAME")%></td>
</tr>
<%if intThisPage = intPerPage then
intThisPage = 0%>
<tr><td align="center" colspan="2">
<%if intPages > 1 then%>
<a href="vbscript: showPage(<%=intPages-1%>)">Prev <%=intPerPage%></a>
<%end if%>
<b><%=intPages%></b>
<%if not(recMain.eof) then%>
<a href="vbscript: showPage(<%=intPages+1%>)">Next <%=intPerPage%></a>
<%end if
intPages = intPages+1%>
</td></tr>
</table>
</div>
<%end if
intCurrent = intCurrent + 1
intThisPage = intThisPage + 1
recMain.moveNext
wend
if intThisPage <> 1 then%>
<tr><td align="center" colspan="2">
<%if intPages > 1 then%>
<a href="vbscript: showPage(<%=intPages-1%>)">Prev <%=intPerPage%></a>
<%end if%>
<b><%=intPages%></b>
</td></tr>
</table>
</div>
<%end if%>
</body>
</html>


Real quick and movement between pages is effortless.

[rickyd]

That will not be quick or effortless once there are a lot of records, infact it will be slow to generate, slow to load, slow to interact with, hammer the server, and not be compatible with older browsers.

Overall a very bad solution for a website designed for an audience as large as possible.

Richard

[putts]

I'm not following how you think that that would hammer the system or being, on average, a greater load on the server than the other method.

It makes one call, and runs one loop. Whereas your method makes an infinite number of calls and an infinite amount of loops (all dependant how many pages the report/search will be).

This one makes one call that, as I said, could be easily limited by using a TOP N query, and should be easily within the realm of a web/db server.

Second off, how is it NOT effortless?! The page loads once and then it just becomes a matter of doing some client side script to view the next page. That's far and away more effortless then making the page reload itself entirely everytime the user goes to move a page forward or backward, which would hammer the server much worse over time, then this.

If you're implying that the DIVS would be what makes it "Not old browser friendly", that's fine. If it was my page, I'd check for old browsers and send them to a simpler page, but I'd still want the default page to use Divs.

I've programmed these type pages both ways and the DIV method is far and away quicker and easier. If you can give me some hard proof backing your points, then that's fine. But in all my experiences, the more times you query a db server, the clunkier your site gets.

[Johan]

Putts dont DIVS use up memory?

How about this even simpler way without DIVS. The coding style is a little out-dated but you get the drift for now:


<%
'Connect to the included MS Access database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db1.mdb") & ";"
Set rs = Server.CreateObject("ADODB.Recordset")

'Set how many records per page we want
Const NumPerPage = 5

'Retrieve what page we're currently on
If Request.QueryString("page") = "" then
CurrentPage = 1 'We're on the first page
Else
CurrentPage = CInt(Request.QueryString("page"))
End If

'Open our recordset (Access database)
strSQL = "SELECT * FROM MyTable ORDER BY Name"
rs.Open strSQL, conn, 1, 1 'Opened as Read-Only

If Not rs.EOF Then
rs.MoveFirst
rs.PageSize = NumPerPage
TotalPages = rs.PageCount

'Set the absolute (current) page
rs.AbsolutePage = CurrentPage
End If

Dim Count
%>

<html><head>
<title>Database Paging Sample</title>
</head><body>

<p><font size="2" face="Arial"><b>

<%
'Display PREV page link, if appropriate
If Not CurrentPage = 1 Then
Response.Write "<a href='" & ScriptName & "?page=" & CurrentPage - 1 & "'>Prev</a> | "
Else
Response.Write "Prev | "
End If

'Display NEXT page link, if appropriate
If Not CurrentPage = TotalPages Then
Response.Write "<a href='" & ScriptName & "?page=" & CurrentPage + 1 & "'>Next</a>"
Else
Response.Write "Next"
End If
%>

</b></font></p>
<p><b><font size="2" face="Verdana">Displaying page <%=CurrentPage%> of <%=TotalPages%>:</font></b></p><p>

<%
'Loop to display data on current page.
Do While Not rs.EOF and Count < rs.PageSize
Response.Write rs("name") & "<br>"
rs.MoveNext
Count = Count + 1
Loop
%>

</p><hr>
<p><font size="1" face="Arial">The current page is stored in the
&quot;page=&quot; portion of the URL.&nbsp; If not &quot;page=&quot; is
specified, it assumes we are on the first page.&nbsp; The NEXT and PREV buttons
control which page we want to view.&nbsp; I added some extra code so that it
will not allow you to advance beyond the maximum number of pages (which will
show blank fields/no data).</font></p>
<p><font size="1" face="Arial">This code was found from
Planet Source Code.&nbsp; I modified it slightly for easier understanding and
included a sample database to pull data from. </font></p>
</body></html>

[putts]

I've heard the stuff about DIVs taking up memory, but if you go to the Website Review section of this site, most of the better web site developers are moving more towards DIVs and away from tables and the older sort of stuff.

I've never experienced the slowness with divs, but the code I wrote would also work well with using just tables by replacing..

<div id="page<%=intPages%>" style="DISPLAY:NONE" style="WIDTH:100%" align="center" >
<table style="WIDTH:75%;BORDER:1px solid #000000;BACKGROUND-COLOR:F1F1F1">

with...

<center>
<table name="page<%=intPages%>" style="DISPLAY:NONE;WIDTH:75%;BORDER:1px solid #000000;BACKGROUND-COLOR:F1F1F1">


So, if you're really concerned about the divs, that's the way I would go. The algorithm of the code is still quick, just implemented in a different fashion.

[Johan]

yeah I managed to get it working using both ways as shown her http://www.futuremovies.co.uk/review.asp

Though in the end its not the best way of showing reviews at least I have learnt how to do it in future. It is also obvious that a MySQL database is better than Access at doing things but at the moment I can only afford Access.

I think the best way of showing my reviews would be to this

A
Alpha century

B
Black Hawk down
Blade runner

Etc…. All on the one page. Should be piss piss easy for some but give be a few days for that (any hints helpful)

[torrent]

Tried posting this last evening but the problems with the reply function prevented me from doing so. Here is the reply I saved:

Just from the SQL perspective you could use a query like:SELECT LEFT(film_name,1) AS category, film_name AS film FROM table ORDER BY category;Not sure whether Access has a LEFT() function, but failing that use its substring function (which pretty much all databases have at their disposal).

[scoutt]

Originally posted by Johan
Putts dont DIVS use up memory?
and who told you that?? DIVS are the way to go and a lot faster than tables. actually you are incouraged to use divs instead and span tags in place of font tags.

Originally posted by rickyd
That will not be quick or effortless once there are a lot of records, infact it will be slow to generate, slow to load, slow to interact with, hammer the server, and not be compatible with older browsers.

Overall a very bad solution for a website designed for an audience as large as possible.
huh? I am with Putts on this one. how is that hammering the server? and if you code for older browsers than you have problems. DIVS work in anything, including NS4.xx but I am sure nobody really cares about that one. all the code on here will work in IE5+, NS6+ and anything else 6+. so I don't see how that will not work in older browsers.

if nobody mines I would like to post the working code on my site. so whomevers code is in working order can I please use it? ;)

[Johan]

Yeah you can use the one titled ASP WORKING SAMPLE 1 and 90% of the code is from my m8 Rob here at work.

Just gotta work on listing A, B, C, D, etc... on every page but I will start a new tread cos there are so many cool ways of doing this.

[scoutt]

thanks Johan. I will give you the credit :)

[putts]

Try to post this last night, but HTMLForums was hating me...

Switch your select statement to something like


SELECT FIRSTNAME,LASTNAME,LEFT(FIRSTNAME,1) AS FIRSTLETTER FROM EMPLOYEES ORDER BY FIRSTLETTER


Now, while doing the loop, watch for that firstletter to change....

dim firstlet
dim recSet
dim strSQL

strSQL = "SELECT FIRSTNAME,LASTNAME,LEFT(FIRSTNAME,1) AS FIRSTLETTER FROM EMPLOYEES ORDER BY FIRSTLETTER"

set recSet = db.execute(strSQL)
%>
<table>
<%
if not(recSet.eof) then
firstLet = recSet(&quot;FIRSTLETTER&quot;)
while not(recSet.eof)
if recSet(&quot;FIRSTLETTER&quot;) <> firstLet%>
<tr><td><%=recSet(&quot;FIRSTLETTER&quot;)%></td></tr>
<%end if
firstlet = recSet(&quot;FIRSTLETTER&quot;)
%>
<tr><td><%=recSet(&quot;FIRSTNAME&quot;) & &quot; &quot; & recSet(&quot;LASTNAME&quot;)%></td></tr>
<%
recSet.moveNext
wend
end if%>
</table>


That's a good example on how to something like that.

So, there it is.:D

[Johan]

Thats looks great Putts thanks. What I want to do with it is showing all my reviews on one page. This is mainly for search engine spider reasons.

This on the BBC website is perfect http://www.bbc.co.uk/music/reviews/atoz.shtml
and want to show my reviews in the same way.

I have greater plans for the navigation to work and once I have done it will post the code here.

[Johan]

http://www.futuremovies.co.uk/review.asp

It works but as you can it will not print the very first letter/number "2" :(


strSql = "SELECT Title, LEFT(Title,1) AS FIRSTLETTER, Review.ID AS [reviewID] FROM review WHERE Review.Delete<>'Y' ORDER BY Title"
Set db = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
db.Open "DSN=futuremovies"
Rs.Open strSql, db


If Not(Rs.eof) then
firstLet = Rs("FIRSTLETTER")
While Not(Rs.eof)
If Rs("FIRSTLETTER") <> firstLet Then
out = out & "<tr><td><br>"

out = out & "<font size=""3""><b>" & Rs("FIRSTLETTER") & "</b></font>"

out = out & "</td></tr>"
End If

firstlet = Rs("FIRSTLETTER")

out = out & "<tr><td>"

out = out & "<a href=""/moviesite/review.asp?ID=" & intID & """>"
out = out & Rs("Title")
out = out & "</a>"

out = out & "</td></tr>"
Rs.MoveNext
wEnd
End If

[Johan]

SORTED :) I just initialised the variable to ""



If Not(Rs.eof) then
firstLet = ""
While Not(Rs.eof)
If Rs("FIRSTLETTER") <> firstLet Then
out = out & "<tr><td><br>"

out = out & "<font size=""3""><b>" & Rs("FIRSTLETTER") & "</b></font>"

out = out & "</td></tr>"
End If

firstlet = Rs("FIRSTLETTER")

out = out & "<tr><td>"

out = out & "<a href=""/moviesite/review.asp?ID=" & intID & """>"
out = out & Rs("Title")
out = out & "</a>"

out = out & "</td></tr>"
Rs.MoveNext
wEnd
End If

[putts]

Good job!

The site looks realll nice.

[Johan]

Chears for you help Putts - if you want any free design work done then let me know.

[Johan]

Another way of paging:

http://www.asp101.com/samples/db_paging.asp

Damn good site that!