VBScript building a parametrized query

Question

Is it possible to build a VBscript parametrized query without knowing the names, types or number of arguments beforehand?

I'm trying to do this:

 set cmd = Server.CreateObject("ADODB.Command")    
 cmd.ActiveConnection = cn    

 cmd.commandText = proc    
 cmd.commandType = 4    

 cmd.Parameters.Refresh  
 For i = 0 To UBound(params)           
     cmd.Parameters(i).Value = params(i)
 Next     

 set rs = cmd.Execute

This gives me the error:

ADODB.Parameter error '800a0d5d'
Application uses a value of the wrong type for the current operation

The argument string I'm trying to parse is of the form ,'arg1','arg2' etc. Params contains an array of just the args. The stored proc could be one of several types, with different argument types and names. I need to be able to parametrize the query to make sure that the input is sanitized. Any ideas?

Answer 1

This problem's a real pain – I suffered through it while building some database abstractions. It turns out that you can't bind params of unknown type to a Command object before executing it, but you can send an array of args of unknown type along when you execute it. Thus:

dim cmd: set cmd = Server.createObject("adodb.command")
cmd.commandText = "select * from Foo where Bar = ?"
set cmd.activeConnection = …

dim rs: set rs = cmd.execute(, Array(42))

If you want to bind unknown-typed arguments to a command, you'll have to write your own command class that uses this approach internally. I ended up writing my own database abstraction class so that the query syntax was:

dim rs: set rs = myDB.query("select * from Foo where Bar = ?", Array(42))