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

Question

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?

Does this answer your question? Is it possible to use named parameters when command type is adCmdText? — GSerg
– GSerg, Commented May 31, 2024 at 18:39

podiluska · Accepted Answer · 2014-01-29 13:08:04Z

13

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.

answered Jan 29, 2014 at 13:08

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tbone Over a year ago

THIS APPROACH ACTUALLY WORKS! In case it's not obvious why, they key is to first DECLARE them by name, and then SET (select @someVar=?,@SomeOtherVar=?) those named parameters using the stupid ADODB incoming by ordinal position notation, and then in the rest of your command text you can use those named parameters exactly as you would normally. Beautiful.

João Marcus Over a year ago

I'm not using VB6 anymore (thank God...), but I marked the answer as accepted anyway because I've teen told the answer works.

CraigTP · Accepted Answer · 2009-07-10 15:15:10Z

6

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

answered Jul 10, 2009 at 15:15

CraigTP

45.1k9 gold badges74 silver badges101 bronze badges

Comments

Kev · Accepted Answer · 2011-09-15 11:50:10Z

2

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

edited Sep 15, 2011 at 11:50

Kev

120k53 gold badges308 silver badges396 bronze badges

answered Aug 21, 2009 at 13:22

João Marcus

1,6081 gold badge13 silver badges20 bronze badges

2 Comments

Harry A Over a year ago

um, YES it does. you need to have the correct ADO project references though. 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)

GSerg Over a year ago

The support for named parameters depends on the driver you are telling ADO to use, not to ADO itself. You can have ADO 2.8 and it would still not support named parameters if the driver doesn't (e.g. OLEDB).

Johan J v Rensburg · Accepted Answer · 2011-03-21 13:43:23Z

0

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

answered Mar 21, 2011 at 13:43

Johan J v Rensburg

3222 silver badges11 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

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

As far as I can tell this does not work, if cmd.commandType = adCmdText

Harry A · Accepted Answer · 2023-06-21 11:24:00Z

-1

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.

edited Jun 21, 2023 at 11:24

answered Jun 14, 2023 at 10:50

Harry A

1211 gold badge1 silver badge10 bronze badges

1 Comment

GSerg Over a year ago

The support for named parameters depends on the driver you are telling ADO to use, not to ADO itself. You can have ADO 2.8 and it would still not support named parameters if the driver doesn't (e.g. OLEDB).

Collectives™ on Stack Overflow

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

6 Answers 6

2 Comments

Comments

2 Comments

Comments

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.

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

2 Comments

Comments

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.

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related