ADO Paging

[Michael Tzoanos]

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

[Jay]

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
>
>

[Michael Tzoanos]

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
>>
>>
>

[Robert Gelb]

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
>
>

[Jay]

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
>>>
>>>
>>
>