View Full Version : page through a closed recordset

11-10-2003, 08:35 PM
Does anyone know if it is possible to page through a closed recordset that has been returned from a stored procedure without using any of the ADO recordset properties such as Pagesize, pagecount, or absolute page?
Any help would be much appreciated.

11-10-2003, 08:44 PM
Well it all depends what you mean by paging - you can replicate a recordset's paging functionality in a number of ways, the most efficient being to use .GetRows() to retrieve the raw dataset.

More to the point, why do you want to do this? Why can't you use a disconnected recordset? See this page too:

And btw, (by definition) a closed recordset doesn't contain any data!


11-10-2003, 08:50 PM
Unless I'm mistaking : after you close a recordset, it's no longer accesible.

So you need to keep it open or if you do wan't to close it (for instace, if you wan't to reduce the number of simultanious db-connection and free the tablelocks and optimize db-performance like this), then you best use GetRows.
Is a recordset method that dumps the recordset into a zero-based twodimensional array.

So you can open the recordset, dump the records in the array and then close the recordset and set it to nothing.

arrayname=rsRecordsetname.GetRows() ' dumps all fields from all records in the array
set rsRecordsetname = Nothing
set conn = Nothing
'To get the values, you can then use for instance
arrayname (0,0) 'to get the value of the first variable from the first record.
arrayname (1,0) 'to get the value for the second variable from the first records
arrayname (0,1) 'to get the value of the first variable from the second record

<edit> posts crossed. Seems GetRows is gaining popularity </edit>:)

11-10-2003, 11:03 PM
Shows paging via GetRows:


11-10-2003, 11:55 PM
Originally posted by raf
<edit> posts crossed. Seems GetRows is gaining popularity </edit>:) It always *has* been popular... just not here, by the looks of things!

11-11-2003, 08:59 AM
If nRecSet.EOF And nRecSet.BOF Then
recCount = -1
allRecs = nRecSet.GetRows(-1) ' Get ALL rows...
recCount = UBound(allRecs, 2) ' Find out # of rows
numCols = UBound(allRecs, 1)
End If

Call CloseAll ' Close the connection and set it equal to Nothing (sub procedure)

If recCount >= 0 Then

Dim newsID, dateAdded, newsTitle, newsStatus

Dim strRecs, startNum, endNum, rc20

strRecs = CInt(Request.QueryString("_Recs"))
If recCount < 20 Then
startNum = "0"
endNum = recCount
rc20 = False
ElseIf recCount > 20 Then
If strRecs <> "" Then
If strRecs > recCount Or (startNum + strRecs) > recCount Then
endNum = recCount
End If
If (strRecs - 1) < 0 Then
startNum = "0"
startNum = strRecs
End If
startNum = "0"
End If
endNum = startNum + 9
rc20 = True
End If

For rowNo = startNum To endNum
Response.Write " <tr class=""nRow"" onMouseOver=""hiLite(this,1,'#F2F2F2');"" onMouseOut=""hiLite(this,0,'#F9F9F9');"">" & vbCRLF

newsID = allRecs(0, rowNo) ' ID
newsTitle = allRecs(1, rowNo) ' Title
dateAdded = allRecs(2, rowNo) ' Date of creation
newsStatus = allRecs(3, rowNo) ' Status

Response.Write " <td width=""2%"" style=""text-align:center;""><input style=""border:1px solid #E7EAEF;
width:15px; height:15px;"" name=""cItem"" type=""checkbox"" value="& newsID &" /></td>" & vbCRLF & _
" <td width=""43%"">"& newsTitle &"</td>" & vbCRLF & _
" <td width=""41%"">"& FormatDateTime(dateAdded, 1) &" "& FormatDateTime(dateAdded, 3) &"</td>" & vbCRLF & _
" <td width=""3%"" style=""text-align: center;"">"& newsID &"</td>" & vbCRLF & _
" <td width=""8%"" style=""text-align: center;"">"& newsStatus &"</td>" & vbCRLF & _
" <td width=""3%"" style=""text-align: center;""><a href=""?id="& newsID &""" title=""View extended info"">?</a></td>" & vbCRLF
Response.Write " </tr>" & vbCRLF
End If
%> </table>
Response.Write "<br />Showing Records: <b>"& startNum + 1 &"-"& endNum + 1 &"</b> of <b>"& recCount + 1 &"</b><br />"& vbCRLF
If strSort <> "" Then
Response.Write "Sorted By: <b>"& strSort &"</b>" & vbCRLF
Response.Write "Sorted By: <b>Date</b><br />" & vbCRLF
End If

This is what I'm using now... it may need some tweaking, but it works for me (for now).