VBA、ADO.Connection 和查询参数 [英] VBA, ADO.Connection and query parameters

问题描述

我有 Excel VBA 脚本:

I have excel VBA script:

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

脚本工作正常，但我想为其添加参数.例如"where (parentid = myparam)"，其中 myparam 设置在查询字符串之外.我该怎么做?

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

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

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

推荐答案

您需要使用可以添加参数的 ADODB.Command 对象.基本上就是这个样子

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

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

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

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.

这篇关于VBA、ADO.Connection 和查询参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！