sp_executesql Operation is not allowed when the object is closed

[cliverama]

help! fried brains....

asp calling a sqlserver7 stored proc which dynamically builds a
sqlstatement & passes it to sp_executesql
asp page gives the operation not allowed when object is closed error
this is the asp code:

Set connInc= server.CreateObject("ADODB.Connection")
connInc.Open "DSN=db_database;User ID=userid;Password=xxxxxx"
Set rsInc= server.CreateObject("ADODB.Recordset")
strSQL = "usp_incmpwfilter_rs 'M9S','2','1','A,B'" '-these are
string variables passed dependant on asp form selections.
rsInc.Open strSQL, connInc
if not rsInc.eof then ^falls over here

usp_incmpfilter_rs builds a dynamic sql statement which works in query
analyser and passes it to sp_executesql:

CREATE PROCEDURE usp_incmpwfilter_rs
(
@strPeriodID varchar ,
@intLevelDetailID varchar,
@intLevelReportID varchar,
@strFilters varchar
)
AS

set nocount on

declare @strSQL nvarchar

set @strSQL = " SELECT rather complex dynamically built but working in
query analyser SQL statement containing @params goes here: can post if
requested"

execute sp_executesql @strSQL

what am i missing? i have had right nitemare getting this far!!!! any
help much appreciated..

 

[Aaron Bertrand [MVP]]

Set connInc= server.CreateObject("ADODB.Connection")

connInc.Open "DSN=db_database;User ID=userid;Password=xxxxxx"
Set rsInc= server.CreateObject("ADODB.Recordset")
strSQL = "usp_incmpwfilter_rs 'M9S','2','1','A,B'" '-these are
string variables passed dependant on asp form selections.
rsInc.Open strSQL, connInc
if not rsInc.eof then ^falls over here

You might have better luck trapping EOF by using the connection object
instead of an explicit ADODB.Recordset.

Set connInc = CreateObject("ADODB.Connection")
connInc.Open "DSN=db_database;User ID=userid;Password=xxxxxx"
' consider dropping the DSN! See www.aspfaq.com/2126
strSQL = "usp_incmpwfilter_rs 'M9S','2','1','A,B'"
set rsInc = connInc.execute(strSQL)
if not rsInc.EOF then
response.write "there are rows"
else
response.write "there are no rows"
end if

set @strSQL = " SELECT rather complex dynamically built but working in
query analyser SQL statement containing @params goes here: can post if
requested"

As complex is it might be, we're generally pretty smart people, and can
figure it out. Also, could you

PRINT @strSQL

And show us that? (The result, I would think, is far more important than
the method.) It might be that your SQL is malformed, etc.

Also, does this return 1 resultset, 5 resultsets, is it ever possible that
it could be EOF?

microsoft.public.inetserver.asp.general dropped from followups.