Re: ADODB.Recordset (0x800A0E78) - Operation is not allowed when the object is closed.

From: Val Mazur (group51a_at_hotmail.com)
Date: 02/28/04 

Date: Sat, 28 Feb 2004 14:11:29 -0500

Hi Julio,

Try to add SET NOCOUNT ON as a very first statement inside of your SP. It
should help. Another suggestion, which is not related to your problem, is to
separate declaration and instantiating of the ADO connection. Use next kind
of code, instead of yours, otherwise it could lead to memory leaks

Dim loConnection As ADODB.Connection
Set loConnection = New ADODB.Connection 

-- 
Val Mazur
Microsoft MVP
"Julio" <kotroneus@hotmail.com> wrote in message 
news:27E325CF-CA01-4BA8-BA88-C221B1ED0FEA@microsoft.com...
> Hi, i have an aruitecture in three layers (Com+ component)  based in an 
> ASP app, The database ius SQL Server 2000, and all the queries are stored 
> al stored procedures
> I have a DB Layer to communicate with the Database, this an example of a 
> function to call a SP with parameters to get a RS.
> Public Function GFQueryStoredProcedureParameter_RS(ByVal lsStoredProcedure 
> As String, _
>                                                ByRef loParameters() As 
> String) _
>                                                As ADODB.Recordset
>
>    On Error GoTo ErrorHandler
>
>    Dim loConnection As New ADODB.Connection
>    Dim loCommand As ADODB.Command
>    Dim loRecordset As ADODB.Recordset
>
>    Dim lbRet As Boolean
>    Dim liContador As Integer
>    Dim liErrores As Integer
>    Dim loLog As LogManager.Log
>    Dim loContext As ObjectContext
>
>    Set loContext = GetObjectContext()
>
>    If loContext Is Nothing Then
>        Set loLog = CreateObject("LogManager.Log")
>        Set loConnection = CreateObject("ADODB.Connection")
>        Set loCommand = CreateObject("ADODB.Command")
>        Set loRecordset = CreateObject("ADODB.Recordset")
>    Else
>        Set loLog = loContext.CreateInstance("LogManager.Log")
>        Set loConnection = loContext.CreateInstance("ADODB.Connection")
>        Set loCommand = loContext.CreateInstance("ADODB.Command")
>        Set loRecordset = loContext.CreateInstance("ADODB.Recordset")
>    End If
>
>    loConnection.Open GFGetConnectionString
>
>    loCommand.ActiveConnection = loConnection
>    loCommand.CommandType = adCmdStoredProc
>    loCommand.CommandText = lsStoredProcedure
>    loCommand.CommandTimeout = 15
>
>    Dim liItem As Integer
>    For liItem = 0 To UBound(loParameters)
>        ' Creamos los parámetros para mandarselos al procedimiento 
> almacenado.
>        Dim loParameter As New Parameter
>        Set loParameter = loCommand.CreateParameter(CStr(loParameters(0, 
> liItem)), adInteger, adParamInput, 4, CInt(loParameters(1, liItem)))
>        loCommand.Parameters.Append loParameter
>    Next
>
>    Set loRecordset = loCommand.Execute
>
>    If (loConnection.Errors.Count <> 0) Then
>        For liContador = 0 To loConnection.Errors.Count - 1
>            'Los errores del 0 al 10 son informativos.
>            If (loConnection.Errors.Item(liContador).Number > 
> SQL_INFORMATION) Then
>                Call 
> loLog.GFAdd(loConnection.Errors.Item(liContador).Number, 
> loConnection.Errors.Item(liContador).Description, "busGTKI.dll 
> [modBase.bas (Function: GFQueryStoredProcedureParameter)]", ERROR_TYPE)
>                liErrores = liErrores + 1
>            Else
>                Call 
> loLog.GFAdd(loConnection.Errors.Item(liContador).Number, 
> loConnection.Errors.Item(liContador).Description, "busGTKI.dll 
> [modBase.bas (Function: GFQueryStoredProcedureParameter)]", 
> INFORMATION_TYPE)
>            End If
>        Next
>    End If
>
>    If (liErrores <> 0) Then
>        loLog.GFDump
>        If Not loContext Is Nothing Then
>            loContext.SetAbort
>        End If
>    Else
>        Set GFQueryStoredProcedureParameter_RS = loRecordset
>        If Not loContext Is Nothing Then
>            loContext.SetComplete
>        End If
>    End If
>
>
>    Set loConnection = Nothing
>    Set loLog = Nothing
>    Set loContext = Nothing
>    Set loCommand = Nothing
>    Set loParameter = Nothing
>    Set loRecordset = Nothing
>
>    Exit Function
>
> ErrorHandler:
>    lbRet = loLog.GFAdd(Err.Number, Err.Description, "busGTKI.dll 
> [modBase.bas (Function: GFQueryStoredProcedureParameter)]", ERROR_TYPE)
>    If (lbRet = True) Then
>        loLog.GFDump
>    End If
>
>    If Not loContext Is Nothing Then
>        loContext.SetAbort
>    End If
>
>    Set loConnection = Nothing
>    Set loLog = Nothing
>    Set loContext = Nothing
>    Set loCommand = Nothing
>    Set loParameter = Nothing
>    Set loRecordset = Nothing
>
> End Function
>
> At the second layer i have many functions/functionality, like this:
> Public Function GFGetOffersByUser( _
>                                ByVal liPersona As Integer, _
>                                ByVal liState As Integer, _
>                                ByVal lsOrder As String, _
>                                ByVal lsLang As String _
>                                ) As ADODB.Recordset
>    On Error GoTo ErrLabel
>
>    Dim oMtsCtx As Object
>    Set oMtsCtx = GetObjectContext()
>
>    Dim loArrOferta
>    loArrOferta = Array(Array("@iPersona", adInteger, adParamInput, 4, 
> liPersona), _
>                        Array("@iEstado", adInteger, adParamInput, 4, 
> liState), _
>                        Array("@sOrder", adVarChar, adParamInput, 10, 
> lsOrder), _
>                        Array("@sLang", adChar, adParamInput, 1, lsLang))
>
>    Set GFGetOffersByUser = GFExecSPParam("GetOffersByUser_sp", 
> loArrOferta)
>
>    If Not oMtsCtx Is Nothing Then
>      oMtsCtx.SetComplete
>    End If
>
>    Set oMtsCtx = Nothing
>
>    Exit Function
>
> ErrLabel:
>    If Not oMtsCtx Is Nothing Then
>      oMtsCtx.SetAbort
>    End If
>    Err.Raise Err.Number, Err.Source & " -> busGTKI.GFGetOffersByUser", 
> Err.Description
> End Function
>
> And at the ASP
> Dim loRSOfertas 'As ADODB.Recordset
> Set loRSOfertas = Server.CreateObject("adodb.recordset")
> Dim loRSFilter 'As ADODB.Recordset.
> Set loRSFilter = Server.CreateObject("adodb.recordset")
>
> lsTitle = "Todas las Ofertas"
>
> ' Recogemos en un recordset todas las ofertas.
> Set loSICOE = Server.CreateObject("busGTKI.busRRHH")
>
> If (lsOrder <> "") Then
> Set loRSFilter = loSICOE.GFGetOffersByUser (CInt(liPersona), CInt(0), 
> CStr(lsOrderBy), CStr(lsLanguage))
> Else
> Set loRSFilter = loSICOE.GFGetOffersByUser(CInt(liPersona), CInt(0), 
> "fec", CStr(lsLanguage))
> End If
>
> Set loSICOE = Nothing
>
> If (loRS_Ofertas.EOF) AND (loRS_Ofertas.BOF) Then
> lbRows = false
> Else%><input Type="text" Id="lstOffers" name="lstOffers" /><%
> lbRows = true
> liRows = 0
>
> 'Recorremos el Recordset y "pintamos" los resultados.
> Do While Not(loRS_Ofertas.EOF)
> If (liRows Mod 2 = 0) Then
> lsColor = "#FFFFFF"
> Else
> lsColor = "#E7E3E7"
> End If%><tbody><tr><td class="texto" align="center" 
> bgcolor="<%=lsColor%>"><a 
> href="Detailoferta.asp?iOferta=<%=loRS_Ofertas("IOFERTA")%>" title="Ver 
> detalle de la Oferta"><%=loRS_Ofertas("IOFERTA")%></a></td><td 
> bgcolor="<%=lsColor%>" align="center" class="texto"><a 
> href="Detailoferta.asp?iOferta=<%=loRS_Ofertas("IOFERTA")%>" title="Ver 
> detalle de la 
> Oferta"><%=loRS_Ofertas("SDPUESTO")%></a></td></tr></tbody><%liRows = 
> liRows + 1
> loRS_Ofertas.MoveNext
> Loop
> End If%>
>
> SomeOne have any idea, i am getting crazy
>
> Thanks in advance
>
> (Note: i have test the component with a Win32 app and works fine.)
>
> Julio
> (CGE&Y)