Fill a vb6 recordset from an sql server 2005 stored procedure

[mcs1000]

I am trying to fill a recordset from a stored procedure.

The problem I have is that I want to include an IF...ELSE
statement in my stored procedure.

When I have an IF...ELSE clause and two or more SELECT statements in my stored procedure I cannot retrieve the data from the database into a recordset.

The error message I get is "The rowset is not bookmarkable"

The recordset fills correctly when my SP only contains a single SELECT statement.

When I try to use the stored procedure with the IF..ELSE statement I can tell that the recordset has loaded by looking at the 'FIELDS' property of the recordset object but the recordcount is '-1' as if no records where returned.

I'd appreciate any advice to fix this as I have had the same problem a couple of times.

 

[gmmastros]

At the beginning of the stored procedure, put....

SET NOCOUNT ON

-George

"the screen with the little boxes in the window." - Moron

 

[mcs1000]

No joy I'm afraid

 

[BobRodes]

Change the recordset type to one that's bookmarkable, then. If you're using ADO, then just make it a client-side recordset.

 

[mcs1000]

The recordset type is adOpenKeyset which is a bookmarkable recordset.

When I comment out the IF...ELSE statements and only return one select statement the recordsets bookmark property is set correctly.

I am using an existing function to execute the stored procedure(not written by me) which I'd rather not change as it would require testing a lot of existing functionality.

 

[mcs1000]

Changed cursor location from adUseServer to adUseClient and it worked. Not ideal but at least it is usable.

Thanks for your advice dudes

 

[BobRodes]

Actually, you might try adOpenStatic if you want to keep the recordset on the server side.

Also, client-side recordsets have become the first choice these days--ADO.Net defaults to them. For this reason I'm curious as to why you say it's not ideal. Would you mind explaining?

Bob