Hi all,

I m new to asp, to Ms-sql and this forum.

I am getting the following error while executing a asp page

"ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed. "

I am havin a sub in asp code that call a stored procedure.. the Stored procedure return either 1 or 0. this is to be validated in the SUB. I get the error in "IF rstBandReqStatus.EOF THEN..." line.

The sub code is as follows:-

================================================== =======
Sub subCheckForPendingRequests(strRequestNo)

DIM rstBandReqStatus
DIM strSqlBandReqDetails
DIM strFlag



    SET objCommand = Session("ObjDBConnect").CreateCommand
    CALL OutputDebugFile(1, "CPSConnection")
    objCommand.ActiveConnection = Session("objConn")
       SET rstBandReqStatus = Session("ObjDBConnect").CreateRecordset

    objCommand.CommandText = "testingDB..spCPSCheckForPendingBandRetReq"
    objCommand.CommandType = adCmdStoredProc
    objCommand.Parameters.Append objCommand.CreateParameter("intRequestNumber",adIn teger, adParamInput,9, strRequestNo)

    rstBandReqStatus.Open objCommand,,adOpenStatic



    'If there is any error while executing the SP, redirect to Error page.
    IF err.number <>0 THEN
        SET rstBandReqStatus = NOTHING
        SET objCommand = NOTHING
        IF instr(1,Err.description,"CPS")>0 THEN
            Response.Redirect "CPSError.asp?ErrorCode=CPS_NOT_CURR"
        END IF
         CALL OutputDebugFile(0, "CPSConnection")
        Response.Redirect "CPSError.asp?ErrorCode=CPS_REQ_DET_SEL_FAIL&Param eters=RequestDetails~" & strRequestNo
    END IF

    IF rstBandReqStatus.EOF THEN
        SET rstBandReqStatus = NOTHING
        SET objCommand = NOTHING
         CALL OutputDebugFile(0, "CPSConnection")
         Response.Redirect "CPSError.asp?ErrorCode=CPS_NO_REQ_DET&Prameters=R equestDetails~" & strRequestNo
    END IF

    strFlag = rstBandReqStatus("@intFlag")

    rstBandReqStatus.Close
    SET rstBandReqStatus = NOTHING
    SET objCommand = NOTHING
    CALL OutputDebugFile(0, "CPSConnection")

    if strFlag<>0 then
        Response.Redirect "CPSError.asp?ErrorCode=CPS_SURRENDER_REQUEST_EXIS TS"
    ELSE
        CALL subGetBandRequestDetails(strRequestNo)
    END IF

End Sub
-----------------------------------
--------------------------------

The stored procedure is as follows



CREATE procedure alphaone.spCPSCheckForPendingbandRetReq
(@intRequestNumber int)
AS
SET NOCOUNT ON

DECLARE @intFlag int --is 1 if the requests or activeband connection exists
DECLARE @txtEmpNo varchar(25)
--DECLARE @txtReturnReqStatus varchar(25)

SET @intFlag=0 --By default there is no pending surrender request

/* Getting the employeeID for the request using the request-number */
SELECT @txtEmpNo= a.txtEmpNo FROM TestingDB.alphaone.CPSTrnbandRequest a WITH (NOLOCK) ,
        TestingDB.alphaone.CPSTrnbandRequest b WITH (NOLOCK)
WHERE a.txtEmpno=b.txtEmpno
AND a.intReqNo = @intRequestNumber



/* Checking if active connection already exists or return-request on active connection */
If exists (SELECT 1 from TestingDB.alphaone.CPSTrnbandRequest WITH (NOLOCK)
WHERE txtEmpno=@txtEmpno and txtNewReqStatus = 'D' and (txtRetReqStatus = 'A' OR txtRetReqStatus='C'))
BEGIN
SET @intFlag=1
END

Else
/* In case on non-existence of active connection already exists or return-request on active connection */
BEGIN
SET @intFlag=0
END


/* Pass the @intFlag value to the webpage */
BEGIN
return @intFlag

END
GO
==================================================


Please help me:(

I think I know what the problem is, but don't know how to solve it. It looks the asp page refuses to take @intFlag from the storeprocedure. I don't know what changes i have to do to for this. I tried changin from return @intflag to select @intFlag, even that is not accepted.
After some changes in Asp, i get the following error "Item cannot be found in the collection corresponding to the requested name or ordinal. "

I tried changing the Stored procedure to
CREATE procedure misuser.spCPSCheckForPendingBroadbandRetReq
(@intRequestNumber int, @intFlag int OUTPUT)
That doesn't solve it either...

Wonder what is wrong?

i found the solution.

instead of sending the variable as select @intFlag

i use select @intFlag as strflag as output value.:D