0

Code:

Dim rs As ADODB.RecordSet
Set rs = New ADODB.RecordSet
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command 

cmd.ActiveConnection = cn 'global connection string
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "usp_GetItem"

with cmd.Parameters
     .Item("@parm1") = parm1
     .Item("@parm2") = parm2
End With

Set rs = cmd.Execute

If rs.EOF Then '<-- error here
    functionName = DefaultValue
Else 
    functionName = rs!Item
End If

Set rs = Nothing
Set cmd = Nothing

Exit Function

The error is happening on rs.EOF. I get "Operation is not allowed when the object is closed" which is in reference to the closed record set that's returned from the cmd.Execute statement. The record that returns contains three fields, first has data, second is null, third is Item. Item is empty but not null. I have multitudes of other queries coded exactly the same way without this error. Why is it happening?

Pendonep
  • 43
  • 6
  • Does this answer your question? [Recordset Closed After Stored Procedure Execution](https://stackoverflow.com/questions/28677262/recordset-closed-after-stored-procedure-execution) – GSerg Jul 16 '21 at 22:49
  • No. My stored procedure has nocount and all those other things – Pendonep Jul 17 '21 at 01:54
  • 1
    Adding the code of your SP may be helpful. – StayOnTarget Jul 17 '21 at 12:10
  • This other question may also be worth looking at if you haven't, it has a lot more detail & more answers than the other. https://stackoverflow.com/questions/16529032/operation-is-not-allowed-when-the-object-is-closed-when-executing-stored-proce – StayOnTarget Jul 17 '21 at 12:11

1 Answers1

0

I always try to be on the safe side and therefore encapsulate such code with

If Not rs.State = adStateClosed Then
   ' Do your stuff
End If
Hel O'Ween
  • 1,423
  • 9
  • 15