[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 789
  • Last Modified:

'operation is not allowed when the object is closed' error message when executing SQL Stored Procedure as ADO Command

Hello fellow experts

I'm trying to execute a SQL Stored Procedure as an ADO command, and am getting the error message mentioned in the title.
To the best of my knowledge everything is spelled correctly, the one parameter is referenced correctly.
The SP executes fine when run in SQL Query Analyzer.

If anyone knows of a reason why this would occur, I'd be greatful for the help.

Thanks in advance.
-Jim


____________________


Public Function importQuery(pCase As String) As ADODB.Recordset

On Error GoTo error_handler

Dim cmd As New ADODB.Command
Dim par As New ADODB.Parameter

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim lRecordsAffected As Long

If mConnSQL Is Nothing Then Call ConnectSQL

With cmd
    .ActiveConnection = mConnSQL
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.spm_prex_pricing_export_table"
   
' Create new parameter for the command and set it's value
    'Feed it the PK
    Set prm = cmd.CreateParameter("@case_id", adVarChar, adParamInput, 25, pCase)
    .Parameters.Append prm
 End With

If rs.State <> adStateClosed Then rs.Close
rs.CursorLocation = adUseServer
rs.Open cmd, , adOpenDynamic, adLockOptimistic

'At any point after rs.Open when you try to manipulate rs, the error message occurs.
End Function

______________________


Public Sub ConnectSQL()

'Set a global SQL Server connection object
Stop

On Error GoTo error_handler

Dim sConnect As String
sConnect = "Provider=sqloledb;Data Source=theserver;Initial Catalog=thedatabase;User Id=theuser;Password=thepassword;"

If mConnSQL Is Nothing Then
    Set mConnSQL = CreateObject("ADODB.Connection")
    mConnSQL.ConnectionString = sConnect
    mConnSQL.Open
End If

If mConnSQL.Errors.Count > 0 Then
    MsgBox "An error occured while attempting to connect to SQL Server BOND:  " & Chr(13) & mConnSQL.Errors(0).Description, vbOKOnly, ""
End If

exit_function:
    'Nothing to do here
    Exit Sub
   
error_handler:
    Stop
    MsgBox Err & ", " & Error$
    Resume exit_function

End Sub
0
Jim Horn
Asked:
Jim Horn
  • 3
  • 2
2 Solutions
 
Leigh PurvisDatabase DeveloperCommented:
Which line does it error on?

One question is - do you know you have
Dim par As New ADODB.Parameter
      ----
and yet

Set prm = cmd.CreateParameter("@case....
     -----

And also you've not executed the cmd before setting it as the recordset source?
0
 
Leigh PurvisDatabase DeveloperCommented:
Hi Jim btw - dunno where my manners are today :-)
0
 
flavoCommented:
woow... You sure you have Option Explict turned on?  This thing doesn't even compile.

Dim cmd As New ADODB.Command
Dim par As New ADODB.Parameter
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

Dim lRecordsAffected As Long

If mConnSQL Is Nothing Then Call ConnectSQL

With cmd
    .ActiveConnection = mConnSQL
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.spm_prex_pricing_export_table"
    ' Create new parameter for the command and set it's value
    'Feed it the PK
    Set par = New ADODB.Parameter
    Set par = cmd.CreateParameter("@case_id", adVarChar, adParamInput, 25, pCase)
    .Parameters.Append par
 End With

If rs.State <> adStateClosed Then rs.Close
rs.CursorLocation = adUseServer
rs.Open cmd, , adOpenDynamic, adLockOptimistic


Dave

BTW, I usually just call a simple rs thus wise:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

With rs
    .CursorLocation = adUseClient
    .Open "spm_prex_pricing_export_table @case_id='" & pCase & "'" _
            , mConnSQL, adOpenDynamic, adLockBatchOptimistic
End With

However, depending on your provider I don't think you can even return a CursorType of OpenDynamic.

ie:
With rs
    .CursorLocation = adUseClient
    .Open "spm_prex_pricing_export_table @case_id='" & pCase & "'" _
            , mConnSQL, adOpenDynamic, adLockBatchOptimistic
End With
debug.print rs.CursorType
{ Prints 3 = adOpenStatic }

Sucks :-(
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Go figure... not having SET NOCOUNT ON at the top of your stored proc interferes with ADO being able to load the results into a recordset or command object.

Problem solved.  

I'll post for lower points, then award as a thanks for helping me rule out a few things.

-Jim
0
 
flavoCommented:
Okkie
0
 
Leigh PurvisDatabase DeveloperCommented:
dokey
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now