How can I deal with this problen Runtime error 3704

Question

0.00/5 (No votes)

See more:

Hello Experts,

C#

Public Sub SQLPrepareCmd()
  
  'Declare the params
 Dim prmBS1 As ADODB.Parameter
 Dim prmBS2 As ADODB.Parameter
 
 Set oRst = New ADODB.Recordset
  
    Set oCmd = New ADODB.Command
    oCmd.ActiveConnection = oCon
    oCmd.CommandType = adCmdStoredProc
    oCmd.CommandText = "spTest"
    oCmd.CommandTimeout = 0

' Set up the parameter for the Stored Procedure

  oCmd.Parameters.Append oCmd.CreateParameter("Periode1", adVarChar, adParamInput, 10, Range("Q3").Text)
  oCmd.Parameters.Append oCmd.CreateParameter("Periode2", adVarChar, adParamInput, 10, Range("Q4").Text)
    
 
    Set oRst = oCmd.Execute(, , adCmdStoredProc)
   
    Application.StatusBar = "Running stored procedure..."

If oRst.EOF = False Then
        ActiveSheet.Cells(40, 2).CopyFromRecordset oRst
            oRst.Close
            
        oCon.Close
             
        Set oCon = Nothing
        Set oCmd = Nothing

End Sub

I am getting this msg: Runtime Error 3704:
Operation is not allowed when the object is closed

on this line

If oRst.EOF = False Then

Will be glad for a help. I tried something like this but it isn't working

VB

'* Open recordset
   Set oRst.Source = oCmd
   oRst.Open oCmd

Posted 8-Dec-15 4:04am

mikybrain1

Updated 8-Dec-15 22:47pm

v3

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)

CHill60 · Accepted Answer · 2015-12-08T04:19:00

Solution 1

You are using oCmd before it has been initialised. Try

C#

  Set oCmd = New ADODB.Command
  oCmd.ActiveConnection = oCon
  oCmd.CommandType = adCmdStoredProc
  oCmd.CommandText = "spTest"
  oCmd.CommandTimeout = 0

oCmd.Parameters.Append oCmd.CreateParameter("Periode1", adVarChar, adParamInput, 10, Range("Q3").Text)
oCmd.Parameters.Append oCmd.CreateParameter("Periode2", adVarChar, adParamInput, 10, Range("Q4").Text)

Posted 8-Dec-15 4:19am

CHill60

Comments

ZurdoDev 8-Dec-15 10:24am

Maybe I'm blind, but the OP does have the init code for oCmd.

CHill60 8-Dec-15 10:31am

Yes they do, but it appears after they have attempted to append to parameters (of the then empty object) - hence I moved the Parameters.Append to after the Set oCmd = New ADODB.Command.

ZurdoDev 8-Dec-15 10:37am

Duh. Yes, it is very clear. It's official, I am blind.

I'll scurry back to work now.

+5

CHill60 8-Dec-15 10:40am

*chuckle*