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

Question

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:

SQL

SELECT dbo.D100601RVDATABearingAllow

arrives.

MY EXCEL UDF:

VB

   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

Posted 7-Oct-11 23:22pm

stackhoover

Updated 7-Oct-11 23:37pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

André Kraak · Answer 1 · 2011-10-08T00:01:00

Solution 1

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.

Posted 8-Oct-11 0:01am

André Kraak

Comments

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 http://stackoverflow.com/questions/2663211/can-someone-explain-me-the-parameter-return-value 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.Refresh
'----------------------------------------------------------
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
AdoError:
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
Next
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

Hi,
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....