ADO: ActiveX Data Objects

Parameters

Parameterized queries are used frequently in today’s databases. Parameters allow queries to be stored within the data source and to be altered based upon different values at runtime.

Passing Parameters

Like most things in ADO, there are a couple ways of passing parameter values to the data provider.

The first way, and probably the easiest, is to specify the value of a parameter in CommandText property of a Command object, like a function call:

com.ActiveConnection = "driver={SQL Server}; " _
                     & "server=JROFF_LAPTOP; " _
                     & "uid=sa; " _
                     & "database=Northwind"

com.CommandText = "CustOrderHist('ALFKI')"
com.CommandType = adCmdStoredProc

Set rst = com.Execute

Do Until (rst.EOF)
    Debug.Print rst.Fields("ProductName")
    rst.MoveNext
Loop

rst.Close

In this code, the value 'ALFKI' is passed to the stored procedure CustOrderHist. This method can also be used when executing commands through a Connection object, as in the following example:

con.Open "driver={SQL Server}; " _
       & "server=JROFF_LAPTOP; " _
       & "uid=sa; " _
       & "database=Northwind"

Set rst = con.Execute("CustOrderHist('ALFKI')", _
                      , _
                      adCmdStoredProc)

Do Until (rst.EOF)
    Debug.Print rst.Fields("ProductName")
    rst.MoveNext
Loop

rst.Close

Although easy to understand, this method leaves much to be desired. Suppose that you wish to pass a parameter value from within a variable, rather than hard-coding it into the string as shown in the last two examples. In this case, of course it can be done. However, you will have to do string concatenation, ...