Click here to Skip to main content
15,306,209 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 7-Oct-11 23:37pm

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.
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?
stackhoover 8-Oct-11 7:57am
I have just checked that. It's ok. Just before I execute Cmd1, in the debugger the Command text is: "{ call dbo.D100601RVDATABearingAllow(?, ?, ?, ?) }"
Are the question marks ok there? And is "call" correct? In the management studio the function only works with SELECT. Thanks for your help so far!
André Kraak 8-Oct-11 8:13am
That looks OK.
André Kraak 8-Oct-11 8:18am
I am really confused why we can not get this to work.
I am grasping at straws right now.

Would you please try:
Cmd1.Parameters("@Fastener").Value = Fastener
Cmd1.Parameters("@Thickness").Value = Thickness
Cmd1.Parameters("@Material").Value = Material
Cmd1.Parameters("@ShearType").Value = ShearType
This should make doubly sure we are assigning the correct values to the correct parameters.
stackhoover 8-Oct-11 8:29am
Tried that...The parameters end up neat as Parameter.Item 2-5 with correct name and value (Item 1 is the @RETURN_VALUE). The following line executes without complaints:
Set rst = Cmd1.Execute()
But, no trace of the command in the profiler...
The whole thing crashes in this line:
RVDATA = rst.Fields(0).Value
A lot of "Operation is not allowed when object is closed" in the properties of the rst. ;-(
I have also tested the whole udf using a plain text sql command, and not an command object with parameters, that does work! So the bug is not in the connection nor in the function on the sql server. I'm getting nuts!
André Kraak 8-Oct-11 8:46am
I am sorry to say that I am lost.

I would like to leave with the suggestion to start a new with a stored procedure without any parameters. Test whether that works and if so then start adding the parameters one by one.
Also I would go back to your original code, that should work.

One more point about the original code I see that you use Set Param1 = Nothing before you call Cmd1.Execute(). Move that after the execute command.

I hope you figure this out.
stackhoover 8-Oct-11 8:51am
Thanks anyway!
PS: I commented that "=Nothing" a long time ago.. ;-)
André Kraak 8-Oct-11 7:37am
No, the Execute command returns an opened recordset.

Are you sure that the stored procedure should return records based on the parameter values used? Perhaps there is something wrong in the procedure and it always returns no result.
stackhoover 8-Oct-11 7:52am
Hi André,
when I try
SELECT dbo.D100601RVDATABearingAllow(2348,2,'f','SS')
directly in the management studio, the function returns the correct result.
Do I have to define a parameter of type adParamReturnValue to store the result in?
stackhoover 8-Oct-11 8:05am
When I use adCmdStoredProc the command does not arrive at the server, at least the profiler can't see it. When i disable adCmdStoredProc I see "exec dbo.D100601RVDATABearingAllow" arrive at the server, but I also get the errormessage back that the variable @Fastener was not supplied....

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

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