Database - How do I use an ADO Command object?

[si_the_geek]

A command object allows you to separate your SQL statement into the structure and the values (thus protecting against errors and injection attacks), allows you to run a Stored Procedure with output parameters, and if run multiple times (even with different values) is faster than using a string based equivalent.

For other reasons why using a Command object is good (and using just a String is bad), see the article Why should I use Parameters in my program instead of putting values directly into my SQL string?

The following does not cover all possibilities for the command object, but just the most common uses. If you want further information, see the help (online version here).

Changes to your SQL statement
No matter what kind of SQL statement you have (eg: Select/Insert/Update/...), you just need to remove any values (and delimiters around them) from your SQL statement, and replace them with either the ? character, or the @ character followed by a name (eg: "@Name"). This is a placeholder that the Command object will use to determine where to place the parameters.

For example, if your existing SQL statement is like this:

  strSQL = "SELECT field1 FROM table1 WHERE field2 = 1 AND field3 = '" & txtVal3.Text & "'"

you should change it either to this:

  strSQL = "SELECT field1 FROM table1 WHERE field2 = @Field2 AND field3 = @Field3"

or to this:

  strSQL = "SELECT field1 FROM table1 WHERE field2 = ? AND field3 = ?"

Note that which of these two styles of placeholder you can use depends on the database system you are working with.


Setting up the Command object
You initiate the Command object like you would other objects (such as a Recordset), and then set up the properties as needed, eg:

Dim objCommand as ADODB.Command
  Set objCommand = New ADODB.Command
  With objCommand
    .ActiveConnection = objConn   'replace this with your Connection object
    .CommandType = adCmdText
    .CommandText = strSQL         'replace this with your SQL statement
    .Prepared = True

Note that the With is used to save typing - anything from there up to the End With (which will be added later) that has . in front of it refers to the object on the With line, so .CommandType = adCmdText is the same as objCommand.CommandType = adCmdText

If you are running a Stored Procedure, change adCmdText to adCmdStoredProc, and set .CommandText to the name of the Stored Procedure.


Adding the parameters/values
So far you have got the SQL statement set up, but no values in it. To put the values into it you append a Parameter and set the value, which you can do like this:

    .Parameters.Append .CreateParameter("Field2", adSmallInt, adParamInput, , 1)
    .Parameters.Append .CreateParameter("Field3", adVarChar, adParamInput, 50, txtVal3.Text)

The arguments for .CreateParameter are as follows:

• Name - this is optional, but does make it easier to read your code. Unfortunately it does not mean that this parameter will be used for the item with the same name in the SQL statement - the first parameter you add (no matter what name you give it) will be used at the first placeholder in the SQL statement.
  
  
• Type - the data type of the field in the database, which will usually be one of the following:
  
  • adBoolean (for Boolean, Bit, Yes/No fields)
  • adVarChar (for a variable length text field)
  • adChar (for fixed length text field)
  • adDate (for Date/Time based fields)
  • adInteger (for whole numbers up to +/-2.1 billion)
  • adSmallInt (for whole numbers up to +/-32767)
  • adSingle (for single-precision floating-point numbers)
  • adCurrency (for fields with a data type of Currency or Money)
  
  
  
• Direction - this specifies whether the value should be placed into the SQL statement (adParamInput), or should act in a different way (such as adParamOutput to return an output parameter of a Stored Procedure; if doing this you do not need to set the value).
  
  
• Size - this is additional info for the data type, and only needs to be specified if the data type is not a fixed size - for the data types I listed above, only adVarChar and adChar need it, and it should be the maximum number of characters/bytes you specified for the field in the table design.
  
  
• Value - the value (text/number/..) that you want to use. This should ideally be the same data type as you specified, for example if you used adDate, the value should not be a String (either directly or via the Format function etc), but instead should be a Date variable/property, or a date value (eg: #06/01/2009#).
  
  If your statement uses Like, your wildcards should be included in this value (eg: , "%" & txtField3.Text & "%") )

For more information about any of these arguments (including full lists of options), see the help for .CreateParameter (online version here).

Append a parameter for each of the placeholders in your SQL statement.


Running it
How you should execute the command depends on whether it returns records or not - a normal Select statement returns records, but Delete and Insert statements do not.

For statements that do return records, execute the command assigning the results to a Recordset object:

    Set objRS = objCommand.Execute   'replace this with your Recordset object
  End With

For statements that don't return records, execute the command specifying that it should not return any records (this reduces the internal work, so makes it faster):

    .Execute , , adExecuteNoRecords
  End With

Note that if you are running a Stored Procedure with output parameters, you can refer to them after executing the command using the Name you specified in CreateParameter, eg:

  MsgBox objCommand.Parameters("OutputParam1").Value

Running it multiple times
If you want to run the command multiple times, you do not need to repeatedly set everything up inside the loop - instead you should set things up before the loop, tidy up after the loop, and inside the loop just need to alter the parameter Values and execute it again.

Note however that inside the loop you should not append parameters as shown above (because the original ones will be used each time), but just set the value. To do this, change the arguments to .CreateParameter so that there is a Name but not a Value, and then inside your loop just set the Value like this:

    .Parameters("Field2").Value = intValue

Tidying up
Just like with other object variables, when you are finished with it you should tidy up (such as free up the memory used). Unlike other ADO objects, you do not actually need to close it, but simply release the memory, eg:

  Set objCommand = Nothing

[dilettante]

Creating stored procedures can be pretty easy actually, especially those used by Jet:

    cnDB.Execute "CREATE PROC InsertPie(NewPie TEXT(50), FruitName TEXT(50)) AS " _
               & "INSERT INTO Pies (Pie, FruitId) " _
               & "SELECT NewPie, Fruits.FruitId FROM Fruits " _
               & "WHERE Fruit = FruitName", _
                 , adCmdText

Invoking them can be even easier. You don't need to use the Command.Parameters collection to do so. Stored Procedures and Named Commands become extended methods on the Connection object:

    cnDB.InsertPie Trim$(txtPie.Text), cboFruit.List(cboFruit.ListIndex)

    'Define transient Command for temporary multiple use.  We could
    'also have used a Stored Procedure but we only need it here.
    Set cmInsertFruit = New ADODB.Command
    With cmInsertFruit
        .Name = "InsertFruit"
        .Prepared = True
        .CommandType = adCmdText
        .CommandText = "INSERT INTO Fruits (Fruit) VALUES (?)"
        Set .ActiveConnection = cnDB
    End With

    'Populate [Fruits].
    intFile = FreeFile(0)
    Open FRUITS_NAME For Input As #intFile
    Do Until EOF(intFile)
        Input #intFile, strFruit
        cnDB.InsertFruit strFruit
    Loop
    Close #intFile

The attached example illustrates this and many other useful tricks, such as creating an MDB with related tables from scratch.

[The_Grudge]

Thanks for posting this. My skills are rusty as I don't program very often anymore and I have to update a legacy program to work with SQL Server instead of Oracle.

I didn't use parameters when I wrote the queries years ago (and there are many), but using your example I was able to update some code. Long road ahead but you got me on my way so thank you - great tutorial.