ASP & SQL - need help!

[Nizar]

Hello all,

I was wondering if you could help me out with a problem that I have encountered
in ASP. Lets say I have a stored procedure in sql as follows:

create procedure dbo.nizTempSP as

insert into nizTemp(field1, field2) values (2, 3)
select * from niztemp

GO

nizTemp is a table with two fields: field1 and field2.

When I try to run this procedure in a .asp page as follows:

set rs = dbConn.Execute("nizTempSP", adCmdStoredProc)

I get the following error message:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/Hospital/Needs/debugNeedDetail.asp, line 15


However, when I run the stored procedure using SQL Query Analyzer, everything
works fine and I get back the records. But it doesn't work if I call the
stored procedure through a .asp page. Also, if I comment out the INSERT
statement then everything works fine and I get back the records. Any ideas
on why I am unable to run this procedure in a .asp page?

Thanks in advance,

Nizar

[JS]

http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=174

you should make the following change to your proc

SET NOCOUNT ON
insert into nizTemp(field1, field2) values (2, 3)
SET NOCOUNT OFF
select * from niztemp


"Nizar" <noorani_786@yahoo.com> wrote:
>
>Hello all,
>
>I was wondering if you could help me out with a problem that I have encountered
>in ASP. Lets say I have a stored procedure in sql as follows:
>
>create procedure dbo.nizTempSP as
>
>insert into nizTemp(field1, field2) values (2, 3)
>select * from niztemp
>
>GO
>
>nizTemp is a table with two fields: field1 and field2.
>
>When I try to run this procedure in a .asp page as follows:
>
>set rs = dbConn.Execute("nizTempSP", adCmdStoredProc)
>
>I get the following error message:
>
>Error Type:
>ADODB.Recordset (0x800A0E78)
>Operation is not allowed when the object is closed.
>/Hospital/Needs/debugNeedDetail.asp, line 15
>
>
>However, when I run the stored procedure using SQL Query Analyzer, everything
>works fine and I get back the records. But it doesn't work if I call the
>stored procedure through a .asp page. Also, if I comment out the INSERT
>statement then everything works fine and I get back the records. Any ideas
>on why I am unable to run this procedure in a .asp page?
>
>Thanks in advance,
>
>Nizar
>
>