Spanning Multiple Pages [Archive]

wasiq

Oct 20th, 2000, 02:04 PM

Hi,
I made an IS Application in vb. Everything is working fine. I have 1000 something rows in a table, what I want is that a user be able to view the pages by limiting the maximum number of pages on each page, (like on search engines), say 10 rows on each coloumn, and a total of 100 pages. How do I do this? I hope you understood my question. In simple words, I want to span all the records on multiple pages, which results in less loading time, and puts very little load on the server too.

monte96

Oct 20th, 2000, 03:24 PM

I'm thinking there are two approaches to doing this.

You could put the recordset object into a session object (This will require a beefy server if the site is hit alot).

Or you can use a temporary table in the database with a sequence number to differentiate one user's data from another, and use the page number times the qty per page to pull records for each page.

wasiq

Oct 21st, 2000, 11:18 PM

there has to be some other way.

Serge

Oct 22nd, 2000, 09:05 PM

You can use PageSize, AbsolutePage and PageCount properties of the Recordset object to achieve this. Here is an example:

TestPage.asp

<%
Dim objConn
Dim objRS
Dim strSQL
Dim intCurrentPage

If Request("cmdSearch") = "Search Example" Then
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=DSNName;UID=UserName;PWD=Password"

Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "Select * From TableName"
With objRS
.CursorLocation = 3 'adUserClient
.CursorType = 3 'adOpenStatic
.Open strSQL, objConn

'Set how many records per page (5 in this example)
.PageSize = 5
End With

Set Session("objCN") = objConn
Set Session("objRS") = objRS
Session("Page") = Session("objRS").AbsolutePage
DisplayPage
Else
If IsObject(Session("objCN")) Then
Set objConn = Session("objCN")
Set objRS = Session("objRS")
intCurrentPage = Session("Page")
If Request("Direction") = "Next" then
'If we are not at the end of the pages then
'go to the next page and display
If Not (intCurrentPage = objRS.PageCount) then
objRS.AbsolutePage = intCurrentPage + 1
DisplayPage
Else
' Display that we are at the end of the records and set the
' page count up so that the Prev will calculate the last page
Response.Write "<P>No More Records"
Session("Page") = objRS.PageCount + 1
End If
Else
'If Previous is selected

If intCurrentPage <> 1 then
objRS.AbsolutePage = intCurrentPage - 1
DisplayPage
Else
'Display that we are at the start of the pages. Set the page number
'to zero so that the next will increment to one
Response.Write "<P>At the beginning of Records"
Session("Page") = 0
End if
End if
End If
End If

Sub DisplayPage
Dim intRecord
Dim intCol

Response.Write "<TABLE Border=1>"

'Create Table Headers
Response.Write "<TR>"
For intCol = 0 To objRS.Fields.Count - 1
Response.Write "<TD bgcolor='#c0c0c0'>" & objRS.Fields(intCol).Name & "</TD>"
Next
Response.Write "</TR>"

Session("Page")=objRS.AbsolutePage
For intRecord = 1 To objRS.PageSize
Response.Write "<TR>"
For intCol = 0 To objRS.Fields.Count - 1
Response.Write "<TD>" & objRS.Fields(intCol) & "</TD>"
Next
Response.Write "</TR>"
objRS.MoveNext
If objRS.EOF Then Exit For
Next

Response.Write "<TFOOT>"
Response.Write "<TR>"
Response.Write "<TD><A HREF=TestPage.asp?Direction=Prev>Previous Page</A></TD>"
Response.Write "<TD><A HREF=TestPage.asp?Direction=Next>Next Page</A></TD>"
Response.Write "</TR>"
Response.Write "</TFOOT>"
Response.Write "</TABLE>"
End Sub

%>
<HTML>
<BODY>
<BR><BR>

<FORM action=TestPage.asp Method="post">
<INPUT Type=submit Value="Search Example" Name=cmdSearch>
</FORM>

</BODY>
</HTML>

[Edited by Serge on 10-22-2000 at 10:09 PM]

wasiq

Oct 23rd, 2000, 02:14 PM

thanks a lot!!