Re: adUseClient and the command object

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 03/30/04 

Date: Tue, 30 Mar 2004 10:09:41 -0800

I think you're barking up the wrong tree.
It's far more efficient to fetch just enough rows to fill the current form
and optimize the query/database/indexes to fetch these smaller resultsets as
needed. That way you'll create a far more scaleable application--that won't
time out.
Use the TOP query to limit the number of rows to 40 or so and the WHERE
clause to focus the resultset to range from set to set. 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"William Morris" <news.remove.this.and.the.dots@seamlyne.com> wrote in
message news:c4c7r3$2gn2j0$1@ID-205671.news.uni-berlin.de...
> I'm using a method for paging through results I got from
4GuysFromRolla.com,
> so:
>
>  set rs = Server.CreateObject("ADODB.Recordset")
>  'Set the cursor location property
>  rs.CursorLocation = adUseClient
>  'Set the cache size = to the # of records/page
>  rs.CacheSize = NumPerPage
>  rs.open sSQL, conn
>  if rs.eof and rs.bof then
>   response.write "&nbsp;<P><div align=""center""><span style='padding:
10px;
> border: 2px solid #ff0000;'>There are no vehicles in our inventory that
> match the criteria you entered.</span></div>"
>  else
>   rs.moveFirst
>   m_totalRows = rs.recordcount
>   rs.pageSize = NumPerPage
>   m_TotalPages = rs.PageCount
>   rs.AbsolutePage = m_currentPage
>   outputArray = rs.GetRows
>
> It works very well as long as the query doesn't take so long that it times
> out, which it sometimes does.  I tried using the command object along with
> the code above so I could specify a CommandTimeout, as in:
>
>  set cmd = server.createobject("adodb.command")
>  cmd.activeconnection = conn
>  cmd.CommandText = sSQL
>  cmd.commandTimeout = 120
>
>  set rsResults = Server.CreateObject("ADODB.Recordset")
>  'Set the cursor location property
>  rsResults.CursorLocation = adUseClient
>  'Set the cache size = to the # of records/page
>  rsResults.CacheSize = NumPerPage
>  set rsResults = cmd.execute
>   rsResults.moveFirst
>   m_totalRows = rsResults.recordCount
>   rsResults.pageSize = NumPerPage
>   m_totalPages = rsResults.pageCount
>   m_currentPage = GetCurrentPageNumber
>   rsResults.AbsolutePage = m_currentPage
>   dim outputArray, tmpRowCounter
>   tmpRowCounter = 1
>   outputArray = rsResults.getRows
>
> ...but I get the following error: " Arguments are of the wrong type, are
out
> of acceptable range, or are in conflict with one another. " on this line:
>
> rsResults.AbsolutePage = m_currentPage
>
> Is there a way to get where I want to go, which is to say, run a long
query
> without timing out?
>
> -- 
> William Morris
> Semster, Seamlyne reProductions
> Visit our website, http://www.seamlyne.com, for the most comfortable
> historically inspired clothing you can buy!
>
>