Spanning Multiple Pages

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.

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.

there has to be some other way.

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

TestPage.asp

Code:

---

<%
        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]

thanks a lot!!