Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    Raleigh, NC
    Posts
    484
    Thanks
    0
    Thanked 0 Times in 0 Posts

    page through a closed recordset

    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.
    -WebMark Art
    Programming is 80% thinking and 20% spelling

  • #2
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    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:
    http://www.adopenstatic.com/experime...dsetpaging.asp

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

    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #3
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Like:
    Code:
    arrayname=rsRecordsetname.GetRows() ' dumps all fields from all records in the array
    rsRecordsetname.Close
    conn.Close
    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 
    'etc
    <edit> posts crossed. Seems GetRows is gaining popularity </edit>
    Last edited by raf; 11-10-2003 at 08:53 PM.

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Shows paging via GetRows:

    http://www.15seconds.com/issue/010308.htm
    does this sig match?

  • #5
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    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!
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #6
    Regular Coder
    Join Date
    Feb 2003
    Location
    Helsinki, Finland
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    <%
    	If nRecSet.EOF And nRecSet.BOF Then
    	  recCount = -1
    	 Else
    	  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"
    			Else
    		      startNum = strRecs
    			End If
    		  Else
    		    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 
    Next
    End If
    %>   </table>
      </form>
    <% 
    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
     Else
      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).


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •