ADODB open recordset fails / "Operation is not allowed when object is closed"

Question

I have the following UDF in excel which uses ADO to connect to my MSSQL server. There it should execute the scalar udf "D100601RVDATABearingAllow".

For some reason the parameters that I try to append are not send to the sql server. At the server only:

SELECT dbo.D100601RVDATABearingAllow

arrives.

MY EXCEL UDF:

   Function RVDATA(Fastener) As Long

    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim Cmd1 As ADODB.Command
    Dim stSQL As String

Const stADO As String = "Provider=SQLOLEDB.1;Data ................"
'----------------------------------------------------------
Set cnt = New ADODB.Connection
 With cnt
    .ConnectionTimeout = 3
    .CursorLocation = adUseClient
    .Open stADO
    .CommandTimeout = 3
 End With
'----------------------------------------------------------
Set Cmd1 = New ADODB.Command
    Cmd1.ActiveConnection = cnt
    Cmd1.CommandText = "dbo.D100601RVDATABearingAllow"
    Cmd1.CommandType = adCmdStoredProc
'----------------------------------------------------------
Set Param1 = Cmd1.CreateParameter("Fastener", adInteger, adParamInput, 5)
Param1.Value = Fastener
Cmd1.Parameters.Append Param1
Set Param1 = Nothing
'----------------------------------------------------------
Set rst = Cmd1.Execute()
RVDATA = rst.Fields(0).Value    
'----------------------------------------------------------
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
'----------------------------------------------------------
End Function

When I use adCmdStoredProc the whole thing fails and in the vba debugger the properties of the recordset has a lot of "Operation is not allowed when object is closed" (may sound a bit different, the message is translated)

When I don't use adCmdStoredProc I get the message that the variable Fastener was not provided.

I think that maybe something is wrong in the way I open the recordset. In other treads I read about using the "SET NOCOUNT ON" option, but that did not work either.

Does anyone have a idea? Regards Lumpi

Answer 1

Ran into this error as well (in my case I am using a Stored Procedure to retrieve some information). I had made some changes which caused the execution to malfunction.

The error disappeared when I put SET NOCOUNT ON as the first statement of the Stored Procedure.

Answer 2

You do not need to SELECT the server side function, just provide its name ("[tra-CAE400-1].dbo.D100601RVDATABearingAllow") in the .CommandText property. Also you should set the .CommandType property to "stored-procedure" (property reference on w3schools.com).

Then adodb will know that you are talking about calling a function, and not trying to send a plain sql-command.

Chances are that it will then allow you to define the parameters on the command object. But the parameters you define on the command object should correspond exactly (in name and type) to the ones that are defined as the arguments of the function in the sql server.

An example from microsoft.com on using the command-object with a stored procedure

ADO Reference on microsoft.com