VB6 + SQL-Server: how can I execute a query with named parameters using ADODB.Command?

Question

I am trying to execute a parameterized query using ADODB.Command. I know I can use "?" for the parameters, but my queries are quite large, and I really don't want to keep track of 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 cannot do this inside the stored procedure due to the dynamic nature of the request itself. When I try to execute a request, I get an error message:

 Must declare the scalar variable "@p_Field1"

Is there another way that does not include the use of stored procedures or (argh) to combine the parameter values ​​in the request itself and not use the parameters at all?

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

, VB6, NamedParameters Command True?

.

objCmd.NamedParameters = True

Answer 3

, , ADODB.Command .

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

, ... , , ...

Below is the code. The biggest catch right now is that you have to execute your setParameter instructions in the correct order as indicated.

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