ADO Paging


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: ADO Paging

  1. #1
    Michael Tzoanos Guest

    ADO Paging


    Hi,

    I am implementing ADO Paging to limit the size of the recordset that is brought
    back to the value of the Pagesize. So far, all my attempts return an entire
    disconnected recordset

    Here is my code

    Dim objRs as ADOR.Recordset

    With
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly (irrelevant as ADO will change it to adLockBatchClient
    when CursorType is static)
    .Pagesize = 20
    .Cachesize = 20
    .Open strSQL etc.
    End With

    I would expect the code above to return 20 records for the single page.
    Unfortunately, it returns all the records.

    Is there a way to return only the records for a specific page thus avoiding
    the network overhead of passing back the entire recordset and then using
    the AbsolutePage to move the cursor.


    Thanks,

    Michael Tzoanos



  2. #2
    Jay Guest

    Re: ADO Paging


    Michael,

    A client side cursor is designed to return the entire recordset to the client.
    I've only been successful using the cache size with Server Side cursors.
    Also, your comment that ADO changes the cursor type is incorrect. I implemented
    your code with Jet and SQL Server, and the lock type stayed adLockReadOnly.
    If you want to utilize the cache size property, use a server side cursor.

    Jay

    "Michael Tzoanos" <mtzoanos@hotmail.com> wrote:
    >
    >Hi,
    >
    >I am implementing ADO Paging to limit the size of the recordset that is

    brought
    >back to the value of the Pagesize. So far, all my attempts return an entire
    >disconnected recordset
    >
    >Here is my code
    >
    >Dim objRs as ADOR.Recordset
    >
    >With
    > .CursorLocation = adUseClient
    > .CursorType = adOpenStatic
    > .LockType = adLockReadOnly (irrelevant as ADO will change it to adLockBatchClient
    >when CursorType is static)
    > .Pagesize = 20
    > .Cachesize = 20
    > .Open strSQL etc.
    >End With
    >
    >I would expect the code above to return 20 records for the single page.


    >Unfortunately, it returns all the records.
    >
    >Is there a way to return only the records for a specific page thus avoiding
    >the network overhead of passing back the entire recordset and then using
    >the AbsolutePage to move the cursor.
    >
    >
    >Thanks,
    >
    >Michael Tzoanos
    >
    >



  3. #3
    Michael Tzoanos Guest

    Re: ADO Paging


    Thanks Jay!

    Is it possible to post your code if it's available to returned only part
    of the recordset? I tried almost all permutations last Friday and I was
    unable to return a subset of the records except when using MaxRecords (unfortunately
    this returned the top records within a recordset thus not allowing paging
    beyond the number specified for the max records)

    I have another question. Let's take the following example, where we set
    the cachesize and pagesize to 20 and there are a total of 100 records returned
    by our query. If I request the first page and loop until EOF is True, should
    I hit EOF after 20th record (the size of the cache) or after the 100th record?
    So far all my recordsets have not hit EOF after the 20th record but after
    the 100th record and I have assumed that as a result I must be receiving
    the entire recordset. Does ADO transparently request more records when the
    cursor reaches the end of the cache.

    Thanks in advance,

    Michael Tzoanos

    >
    >Michael,
    >
    >A client side cursor is designed to return the entire recordset to the client.
    > I've only been successful using the cache size with Server Side cursors.
    > Also, your comment that ADO changes the cursor type is incorrect. I implemented
    >your code with Jet and SQL Server, and the lock type stayed adLockReadOnly.
    > If you want to utilize the cache size property, use a server side cursor.
    >
    >Jay
    >
    >"Michael Tzoanos" <mtzoanos@hotmail.com> wrote:
    >>
    >>Hi,
    >>
    >>I am implementing ADO Paging to limit the size of the recordset that is

    >brought
    >>back to the value of the Pagesize. So far, all my attempts return an entire
    >>disconnected recordset
    >>
    >>Here is my code
    >>
    >>Dim objRs as ADOR.Recordset
    >>
    >>With
    >> .CursorLocation = adUseClient
    >> .CursorType = adOpenStatic
    >> .LockType = adLockReadOnly (irrelevant as ADO will change it to adLockBatchClient
    >>when CursorType is static)
    >> .Pagesize = 20
    >> .Cachesize = 20
    >> .Open strSQL etc.
    >>End With
    >>
    >>I would expect the code above to return 20 records for the single page.

    >
    >>Unfortunately, it returns all the records.
    >>
    >>Is there a way to return only the records for a specific page thus avoiding
    >>the network overhead of passing back the entire recordset and then using
    >>the AbsolutePage to move the cursor.
    >>
    >>
    >>Thanks,
    >>
    >>Michael Tzoanos
    >>
    >>

    >



  4. #4
    Robert Gelb Guest

    Re: ADO Paging

    Limit does work with ClientSide cursors. Set the .MaxRecords property. As
    far as being able to bring just a specific page, the answer is NO.

    --
    Robert Gelb

    "Michael Tzoanos" <mtzoanos@hotmail.com> wrote in message
    news:396635fc$1@news.devx.com...
    >
    > Hi,
    >
    > I am implementing ADO Paging to limit the size of the recordset that is

    brought
    > back to the value of the Pagesize. So far, all my attempts return an

    entire
    > disconnected recordset
    >
    > Here is my code
    >
    > Dim objRs as ADOR.Recordset
    >
    > With
    > .CursorLocation = adUseClient
    > .CursorType = adOpenStatic
    > .LockType = adLockReadOnly (irrelevant as ADO will change it to

    adLockBatchClient
    > when CursorType is static)
    > .Pagesize = 20
    > .Cachesize = 20
    > .Open strSQL etc.
    > End With
    >
    > I would expect the code above to return 20 records for the single page.
    > Unfortunately, it returns all the records.
    >
    > Is there a way to return only the records for a specific page thus

    avoiding
    > the network overhead of passing back the entire recordset and then using
    > the AbsolutePage to move the cursor.
    >
    >
    > Thanks,
    >
    > Michael Tzoanos
    >
    >




  5. #5
    Jay Guest

    Re: ADO Paging


    Michael,

    To answer your first question, you won't hit the EOF of the recordset until
    you scroll past the 100th record, regardless of the pagesize. Think of the
    pagesize property as a way of segmenting the recordset, not slicing it.
    It's handy when you want to dump a recordset to an ASP page, and spread it
    out among several pages. For your second question, yes ADO transparently
    refreshes the Cached records once you scroll past the number of records currently
    cached on the client. If the cache size is set to 20, ADO will request the
    next 20 records. If not sure whether ADO releases the previous 20 or keeps
    them cached. I would assume that ADO releases the previous 20 records when
    requesting the next 20. Hope this helps.

    Jay

    "Michael Tzoanos" <mtzoanos@hotmail.com> wrote:
    >
    >Thanks Jay!
    >
    >Is it possible to post your code if it's available to returned only part
    >of the recordset? I tried almost all permutations last Friday and I was
    >unable to return a subset of the records except when using MaxRecords (unfortunately
    >this returned the top records within a recordset thus not allowing paging
    >beyond the number specified for the max records)
    >
    >I have another question. Let's take the following example, where we set
    >the cachesize and pagesize to 20 and there are a total of 100 records returned
    >by our query. If I request the first page and loop until EOF is True, should
    >I hit EOF after 20th record (the size of the cache) or after the 100th record?
    >So far all my recordsets have not hit EOF after the 20th record but after
    >the 100th record and I have assumed that as a result I must be receiving
    >the entire recordset. Does ADO transparently request more records when

    the
    >cursor reaches the end of the cache.
    >
    >Thanks in advance,
    >
    >Michael Tzoanos
    >
    >>
    >>Michael,
    >>
    >>A client side cursor is designed to return the entire recordset to the

    client.
    >> I've only been successful using the cache size with Server Side cursors.
    >> Also, your comment that ADO changes the cursor type is incorrect. I implemented
    >>your code with Jet and SQL Server, and the lock type stayed adLockReadOnly.
    >> If you want to utilize the cache size property, use a server side cursor.
    >>
    >>Jay
    >>
    >>"Michael Tzoanos" <mtzoanos@hotmail.com> wrote:
    >>>
    >>>Hi,
    >>>
    >>>I am implementing ADO Paging to limit the size of the recordset that is

    >>brought
    >>>back to the value of the Pagesize. So far, all my attempts return an

    entire
    >>>disconnected recordset
    >>>
    >>>Here is my code
    >>>
    >>>Dim objRs as ADOR.Recordset
    >>>
    >>>With
    >>> .CursorLocation = adUseClient
    >>> .CursorType = adOpenStatic
    >>> .LockType = adLockReadOnly (irrelevant as ADO will change it to adLockBatchClient
    >>>when CursorType is static)
    >>> .Pagesize = 20
    >>> .Cachesize = 20
    >>> .Open strSQL etc.
    >>>End With
    >>>
    >>>I would expect the code above to return 20 records for the single page.

    >>
    >>>Unfortunately, it returns all the records.
    >>>
    >>>Is there a way to return only the records for a specific page thus avoiding
    >>>the network overhead of passing back the entire recordset and then using
    >>>the AbsolutePage to move the cursor.
    >>>
    >>>
    >>>Thanks,
    >>>
    >>>Michael Tzoanos
    >>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center