How can I add paging for results in a table created in Classic ASP?

Question

I have some code done in VBScript that creates a table. Specifically, the code pulls information from a database and then loops through the result adding them to a table. The problem is that there are 14,000 rows in this table. Every time this page tries to load, I get a 500 Internal Server error which I assume is due to lack of memory.

For the loop, I have this:

<%
fHideNavBar = False
fHideNumber = False
fHideRequery = False
fHideRule = False
stQueryString = ""
fEmptyRecordset = False
fFirstPass = True
fNeedRecordset = False
fNoRecordset = False
tBarAlignment = "Left"
tHeaderName = "DataRangeHdr1"
tPageSize = 0
tPagingMove = ""
tRangeType = "Text"
tRecordsProcessed = 0
tPrevAbsolutePage = 0
intCurPos = 0
intNewPos = 0
fSupportsBookmarks = True
fMoveAbsolute = False

If IsEmpty(Session("DataRangeHdr1_Recordset")) Then
    fNeedRecordset = True
Else
    If Session("DataRangeHdr1_Recordset") Is Nothing Then
        fNeedRecordset = True
    Else
        Set DataRangeHdr1 = Session("DataRangeHdr1_Recordset")
    End If
End If

If fNeedRecordset Then
    Set DataConn = Server.CreateObject("ADODB.Connection")
    DataConn.Open "DSN=MYDSN","MyUserName","MyPassword"
    Set cmdTemp = Server.CreateObject("ADODB.Command")
    Set DataRangeHdr1 = Server.CreateObject("ADODB.Recordset")
    cmdTemp.CommandText = "SELECT PHONE, FAX, FIRM, ID FROM NNYBEA ORDER BY ID"
    cmdTemp.CommandType = 1

    Set cmdTemp.ActiveConnection = DataConn
    DataRangeHdr1.Open cmdTemp, , 0, 1
End If
On Error Resume Next
If DataRangeHdr1.BOF And DataRangeHdr1.EOF Then fEmptyRecordset = True
On Error Goto 0
If Err Then fEmptyRecordset = True

If Not IsEmpty(Session("DataRangeHdr1_Filter")) And Not fEmptyRecordset Then
    DataRangeHdr1.Filter = Session("DataRangeHdr1_Filter")
    If DataRangeHdr1.BOF And DataRangeHdr1.EOF Then fEmptyRecordset = True
End If

If fEmptyRecordset Then
    fHideNavBar = True
    fHideRule = True
End If
Do
    If fEmptyRecordset Then Exit Do
    If Not fFirstPass Then
        DataRangeHdr1.MoveNext
    Else
        fFirstPass = False
    End If
    If DataRangeHdr1.EOF Then Exit Do
%>
<tr>
<td><p align="center"><%= DataRangeHdr1("FIRM") %></td>
<td><p align="center"><%= DataRangeHdr1("PHONE") %></td>
<td><p align="center"><%= DataRangeHdr1("FAX") %></td>
<%end if%>
</tr>


<%
Loop%>

Now, I believe that the programmer before me essentially copied the code from this website: http://www.nnybe.com/board%20members/DEFAULT.ASP

In fact, I actually changed the column names in my loop to match the website, since it was so similar (my real column names are different). After the loop, the code I have is as follows:

</TABLE>
<%
If tRangeType = "Table" Then Response.Write "</TABLE>"
If tPageSize > 0 Then
    If Not fHideRule Then Response.Write "<HR>"
    If Not fHideNavBar Then
        %>
        <TABLE WIDTH=100% >
        <TR>
            <TD WIDTH=100% >
                <P ALIGN=<%= tBarAlignment %> >
                <FORM <%= "ACTION=""" & Request.ServerVariables("PATH_INFO") & stQueryString & """" %> METHOD="POST">
                    <INPUT TYPE="Submit" NAME="<%= tHeaderName & "_PagingMove" %>" VALUE="   &lt;&lt;   ">
                    <INPUT TYPE="Submit" NAME="<%= tHeaderName & "_PagingMove" %>" VALUE="   &lt;    ">
                    <INPUT TYPE="Submit" NAME="<%= tHeaderName & "_PagingMove" %>" VALUE="    &gt;   ">
                    <% If fSupportsBookmarks Then %>
                        <INPUT TYPE="Submit" NAME="<%= tHeaderName & "_PagingMove" %>" VALUE="   &gt;&gt;   ">
                    <% End If %>
                    <% If Not fHideRequery Then %>
                        <INPUT TYPE="Submit" NAME="<% =tHeaderName & "_PagingMove" %>" VALUE=" Requery ">
                    <% End If %>
                </FORM>
                </P>
            </TD>
            <TD VALIGN=MIDDLE ALIGN=RIGHT>
                <FONT SIZE=2>
                <%
                If Not fHideNumber Then
                    If tPageSize > 1 Then
                        Response.Write "<NOBR>Page: " & Session(tHeaderName & "_AbsolutePage") & "</NOBR>"
                    Else
                        Response.Write "<NOBR>Record: " & Session(tHeaderName & "_AbsolutePage") & "</NOBR>"
                    End If
                End If
                %>
                </FONT>
            </TD>
        </TR>
        </TABLE>
    <%
    End If
End If
%>
</TABLE>

I'm guessing from the < and > around the PagingMove part, this is supposed to allow paging. However, I'm not even seeing this on my page. I don't know if the code on the link above works on their website, but for my own website I'd ask:

How can I modify this code to provide an option to click through pages of the data result so the server doesn't run out of memory?

If there is a more elegant solution to this that can accomplish the same thing, I'd appreciate that as well!!!

Answer 1

In your SQL you could add a LIMIT offset

SELECT PHONE, FAX, FIRM, ID FROM NNYBEA ORDER BY ID LIMIT 0,10 ' Results 1 to 10
SELECT PHONE, FAX, FIRM, ID FROM NNYBEA ORDER BY ID LIMIT 10,10 ' 11 - 20
SELECT PHONE, FAX, FIRM, ID FROM NNYBEA ORDER BY ID LIMIT 20,10 ' 21 - 30
...

If you're using MySQL you can use...

SELECT SQL_CALC_FOUND_ROWS PHONE, FAX, FIRM, ID FROM NNYBEA ORDER BY ID LIMIT 0,10

... to get a total count of the results and calculate the number of page links to display:

(total_results/results_per_page) ' and round up.

Then link to the pages below the results table and pass the page numbers as a query string:

default.asp?page=1
default.asp?page=2
default.asp?page=3
...

Have some code at the top of your page that gets the requested page number and calculates the correct offset value:

<%

Const results_per_page = 10

Dim limit_offset, page_num

limit_offset = 0 ' default
page_num = request.querystring("page")

if isNumeric(page_num) then
    page_num = int(page_num)
    if page_num > 0 then
        limit_offset = (page_num-1)*results_per_page
    else
        page_num = 1 ' default   
    end if
else
    page_num = 1 ' default    
end if

%>

Finally, apply the limit offset to your SQL:

cmdTemp.CommandText = "SELECT PHONE, FAX, FIRM, ID FROM NNYBEA ORDER BY ID LIMIT " & limit_offset & "," & results_per_page

You could also use GetRows() to convert the recordset to a 2D array and apply a limit when looping

Dim r, rs_loops, theData

theData = DataRangeHdr1.getRows()

rs_loops = page_num*results_per_page 

if rs_loops > uBound(theData,2) then rs_loops = uBound(theData,2)

for r = limit_offset to rs_loops

' output data from the DataRangeHdr1 recordset

%>

<tr>
<td><p align="center"><%= theData(2,r) ' firm %></td>
<td><p align="center"><%= theData(0,r) ' phone %></td>
<td><p align="center"><%= theData(1,r) ' fax %></td>
</tr>

<%

next

But this would mean storing large amounts of unseen data in memory. Using a LIMIT offset in the SQL would make more sense.