VB6+SQL-Server: How can I execute a Query with nam

问题:

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

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"

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?

回答1:

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.

回答2:

So it looks like ADODB.Command doesn't really support named parameters.

回答3:

It's been quite some time since I used VB6, but have you tried setting the NamedParameters property on the Command object to True?

ie.

objCmd.NamedParameters = True

回答4:

The only way I know how to do this is to create ? as the parameter e.g.

In you example:

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

change it to:

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

Hope this helps

回答5:

It's not perfect, but...This is wrapper class I created that allows you to create Named Parameter queries...

Below this code is a demo. The biggest catch right now is that you must execute your setParameter statements in the correct order as declared.

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