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

If you're generating the CommantText on the fly anyway (meaning you're not using a stored procedure), why don't you just put your variable inline with the rest of the Select statement?

objCmd.CommandType = adCmdText
objCmd.CommandText = "SELECT ... WHERE field1=" + p_Field1 + " ...."    
...
objCmd.Open

Answer 2

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

Answer 3

http://www.dotneteer.com/Weblog/post/2008/04/Prepared-statements-(parameterized-query)-with-ADO.aspx

Answer 4

So it looks like ADODB.Command doesn't really support named parameters. ASP/VBScript sucks so much...

Answer 5

My visual basic 6 select query is not working with sql server 2000 cause trim is not supported by sql server2000 help me pls thanx in advanced