Re: adUseClient and the command object

From: William Morris (news.remove.this.and.the.dots_at_seamlyne.com)
Date: 03/30/04 

Date: Tue, 30 Mar 2004 13:07:46 -0600

I'm good with that as a Best Practices Concept, but at first glance it looks
like most other BPCs in that it's great for textbooks but doesn't solve the
Real World Problem.

Let's say that I have a table with 1500 records of vehicle make; model;
year; color; and lineid; about evenly distributed between Ford, Chevy, Olds,
Chrysler, and Dodge. The possible criteria are make and model, and the user
enters "FORD". We want to return the results 25 at a time.

SELECT TOP 25
FROM vehicles
WHERE make = 'FORD'
ORDER BY Make, Model, Year

Page 1: Cool. Now I want page 2. I've tried a method described at
ASPFaq.com that works paging into a stored procedure, but on the same query
I might get the same vehicle repeating on every page because the order of
the resultset is not reliable;

Struggling, trying to work it out.

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:O2pVtIoFEHA.4008@TK2MSFTNGP10.phx.gbl...
> 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!
> >
> >
>
>

Relevant Pages

  • Re: Didja Know?
    ... vehicle in the Nascar stable that is wholly made in the U.S. Ford, ... High risk, either physical or financial Most of the production lines require none of the three, though some are unique skills. ...
    (rec.autos.sport.nascar)
  • Re: Previous record
    ... I already have another query that orders the list of vehicles down to the ... I need the Last Battery field populated with Battery 028 ... from the battery that should be in the vehicle currently. ... > stores the date and time of the battery change. ...
    (microsoft.public.access.queries)
  • Re: Yaris diesel head gasket failure
    ... > Apologies from a newbie to this group. ... However, my query is this: ... > warranty, and I would have expected them to do the job for free if it was ... Once the vehicle is out of warranty, ...
    (alt.autos.toyota)
  • Re: adUseClient and the command object
    ... Model and Make from the user before doing the gross query. ... > FROM vehicles ... > ASPFaq.com that works paging into a stored procedure, ... >>> William Morris ...
    (microsoft.public.data.ado)
  • Re: OT: New Ford Edge will be a hot seller / and the penny arcade at the Minnesota state fair
    ... I work at a Ford/Lincoln/Merc dealer and knew about them for some ... Just like my pinball machines I bought this vehicle sight unseen. ... The Ford vehicle booth and the penny arcade. ...
    (rec.games.pinball)