ADO/Stored Procedure Problem

[James Arnall]

After creating a new recordset and setting the cursor
type to AdOpenStatic, I call a stored procedure thru the
recordset's Open() method, and the cursor type is set to
AdOpenForwardOnly. Any idea what's going on or how to
get around this?

[Ray at]

Did you define your variable of AdOpenStatic to 3?

Ray at work

"James Arnall" <jamesarnall@msn.com> wrote in message
news:0c8c01c361c6$ff3f1910$a001280a@phx.gbl...

> After creating a new recordset and setting the cursor
> type to AdOpenStatic, I call a stored procedure thru the
> recordset's Open() method, and the cursor type is set to
> AdOpenForwardOnly. Any idea what's going on or how to
> get around this?

[Bob Barrows]

James Arnall wrote:

> After creating a new recordset and setting the cursor
> type to AdOpenStatic, I call a stored procedure thru the
> recordset's Open() method, and the cursor type is set to
> AdOpenForwardOnly. Any idea what's going on or how to
> get around this?

1. Why do you need a static cursor? Usually a forward-only cursor is
sufficient for use in asp.
2. We need to see the code. FWIW, this code results in a static cursor for
me (assuming you have the ADO constants defined):
set rs = server.createobject("adodb.recordset")
rs.open "proc_name", cn, adOpenStatic,,adCmdStoredProc

If you need a client-side static cursor, then set the cursorlocation
property of the rs object to adUseClient before calling the Open method.

Bob Barrows

[James Arnall]

Yes, I am setting the CursorType to AdOpenStatic. Alternately, I tried
setting it to 3:

SearchRS.CursorType = AdOpenStatic
-0r-
SearchRS.CursorType = 3

After calling SearchRS.Open, SearchRS.CursorType now = 0.

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[James Arnall]

Normally I would stick to a forward-only cursor, but unfortunately I
have to include legacy code that uses attributes of static cursors, such
as RecordCount, PageSize, bookmarks, etc.

The code you included is, essentially, how I'm opening the recordset. I
just discovered that it works (that is, that the recordset retains the
static cursor) when my stored procedure contains only a straightforward
"SELECT", but not when I declare custom variables.

Thanks for your help!

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

James Arnall wrote:

> Normally I would stick to a forward-only cursor, but unfortunately I
> have to include legacy code that uses attributes of static cursors,
> such as RecordCount, PageSize, bookmarks, etc.
>
> The code you included is, essentially, how I'm opening the recordset.
> I just discovered that it works (that is, that the recordset retains
> the static cursor) when my stored procedure contains only a
> straightforward "SELECT", but not when I declare custom variables.
>

Please provide an example. I'm not sure what you mean by "custom variables".

Bob Barrows

[Bob Barrows]

James Arnall wrote:

> Normally I would stick to a forward-only cursor, but unfortunately I
> have to include legacy code that uses attributes of static cursors,
> such as RecordCount, PageSize, bookmarks, etc.
>
> The code you included is, essentially, how I'm opening the recordset.
> I just discovered that it works (that is, that the recordset retains
> the static cursor) when my stored procedure contains only a
> straightforward "SELECT", but not when I declare custom variables.
>

PS. Again, if you set the recordset's CursorLocation to adUseClient before
opening it, you will ALWAYS get a static cursor.

Bob

[James Arnall]

If my stored procedure is defined as follows...

CREATE sp_MyProc AS

SELECT * FROM TABLE

GO

... then the recordset I open using this stored proc, if set with a
static cursor, will retain the static cursor. However, if I redefine my
procedure as follows...

CREATE sp_MyProc AS

DECLARE @x int

SELECT * FROM TABLE

GO

... the recordset will switch to a forward only cursor.

Does this answer your question?

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[James Arnall]

Sorry, I should have specified which database. I am using SQL Server 7.
And declaring SET NOCOUNT ON does not help. In fact, simply adding SET
NOCOUNT ON without declaring any local SQL variables will reset the
cursortype to forward-only when the recordset is opened. Very puzzling
indeed.

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

James Arnall wrote:

> Sorry, I should have specified which database. I am using SQL Server
> 7. And declaring SET NOCOUNT ON does not help. In fact, simply
> adding SET NOCOUNT ON without declaring any local SQL variables will
> reset the cursortype to forward-only when the recordset is opened.

Even when you set cursorlocation to adUseClient? Impossible.

> Very puzzling indeed.
>

Well, I am out of answers until you provide a repro script with an example
stored procedure that causes the problem, along with the create table
statement for the table used by your procedure, and the actual asp code used
to call the procedure.

Sorry,
Bob barrows