VBA、ADO.Connection 和查询参数

VBA, ADO.Connection and query parameters

提问by Alexey

I have excel VBA script:

我有excel VBA脚本：

Set cоnn = CreateObject("ADODB.Connection")
conn.Open "report"
Set rs = conn.Execute("select * from table" ) 

Script work fine, but i want to add parameter to it. For example " where (parentid = myparam)", where myparamsetted outside query string. How can i do it?

脚本工作正常，但我想向它添加参数。例如“ where (parentid = myparam)”，其中myparam设置在查询字符串之外。我该怎么做？

Of course i can modify query string, but i think it not very wise.

当然我可以修改查询字符串，但我认为这不是很明智。

回答by Dick Kusleika

You need to use an ADODB.Command object that you can add parameters to. Here's basically what that looks like

您需要使用可以向其添加参数的 ADODB.Command 对象。这基本上是这样的

Sub adotest()

    Dim Cn As ADODB.Connection
    Dim Cm As ADODB.Command
    Dim Pm As ADODB.Parameter
    Dim Rs as ADODB.Recordset

    Set Cn = New ADODB.Connection
    Cn.Open "mystring"
    Set Cm = New ADODB.Command
    With Cm
        .ActiveConnection = Cn
        .CommandText = "SELECT * FROM table WHERE parentid=?;"
        .CommandType = adCmdText

        Set Pm = .CreateParameter("parentid", adNumeric, adParamInput)
        Pm.Value = 1

        .Parameters.Append Pm

        Set Rs = .Execute
    End With

End Sub

The question mark in the CommandText is the placeholder for the parameter. I believe, but I'm not positive, that the order you Append parameters must match the order of the questions marks (when you have more than one). Don't be fooled that the parameter is named "parentid" because I don't think ADO cares about the name other than for identification.

CommandText 中的问号是参数的占位符。我相信，但我并不肯定，您追加参数的顺序必须与问号的顺序相匹配（当您有多个问号时）。不要被参数命名为“parentid”而上当，因为我认为 ADO 除了用于标识之外并不关心名称。

回答by Big McLargeHuge

Alternative example returning a command from a function:

从函数返回命令的替代示例：

Function BuildCommand(conn As ADODB.Connection) As ADODB.Command
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    cmd.Parameters.Append cmd.CreateParameter("@name", adVarChar, adParamInput, 255, "Dave")
    cmd.CommandText = "SELECT * FROM users WHERE name = @name;"
    Set BuildCommand = cmd
End Function

A couple things to note:

需要注意的几点：

1. When using adVarChardata type, the size argument to cmd.CreateParameter(e.g. 255) is required. Not supplying it results a run-time error 3708: Application-defined or object-defined error, as indicated in the documentation:

   If you specify a variable-length data type in the Type argument, you must either pass a Size argument or set the Size property of the Parameter object before appending it to the Parameters collection; otherwise, an error occurs.

2. If the cmd.ActiveConnectionproperty is set when cmd.CommandTextis set, and cmd.CommandTextcontains named parameters, cmd.Parameterswill be populated accordingly. Calling cmd.Parameters.Appendafterwards could result in duplicates. For example:

   cmd.ActiveConnection = conn
   cmd.CommandType = adCmdText
   Debug.Print cmd.Parameters.Count ' 0
   
   cmd.CommandText = "SELECT * FROM users WHERE name = @name;"
   Debug.Print cmd.Parameters.Count ' 1
   
   cmd.Parameters.Append cmd.CreateParameter("@name", adVarChar, adParamInput, 255, "Dave")
   Debug.Print cmd.Parameters.Count ' 2
   

   I believe this is what is meant in the documentation, which is slightly inaccurate:

   If the Prepared property of the Command object is set to True and the Command object is bound to an open connection when you set the CommandText property, ADO prepares the query (that is, a compiled form of the query that is stored by the provider) when you call the Execute or Open methods.

   As a workaround, either set cmd.CommandTextor cmd.ActiveConnectionafter adding parameters.

1. 使用adVarChar数据类型时，需要cmd.CreateParameter（例如 255）的大小参数。不提供它会导致运行时错误 3708: Application-defined or object-defined error，如文档中所示：

   如果在 Type 参数中指定可变长度数据类型，则必须在将其附加到 Parameters 集合之前传递 Size 参数或设置 Parameter 对象的 Size 属性；否则，会发生错误。

2. 如果该cmd.ActiveConnection属性在设置时cmd.CommandText设置，并且cmd.CommandText包含命名参数，cmd.Parameters则将相应地填充。cmd.Parameters.Append之后调用可能会导致重复。例如：

   cmd.ActiveConnection = conn
   cmd.CommandType = adCmdText
   Debug.Print cmd.Parameters.Count ' 0
   
   cmd.CommandText = "SELECT * FROM users WHERE name = @name;"
   Debug.Print cmd.Parameters.Count ' 1
   
   cmd.Parameters.Append cmd.CreateParameter("@name", adVarChar, adParamInput, 255, "Dave")
   Debug.Print cmd.Parameters.Count ' 2
   

   我相信这就是文档中的意思，这有点不准确：

   如果将 Command 对象的 Prepared 属性设置为 True 并且在设置 CommandText 属性时将 Command 对象绑定到打开的连接，则 ADO 准备查询（即提供程序存储的查询的编译形式）当您调用 Execute 或 Open 方法时。

   作为解决方法，设置cmd.CommandText或cmd.ActiveConnection添加参数后。