VB6+SQL-Server:如何使用 ADODB.Command 执行带有命名参数的查询? [英] VB6+SQL-Server: How can I execute a Query with named parameters using ADODB.Command?

问题描述

我一直在尝试使用 ADODB.Command 执行参数化查询.我知道我可以使用?"对于参数，但我的查询相当大，我真的不想跟踪参数的确切顺序.我尝试了以下内容:

I've been trying to execute a parametrized query with ADODB.Command. I know I can use "?" for parameters, but my queries are rather large and I really don't want to track the exact order of the parameters. I tried something like the following:

objCmd.CommandType = adCmdText
objCmd.CommandText = "SELECT ... WHERE field1=@p_Field1 ...."    
Dim objParam As ADODB.Parameter
Set objParam = objCmd.CreateParameter("@p_Field1" ...)
objCmd.Parameters.Append objParam
...
objCmd.Open

它适用于存储过程参数(显然，设置 CommandType = adCmdStoredProc)，但由于查询本身的动态特性，我无法在存储过程中执行此操作.当我尝试运行查询时，出现错误:

It works for stored procedure parameters (setting CommandType = adCmdStoredProc, obviously), but I can't do this inside a Stored Procedure because of the dynamic nature of the query itself. When I try to run the query, I get the error:

 Must declare the scalar variable "@p_Field1"

有没有其他不涉及使用存储过程或(argh)连接查询本身中的参数值而不使用参数的其他方法?

Is there any other way around this that doesn't involve using stored procedures or (argh) concatenating the parameters values in the query itself and not use parameters at all?

推荐答案

您可以这样做，以允许您重用参数

You can do this, to allow you to reuse parameters

objCmd.CommandText = "declare @var1 int, @var2 int, @var3 int; " & _
    "select @var1 = ?, @var2 = ?, @var3 = ?;" & _
    "select ... where field1 = @var1 or field4 = @var1 or field2 = @var2 ... "

并按正常顺序添加参数.

and add the parameters in the normal ordered manner.

这篇关于VB6+SQL-Server:如何使用 ADODB.Command 执行带有命名参数的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！