ADODB fill recrodset with stored proc

I have a ADODB recordset problem. I want to use a stored proc to retrieve a
recordset. Code goes something like:
Set SQLCmd = New ADODB.Command
Set SQLCmd.ActiveConnection = SQLConn
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = SQLConn
rs.CursorType = adOpenForwardOnly
SQLCmd.CommandText = "usp_RPT_PForm_SetWhereTableBooks"
SQLCmd.CommandType = adCmdStoredProc
'*** Set up parameters
SQLCmd.Parameters.Refresh
SQLCmd.Parameters(1) = "aaa"
…
SQLCmd.Parameters(6) = 0
Set rs = SQLCmd.Execute
The proc usp_RPT_PForm_SetWhereTableBooks should returns a recordset
Pre tests:
I know the connection is active because when I call
SQLCmd.Parameters.Refresh the parameter list (of 6 items) are created.
I have copied the parameters into the sql query analyzer and records are
returned
The problem is that the recordset is not being filled
Can you tell me what I am missing please.
Thanks in advance,
Stewart RogersDo you have "Set NoCOunt On"
at the beginning of your Stored Proc '
If Not, add it and try again...
"Datasort" wrote:

> I have a ADODB recordset problem. I want to use a stored proc to retrieve
a
> recordset. Code goes something like:
>
> Set SQLCmd = New ADODB.Command
> Set SQLCmd.ActiveConnection = SQLConn
> Set rs = New ADODB.Recordset
> Set rs.ActiveConnection = SQLConn
> rs.CursorType = adOpenForwardOnly
>
> SQLCmd.CommandText = "usp_RPT_PForm_SetWhereTableBooks"
> SQLCmd.CommandType = adCmdStoredProc
> '*** Set up parameters
> SQLCmd.Parameters.Refresh
> SQLCmd.Parameters(1) = "aaa"
> …
> SQLCmd.Parameters(6) = 0
> Set rs = SQLCmd.Execute
> The proc usp_RPT_PForm_SetWhereTableBooks should returns a recordset
> Pre tests:
> I know the connection is active because when I call
> SQLCmd.Parameters.Refresh the parameter list (of 6 items) are created.
> I have copied the parameters into the sql query analyzer and records are
> returned
> The problem is that the recordset is not being filled
> Can you tell me what I am missing please.
> Thanks in advance,
> Stewart Rogers
>|||try this:
Dim rs As Adodb.Recordset
Set rs = new ADODB.Recordset
SQLConn.usp_Rpt_PFrom_SetWhereTableBooks "aaa", rs
I bet that works.
Greg Jackson
Portland, OR|||That did it ... Thanks!!! Can you tell me why it works with nocount on?
"CBretana" wrote:
> Do you have "Set NoCOunt On"
> at the beginning of your Stored Proc '
> If Not, add it and try again...
>
> "Datasort" wrote:
>|||Datasort...
Yes, what's going on is that SQL Server sends the Record count information
ahead of the actual recordset... and ADO is not smart enough to tell the
difference, so it's trying to "Read" the record count info as your recordset
,
and failing... It couldn' be coded to figure this out, because ADO 2.x added
functionality to handle multiple recordsets in one call to the server.
There's a method (on the RecordSet Object) in ADO called .NextRecordset, tha
t
moves to the next one in the sequence, until it retruns null when you call i
t
on the last one.
"Datasort" wrote:
> That did it ... Thanks!!! Can you tell me why it works with nocount on?
> "CBretana" wrote:
>|||And, obviously, adding Set NoCount On, removes this information from the TDS
(Tabular Data Stream), and then all that's sent backto ADO Is the actual
recordset...
"Datasort" wrote:
> That did it ... Thanks!!! Can you tell me why it works with nocount on?
> "CBretana" wrote:
>|||Odd, horribly odd!
That sounds like a bug since the record count is not returned (or was not
returned) as a recordset, it is returned as a message AFAIK. Further, the
record count is returned after the requested recordset, not before since it
doesn't know the count until it has returned the rerdorset.
So, I would prefer to see this regarded as a BIG BAD BUG!
Comments?
- Tim
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:C6A14044-735D-4DD7-802D-1BB719A4A40F@.microsoft.com...
> Datasort...
> Yes, what's going on is that SQL Server sends the Record count information
> ahead of the actual recordset... and ADO is not smart enough to tell the
> difference, so it's trying to "Read" the record count info as your
> recordset,
> and failing... It couldn' be coded to figure this out, because ADO 2.x
> added
> functionality to handle multiple recordsets in one call to the server.
> There's a method (on the RecordSet Object) in ADO called .NextRecordset,
> that
> moves to the next one in the sequence, until it retruns null when you call
> it
> on the last one.
> "Datasort" wrote:
>|||this is common and well documented behavior in ado
GAJ|||So its a "feature" then?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23nc7lqYKFHA.3420@.tk2msftngp13.phx.gbl...
> this is common and well documented behavior in ado
>
> GAJ
>