Click here to Skip to main content
15,843,031 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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



   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    
    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
Updated 8-Oct-11 0:37am

1 solution

Are you sure the connection opens correctly, you have no error checking in place. So if opening of the connection fails you continue as it was opened and you are performing operations on a closed object.
Share this answer
stackhoover 8-Oct-11 6:06am    
Hi !
Yes I have checked the connection, it's open. The command "dbo.D100601RVDATABearingAllow" text does arrive at the server. (sql profiler shows that). I have a error handeler, did not post in order to keep the post short and clear. In the vba debugger I can see that the parameter has been appended.
André Kraak 8-Oct-11 6:26am    
What you might try is use the Cmd1.Parameters.Refresh command and check the result to see what the expected parameter is. This way you can verify that the correct parameter is used.
stackhoover 8-Oct-11 7:02am    
Ok, I tried that, and after the refresh I get another parameter called @RETURN_VALUE. ?? (It is empty) Where does that come from? The original parameter Param1 set by me on the other hand has suddenly the value "Empty". What is going on??
André Kraak 8-Oct-11 7:18am    
Using the Cmd1.Parameters.Refresh command was a check not the solution.
See about the @RETURN_VALUE parameter.

The original parameter has no value because the Refresh command only creates the parameters and gives them no value.
What are the properties of the created parameter and are you using the same in your call Set Param1 = Cmd1.CreateParameter("Fastener", adInteger, adParamInput, 5).
stackhoover 8-Oct-11 7:26am    
I now use:

Cmd1.Parameters(1).Value = Fastener
Cmd1.Parameters(2).Value = Thickness
Cmd1.Parameters(3).Value = Material
Cmd1.Parameters(4).Value = ShearType

Just before I want to execute Cmd1 all parameters are there. My errorhandler is
i = 1
On Error Resume Next
Set Errs1 = cnt.Errors
For Each errLoop In Errs1
With errLoop
strTmp = strTmp & vbCrLf & "ADO Error # " & i & ":"
strTmp = strTmp & vbCrLf & " ADO Error # " & .Number
strTmp = strTmp & vbCrLf & " Description " & .Description
strTmp = strTmp & vbCrLf & " Source " & .Source
i = i + 1
End With
But i does not catch any Ado error.

To me it seems like the problem is in this line:
Set rst = Cmd1.Execute()
Do I have to open the recordset somehow before I use it?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900