VB6 + SQL-服务器：我如何执行与使用ADODB.Command命名参数查询？(VB6+SQL

我一直在试图执行与ADODB.Command一个参数化查询。 我知道我可以使用“？” 为参数，但我的疑问是相当大的，我真的不想跟踪参数的确切顺序。 我试过类似如下：

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，很明显），但我不能这样做在存储过程中，由于查询本身的动态特性。 当我尝试运行查询，我得到的错误：

 Must declare the scalar variable "@p_Field1"

有没有解决这个不使用存储过程或（哎呀）涉及任何其他方式在查询本身串接参数的值，而不是使用参数呢？

Answer 1:

你可以这样做，让你重用参数

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

而在正常有序的方式添加参数。

Answer 2:

所以它看起来像ADODB.Command真的不支持命名参数。

Answer 3:

它已经，因为我用VB6相当长的一段时间，但你有没有尝试设置NamedParameters Command对象为True的房产吗？

即。

objCmd.NamedParameters = True

Answer 4:

我知道如何做到这一点的唯一方法是创建？ 作为参数例如

在您例如：

objCmd.CommandText = "SELECT ... WHERE field1=@p_Field1 ...."    

将其更改为：

objCmd.CommandText = "SELECT ... WHERE field1 = ?"    
objCmd.NamedParameters = True
Dim objParam As ADODB.Parameter
Set objParam = objCmd.CreateParameter("@AnyName",,,,Value)
objCmd.Parameters.Append objParam

希望这可以帮助

Answer 5:

这不是完美的，但...这是包装类我创建了一个允许您创建命名参数查询...

下面这段代码是一个演示。 最大的渔获现在的问题是，你必须按照正确的顺序执行申报您的setParameter语句。

Class SQLBuilder

Private Sub Class_Initialize(  )
    Set internal_commandObject = Server.CreateObject("ADODB.Command")
End Sub

Private internal_sql
Private internal_parameters
private internal_commandObject

Public Property Let CommandType(ByVal value)
    internal_commandObject.CommandType = value
End Property

Public Property Get CommandType
    CommandType = internal_commandObject.CommandType
End Property

Public Property Let Prepared(ByVal value)
    internal_commandObject.Prepared = value
End Property

Public Property Get Prepared
    Prepared = internal_commandObject.Prepared
End Property

Public Property Get SQLCommandObject
Set SQLCommandObject = internal_commandObject
End Property

Public Property Let SQLCommandObject(ByVal value)
Set internal_commandObject = value
End Property

Public Property Get CommandText
    CommandText = internal_commandObject.CommandText
End Property

Public Property Let CommandText(ByVal sqlStatement)
    GetSQLParameters sqlStatement
    internal_commandObject.CommandText =  internal_sql
End Property

Public Property Get Parameters
    Set Parameters = internal_parameters
End Property


Public Function SetParameter(name,datatype,direction,size,value)

 internal_commandObject.Parameters.Append internal_commandObject.CreateParameter(name,datatype,direction,size,value)
End Function 

Private Sub GetSQLParameters(sql)
    internal_commandObject.NamedParameters = true
    Set RegExObj = New RegExp

    With RegExObj
        .Global = true
        .Multiline = true
        .Pattern = "@\S+"
        .IgnoreCase = true
    End With

    Set internal_parameters =  CreateObject("Scripting.Dictionary")

    With internal_parameters
        For each item in RegExObj.Execute(sql) 
        if Not .Exists(item.value) then  
            .Add item.value,item.value
        end if
        Next

    End With
    internal_sql = RegExObj.Replace(sql,"?")
End Sub


End Class

---

    Dim Temp
Dim mySqlBuilder 
Set mySqlBuilder = new SQLBuilder

With mySqlBuilder
    set .SQLCommandObject.ActiveConnection =PropConnection
.CommandText = "select LegalDescription from STAGE.DataLegalDescription where FISCAL_YEAR = @FISCAL_YEAR AND  AccountNumber = @ACCOUNT_NUMBER"
.Prepared = true
.SetParameter "@FISCAL_YEAR",adInteger,adParamInput,4, Cint(Year)
.SetParameter "@ACCOUNT_NUMBER",adVarChar,adParamInput,13, AccountNumber
End With

    RSProp.Open mySqlBuilder.SQLCommandObject

文章来源: VB6+SQL-Server: How can I execute a Query with named parameters using ADODB.Command?