Re: Opening parameterized ADO Recordset with Dynamic/Keyset cursor typ
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Fri, 20 Jul 2007 07:10:02 -0400
Marius wrote:
I always use parameterized queries to access the database by using
the ADO Command object. When I want to open a recordset I use
Command.Execute. This gives me a static recordset, wich is sufficient
most of the time.
Unless you've set the Connection's CursorLocation property to adUseClient,
you are actually getting a forward-only recordset, which is the default
cursor type when cursorlocation is set to adUseServer (the default when it
is not specified) and cursortype is not specified. If you've set the
cursorlocation to adUseClient, then of course, the only cursor type you will
get will be static.
Now I want to open a recordset with a dynamic or keyset cursor type,
using the folling code :
Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open <oCommand>, , 1, 3 '(keyset cursor/optimistic locking)
This works fine, but a LEFT OUTER JOIN in the query behaves like an
INNER JOIN !?
When I create a recordset with exactly the same query and parameters
like ...
Set Rs = <oCommand>.Execute
... the LEFT OUTER JOIN is correctly executed.
Is there a way to open a Recordset with parameterized query and
dynamic/keyset cursor type without this 'bug'?
Maybe there's just something wrong with the SQL statement, but why
would the same statement behave differently when used in the 2 ways
mentioned above?
I have never run into this (I typically use stored procedures) so I am going
to have to go give this a try. I will get back to you. It will take a while
because i want to try it against SQL7, SQL2000 and SQL2005 to see if it's a
version-specific bug.
In the meantime, you might want to check out this post to see a technique to
use parameterized queries and Command objects without bothering with the
Parameters collection. It involves using a variant array to pass the
parameter values to the Command object, rather than explicitly creating the
Parmeter objects and appending them to the Parameters collection, which, in
vbscript, is overkill (IMO):
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
More info :
Environment: WinXP/Win2003 Server, classic ASP, ADO 2.8, SQL Server
2005 database
Out of curiosity, in an ASP environment, why would you ever need a dynamic
or keyset cursor? The need for either of these cursor types is typically
predicated on the idea that you are planning to keep the cursor open long
enough to worry about what other users are doing to the data. That is the
only advantage that dynamic and keyset cursors have over forward-only and
static cursrs: the fact that they will reflect changes to the data done by
other users. In ASP, where the best practice is to get in and out of the
database as quickly as possible, dynamic/keyset cursors are never needed, at
least in my experience.
Code:<snip>
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
.
- Prev by Date: Re: Execute SP async with ability to stop execution
- Next by Date: Re: Data Project - Running SP via ADO
- Previous by thread: Execute SP async with ability to stop execution
- Next by thread: Re: Opening parameterized ADO Recordset with Dynamic/Keyset cursor
- Index(es):
Relevant Pages
|
|
