How do I associate Parameters to Command objects in ADO with VBScript?

Question

I have been working an ADO VBScript that needs to accept parameters and incorporate those parameters in the Query string that gets passed the the database. I keep getting errors when the Record Set Object attempts to open. If I pass a query without parameters, the recordset opens and I can work with the data. When I run the script through a debugger, the command object does not show a value for the parameter object. It seems to me that I am missing something that associates the Command object and Parameter object, but I do not know what. Here is a bit of the VBScript Code:

...
'Open Text file to collect SQL query string'
Set fso = CreateObject("Scripting.FileSystemObject")
fileName = "C:\SQLFUN\Limits_ADO.sql"
Set tso = fso.OpenTextFile(fileName, FORREADING)

SQL = tso.ReadAll

'Create ADO instance'
 connString = "DRIVER={SQL Server};SERVER=myserver;UID=MyName;PWD=notapassword;   Database=favoriteDB"
 Set connection = CreateObject("ADODB.Connection")
 Set cmd = CreateObject("ADODB.Command")

  connection.Open connString
  cmd.ActiveConnection = connection
  cmd.CommandText = SQL
  cmd.CommandType = adCmdText

  Set paramTotals = cmd.CreateParameter
  With paramTotals
       .value = "tot%"
       .Name = "Param1"
  End With

  'The error occurs on the next line'
  Set recordset = cmd.Execute

  If recordset.EOF then
      WScript.Echo "No Data Returned"
  Else
      Do Until recordset.EOF
            WScript.Echo recordset.Fields.Item(0) ' & vbTab & recordset.Fields.Item(1)
            recordset.MoveNext
      Loop
  End If

The SQL string that I use is fairly standard except I want to pass a parameter to it. It is something like this:

SELECT column1
FROM table1
WHERE column1 IS LIKE ?

I understand that ADO should replace the "?" with the parameter value I assign in the script. The problem I am seeing is that the Parameter object shows the correct value, but the command object's parameter field is null according to my debugger.

Answer 1

I would parse the SQL and add in the parameters that way:

For example:

Dim strSQL : strSQL = "SELECT column1 FROM table1 WHERE column1 IS LIKE ?"

strSQL = Replace(strSQL, "?", Param1)

I would then execute this SQL Command against the DB.

The Parameter object for for Stored Procedured. etc.

Answer 2

I never got CreateParameter doing what I wanted. Proper parameterization is a necessity to avoid SQL injection, but CreateParameter is a complete PITA. Thankfully, there's an alternative: Command.Execute takes in parameters directly.

dim cmd, rs, rows_affected
set cmd = Server.createObject("adodb.command")
cmd.commandText = "select from Foo where id=?"
set cmd.activeConnection = someConnection
set rs = cmd.execute(rows_affected, Array(42))

It's much nicer when wrapped up in a proper abstraction. I wrote my own database class wrapping ADODB.Connection so I wouldn't have to do all of this manually. It relies a bit on other custom classes, but the gist of it should be apparent:

class DatabaseClass
'   A database abstraction class with a more convenient interface than
'   ADODB.Connection. Provides several simple methods to safely query a
'   database without the risk of SQL injection or the half-dozen lines of
'   boilerplate otherwise necessary to avoid it.
'   
'   Example:
'   
'   dim db, record, record_set, rows_affected
'   set db = Database("/path/to/db")
'   set record = db.get_record("select * from T where id=?;", Array(42))
'   set record_set = db.get_records("select * from T;", empty)
'   rows_affected = db.execute("delete from T where foo=? and bar=?",
'                              Array("foo; select from T where bar=", true))

    private connection_
'       An ADODB connection object. Should never be null.

    private sub CLASS_TERMINATE
        connection_.close
    end sub

    public function init (path)
'       Initializes a new database with an ADODB connection to the database at
'       the specified path. Path must be a relative server path to an Access
'       database. Returns me.

        set connection_ = Server.createObject ("adodb.connection")
        connection_.provider = "Microsoft.Jet.OLEDB.4.0"
        connection_.open Server.mapPath(path)

        set init = me
    end function

    public function get_record (query, args)
'       Fetches the first record returned from the supplied query wrapped in a
'       HeavyRecord, or nothing if there are no results. 

        dim data: set data = native_recordset(query, args)
        if data.eof then
            set get_record = nothing
        else
            set get_record = (new HeavyRecordClass).init(data)
        end if
    end function

    public function get_records (query, args)
'       Fetches all records returned from the supplied query wrapped in a
'       RecordSet (different from the ADODB recordset; implemented below).

        set get_records = (new RecordSetClass).init(native_recordset(query, args))
    end function

    public function execute (query, args)
'       Executes the supplied query and returns the number of rows affected.

        dim rows_affected
        build_command(query).execute rows_affected, args
        execute = rows_affected
    end function

    private function build_command (query)
'       Helper method to build an ADODB command from the supplied query.

        set build_command = Server.createObject("adodb.command")
        build_command.commandText = query
        set build_command.activeConnection = connection_
    end function

    private function native_recordset (query, args)
'       Helper method that takes a query string and array of arguments, queries
'       the ADODB connection, and returns an ADODB recordset containing the
'       result.

        set native_recordset = build_command(query).execute( , args) ' Omits out-parameter for number of rows
    end function
end class

Answer 3

After you create the parameter, you have to append it to the Command object's Parameters collection before you execute the Command:

Set paramTotals = cmd.CreateParameter
With paramTotals
    .Value = "tot%"
    .Name = "Param1"
End With

cmd.Parameters.Append paramTotals

You may also need to specify the Type and Size properties for the Parameter. Generally, I use the arguments of the CreateParameter function to set all the necessary properties in one line:

Set paramTotals = cmd.CreateParameter("Param1", adVarChar, adParamInput, 30, "tot%")
cmd.Parameters.Append paramTotals