Re: adUseClient and the command object
From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 03/30/04
- Next message: Dave: "Re: How do I add an index to a FoxPro table"
- Previous message: William Morris: "Re: adUseClient and the command object"
- In reply to: William Morris: "Re: adUseClient and the command object"
- Next in thread: Val Mazur: "Re: adUseClient and the command object"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 30 Mar 2004 11:49:27 -0800
My books are drawn from "real world" experience and this is a very widely
accepted practice. Use a unique identifier or other key(s) to provide
paging. I would further filter the number of cars returned by capturing the
Year, Model and Make from the user before doing the gross query. These can
be added to the WHERE clause to further limit the total rowset. I would also
consider doing a "post" sort that sorts the data after it arrives in the ASP
page to improve query performance.
SELECT TOP 25 ID, OtherColumnsYouNeed
FROM vehicles
WHERE make = @CarWanted and ID > @LastIDRead
ORDER BY Make, Model, Year
--
____________________________________
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:c4cgii$2i277d$1@ID-205671.news.uni-berlin.de...
> 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 " <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!
> > >
> > >
> >
> >
>
>
- Next message: Dave: "Re: How do I add an index to a FoxPro table"
- Previous message: William Morris: "Re: adUseClient and the command object"
- In reply to: William Morris: "Re: adUseClient and the command object"
- Next in thread: Val Mazur: "Re: adUseClient and the command object"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
