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屋!