CODEWITHSUNDEEP
sql-servervb6
João Marcus
João Marcus

Reputation: 1608

VB6+SQL-Server: How can I execute a Query with named parameters using 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

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?

Upvotes: 5

Views: 14860

Answers (6)

Harry A
Harry A

Reputation: 121

this is important if you need to make use of namedparameters property: You need to have the correct ADO project references. ADO 2.8 to be precise. "Microsoft ActiveX Data Objects 2.8 Library" (msado15.dll). not sure about this one - MAYBE you also need the reference "Microsoft ADO Ext 2.8 for DDL and Security" (msadox.dll)

if you dont have 2.8 referenced, the compiler will complain to you it doesnt know what that property (namedparameters) is. your project references need 2.8 ADO

Upvotes: -1

podiluska
podiluska

Reputation: 51504

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.

Upvotes: 13

João Marcus
João Marcus

Reputation: 1608

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

Upvotes: 2

Doug Chamberlain
Doug Chamberlain

Reputation: 11351

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

Upvotes: 0

Johan J v Rensburg
Johan J v Rensburg

Reputation: 322

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

Upvotes: 0

CraigTP
CraigTP

Reputation: 44929

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

Upvotes: 6

Related Questions