question on ASP calling stored procedure

I'm trying to pass a recordset from an stored procedure to an ASP.
Here's the ASP code:
Dim cnn, cmd, rs
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB;Driver={SQL Server};Server=..."
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cnn
cmd.CommandText = "SP_Name"
cmd.CommandType = adCmdStoredProc
Set rs = cmd.Execute
if rs.bof or rs.eof then
response.write "eof<BR>"
end if
This works fine with the following stored procedure:
CREATE procedure get_recordset
as
select * from mytable
GO
But when I try to do additional stuff in the stored procedure before
selecting, like:
CREATE procedure get_recordset
as
declare @.TableVar TABLE (
field1 varchar(20) null,
field2 decimal(10, 2) null
)
insert into @.TableVar values('hello', 12.2)
select * from @.TableVar
The result on the ASP when asking for BOF/EOF is: Operation is not
allowed when the object is closed. Yet, if I run that stored procedure
from query analyzer, I get the record back. Am I doing something
wrong?
thanks,
JoeUse SET NOCOUNT ON in the stored procedure.
CREATE PROCEDURE dbo.doSomething
AS
BEGIN
SET NOCOUNT ON
-- do stuff
END
GO
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
<joseph.fanelli@.vba.va.gov> wrote in message
news:1114097950.793760.84800@.g14g2000cwa.googlegroups.com...
> I'm trying to pass a recordset from an stored procedure to an ASP.
> Here's the ASP code:
> Dim cnn, cmd, rs
> Set cnn = Server.CreateObject("ADODB.Connection")
> cnn.Open "Provider=SQLOLEDB;Driver={SQL Server};Server=..."
> Set cmd = Server.CreateObject("ADODB.Command")
> cmd.ActiveConnection = cnn
> cmd.CommandText = "SP_Name"
> cmd.CommandType = adCmdStoredProc
> Set rs = cmd.Execute
> if rs.bof or rs.eof then
> response.write "eof<BR>"
> end if
> This works fine with the following stored procedure:
> CREATE procedure get_recordset
> as
> select * from mytable
> GO
> But when I try to do additional stuff in the stored procedure before
> selecting, like:
> CREATE procedure get_recordset
> as
> declare @.TableVar TABLE (
> field1 varchar(20) null,
> field2 decimal(10, 2) null
> )
> insert into @.TableVar values('hello', 12.2)
> select * from @.TableVar
> The result on the ASP when asking for BOF/EOF is: Operation is not
> allowed when the object is closed. Yet, if I run that stored procedure
> from query analyzer, I get the record back. Am I doing something
> wrong?
> thanks,
> Joe
>|||If you don't want to change stored procedure ( wich is better solution )
you can add line of code after cmd.Execute :
Set rs = rs.NextRecordset
...
and then check for EOF
Hope it helps
"joseph.fanelli@.vba.va.gov" wrote:

> I'm trying to pass a recordset from an stored procedure to an ASP.
> Here's the ASP code:
> Dim cnn, cmd, rs
> Set cnn = Server.CreateObject("ADODB.Connection")
> cnn.Open "Provider=SQLOLEDB;Driver={SQL Server};Server=..."
> Set cmd = Server.CreateObject("ADODB.Command")
> cmd.ActiveConnection = cnn
> cmd.CommandText = "SP_Name"
> cmd.CommandType = adCmdStoredProc
> Set rs = cmd.Execute
> if rs.bof or rs.eof then
> response.write "eof<BR>"
> end if
> This works fine with the following stored procedure:
> CREATE procedure get_recordset
> as
> select * from mytable
> GO
> But when I try to do additional stuff in the stored procedure before
> selecting, like:
> CREATE procedure get_recordset
> as
> declare @.TableVar TABLE (
> field1 varchar(20) null,
> field2 decimal(10, 2) null
> )
> insert into @.TableVar values('hello', 12.2)
> select * from @.TableVar
> The result on the ASP when asking for BOF/EOF is: Operation is not
> allowed when the object is closed. Yet, if I run that stored procedure
> from query analyzer, I get the record back. Am I doing something
> wrong?
> thanks,
> Joe
>|||Aaron/Sergey,
Thanks, both of your suggestions worked.