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?

Answer 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.