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

sql-server, vb6

Solution

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.

Problem

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?

Original source