我如何处理此问题运行时错误3704 [英] How can I deal with this problen Runtime error 3704

问题描述

Hello Experts，

 Public Sub SQLPrepareCmd（）
 
 ' 声明参数
 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 
 
'为存储过程设置参数 
 
 oCmd.Parameters.Append oCmd.CreateParameter（  Periode1，adVarChar，adParamInput， 10 ，范围（  Q3）。文字） 
 oCmd.Parameters.Append oCmd.CreateParameter（  Periode2， adVarChar，adParamInput， 10 ，范围（  Q4).Text)
 
 
设置oRst = oCmd.Execute（,, adCmdStoredProc）
 
 Application.StatusBar =  运行存储过程...... 
 
如果oRst.EOF = False那么
 ActiveSheet.Cells （ 40 ， 2 ）。CopyFromRecordset oRst 
 oRst.Close 
 
 oCon.Close 
 
 Set oCon = Nothing 
 Set oCmd = Nothing 
 
 End Sub 

我收到这个消息：运行时错误3704：

当对象关闭时不允许操作



在这条线上

如果oRst.EOF = False那么

Will很高兴能得到帮助。我试过这样的东西，但它不起作用

 '  *开放记录集 
 设置 oRst.Source = oCmd 
 oRst.Open oCmd 

解决方案

在初始化之前，您正在使用 oCmd 。尝试

设置oCmd =新ADODB.Command 
 oCmd.ActiveConnection = oCon 
 oCmd.CommandType = adCmdStoredProc 
 oCmd.CommandText =   spTest 
 oCmd.CommandTimeout =  0  
 
 oCmd.Parameters.Append oCmd.CreateParameter（  Periode1，adVarChar，adParamInput， 10 ，范围（  Q3）。文本）
 oCmd.Parameters.Append oCmd.CreateParameter（  Periode2 ，adVarChar，adParamInput， 10 ，范围（  Q4）。文本）

Hello Experts,

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

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

解决方案

You are using oCmd before it has been initialised. Try

  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)

这篇关于我如何处理此问题运行时错误3704的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！