PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Database - How do I get just a range of records, as you do in search results?-VBForums
Results 1 to 1 of 1

Thread: Database - How do I get just a range of records, as you do in search results?

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,904

    Database - How do I get just a range of records, as you do in search results?

    When showing lots of data, many programs and web sites (including this one) show just a range of results, with links to Next/Previous/etc pages. This is known as "paging" the data.

    Doing this makes navigating the results easier, and also means that the program/site runs faster, as less data is worked with at any one time. But how do you do it in ADO?


    Well it is actually quite simple - you just use a few more properties than usual.

    Before opening the recordset, you set PageSize (the number of records per "page") and CacheSize (how many records to store in memory at one time). For the best speed, CacheSize should be the same value as PageSize.

    Next you open the recordset as normal, and then simply set AbsolutePage to be the page to load (the first page is 1). You can read PageCount to find out how many pages of data there are in the recordset (ie: the maximum value you can use for AbsolutePage).


    To show data you would normally loop until you reach EOF, but in this case you do not want to do that, as you only want to load the right number records for a page - but you must still check for EOF, as there may not be enough records on the last page for it to be full!

    Here is an example of how you might do all of this:
    vb Code:
    1. 'set up the recordset, and load the data:
    2. Dim rs as ADODB.Recordset
    3.   Set rs = New ADODB.Recordset
    4.   rs.PageSize = 10
    5.   rs.CacheSize = 10
    6.   rs.Open "SELECT field1 FROM table1", cn, adOpenStatic, adLockReadOnly, adCmdText
    7.  
    8. 'use the data
    9. Dim intRecord As Integer
    10.   If rs.EOF Then
    11.     Msgbox "No data found!"
    12.   Else
    13.       'for this example, go to the second page of data
    14.     rs.AbsolutePage = 2  
    15.       'show the data for this page..
    16.     For intRecord = 1 To rs.PageSize
    17.         '..do what we want with the data in this record
    18.       MsgBox rs.Fields("field1").value
    19.  
    20.         '..move to the next record in this page
    21.       rs.MoveNext
    22.         '..if we have run out of records (which we may on the last page) exit the loop
    23.       If rs.EOF Then Exit For
    24.  
    25.     Next intRecord
    26.   End If
    27.  
    28. 'tidy up
    29.   rs.Close
    30.   Set rs = Nothing
    ..and in the attachment below is an example of how you can extend this to allow the user to move from one page to another.
    Attached Files Attached Files

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width