Disconnected recordsets, MTS & Connection pooling
msuk
I have an MTS Component (DAL) which creates and disconnected
recordset and pass it to another MTS Component (CLIENT).
The problem I am experiencing is that the client MTS component
sometimes cannot access properties of the disconnected recordset
like the .EOF but other times it can even when the same stored
procedure and parameters are used.
The error I keep on getting is '3704 Operation is not allowed
when the object is closed'.
I have made sure that all my stored procedures are using 'SET
NOCOUNT ON'
Data Access Layer Code (DAL)
Public Function GetRecordSet(.. some Params..) as ADODB.Recordset
Set oCmdSQL = createobject("ADODB.Command")
... Code here creates command object and sets the parameters...
' Open the connection to the database
70 Set moCon = CreateObject("ADODB.Connection")
71 With moCon
72 .ConnectionString = sConnection
73 .CursorLocation = adUseClient
74 .Open
75 End With
76 ' Execute the SQL
77 With oCmdSQL
78 .ActiveConnection = moCon
79 .CommandText = vsSQL
80 .CommandTimeout = vlTimeOut
81 .CommandType = viCommandType
82 End With
83 Set oRec = CreateObject("ADODB.Recordset")
84 oRec.CursorLocation = adUseClient
85 ' Create a disconnected recordset
86 oRec.Open oCmdSQL, , adOpenStatic, adLockBatchOptimistic
' Disconnect here
104 Set oCmdSQL.ActiveConnection = Nothing
105 ' Send it back to the client
106 Set GetRecordSet = oRec
107 Set oRec = Nothing
108 Set oCmdSQL = Nothing
109 Set oPrm = Nothing
115 Set moCon = Nothing
116 ' Commit if a transaction
117 If Not moContext Is Nothing Then
118 moContext.SetComplete
119 End If
120 Exit Function
ErrorHandler:
121 sDesc = Err.Description
122 lErrNum = Err.Number
123 sSource = Err.Source
137 ' Check the ADODB Error Collection
138 If Not moCon Is Nothing Then
139 ' Check the ADODB Error Collection
140 If moCon.State = adStateOpen Then
141 If moCon.Errors.Count >= 1 Then
142 sDesc = ADODBErrors(sDesc)
143 End If
144 'Close the connection
145 moCon.Close
146 End If
147 End If
148 Set oRec = Nothing
149 Set oCmdSQL = Nothing
150 Set oPrm = Nothing
151 Set moCon = Nothing
152 If Not moContext Is Nothing Then
153 moContext.SetAbort
154 End If
155 sSource = "LIB.MODULE.GetRecordSet" & IIf(Erl <> 0, " on
line " & CStr(Erl), "") & vbCrLf & sSource
156 Err.Raise lErrNum, sSource, sDesc
End sub
MTS CLIENT Components that use the disconnected recordset
Private Function Test() As String
1 On Error GoTo ErrorHandler
5 Dim oTest As oDB.CDatabase
7 Dim oRec As ADODB.Recordset
16 Set oTest = oContext.CreateInstance("oDB.CDatabase")
Set oRec = oDB.GetRecordSet("sp_Test", adCmdStoredProc,
msCon, , , varParameters)
Set oDB = Nothing
With oRec
** ERRORS HERE ***
If Not .EOF Then
Test= CStr(UCase(.Fields(0)))
25 Else
26 Test= ""
27 End If
28 End With
38 Set oRec = Nothing
39 moContext.SetComplete
40 Exit Function
ErrorHandler:
41 sDesc = Err.Description
42 lErrNum = Err.Number
43 sSource = Err.Source
44 Set oRec = Nothing
45 Set oDB = Nothing
46 moContext.SetAbort
47 sSource = "LIB.MOD.Test" & IIf(Erl <> 0, " on line " &
CStr(Erl), "") & vbCrLf & sSource
48 Err.Raise lErrNum, sSource, sDesc
End Function
I am also using connection pooling where the connection is
pooled for 2 mins.
Can you tell me what I am doing wrong. I have been fighting
this for 1 week now and cant understand why is sometimes works
and sometimes doesnt even when the stored procedure and params
are the same
Thanks in advance
Sukhraj
* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!