VB6 using ADO, sproc with return recordset

VB6 code snippet:

with cmd
.cmdtext = "exec buildwordlist 11"
.commandtype = adcmdtext
end with
set rs = cmd.execute()
if not rs.eof then
strfield = rs!fieldname
rs.movenext
end if

the above code works just fine if the sproc is a simple select * from
tblword and returns records.

However, if the sproc is changed to include a temp table (#tempwords) for
filtering the word list before returning a recordset, it throws a 3704 error,
"operation not allowed when the object is closed". This makes no sense to me.

If I run this as a pass-through query using ODBC, it works just fine. I
want to use ADO and not ODBC.

Please suggest a solution.

Thanks,
Jim

"jim" wrote in message

 > VB6 code snippet:
 >
 > with cmd
 > .cmdtext = "exec buildwordlist 11"
 > .commandtype = adcmdtext
 > end with
 > set rs = cmd.execute()
 > if not rs.eof then
 > strfield = rs!fieldname
 > rs.movenext
 > end if
 >
 > the above code works just fine if the sproc is a simple select * from
 > tblword and returns records.
 >
 > However, if the sproc is changed to include a temp table (#tempwords) for
 > filtering the word list before returning a recordset, it throws a 3704
 > error,
 > "operation not allowed when the object is closed". This makes no sense to
 > me.
 >
 > If I run this as a pass-through query using ODBC, it works just fine. I
 > want to use ADO and not ODBC.
 >
 > Please suggest a solution.
 >
 > Thanks,
 > Jim

You either need to add SET NOCOUNT ON at the top of your stored procedure,
or walk through the returned recordset[s] by calling NextRecordset until it
returns a recordset object that has it's State property set to 1, or it
returns Nothing.

Without SET NOCOUNT ON, it returns a recordset for every SQL statement
that's executed in the procedure. With it, only statements that return rows
(or could return rows, to be more precise) generate a recordset.


-Mark