Step 2: Create a Command (ADO Tutorial)

You are Here...

• Make a connection to a data source.
  
  
• Optionally, create an object to represent an SQL query command.
  
  
• Optionally, specify values in the SQL command as variable parameters.
  
  
• Execute the command. If the command is row-returning, store the rows in a storage object.
  
  
• Optionally, navigate, examine, manipulate, and edit the data.
  
  
• If appropriate, update the data source with changes from the storage object. Optionally, embed the update in a transaction.
  
  
• If a transaction was used, accept or reject the changes made during the transaction. End the transaction.

Discussion

A query command requests that the data source return a Recordset object containing rows of requested information. Commands are typically written in Structured Query Language (SQL).

1. As mentioned, operands such as a command string can be represented as:
   • A literal string or a variable that represents the string. This tutorial could query for all the information in the authors table of the pubs database with the command string "SELECT * from authors".
     
     
   • An object that represents the command string. In this case, the value of a Command object CommandText property set to the command string.

     Command cmd = New ADODB.Command;
     cmd.CommandText = "SELECT * from authors"
     

2. Specify a parameterized command string with the '?' placeholder.

   The content of an SQL string is fixed. However, you can create a parameterized command where '?' placeholder substrings can be replaced with parameters when a command is executed.

   You can optimize the performance of parameterized commands with the Prepared property. You can issue them repeatedly, changing only their parameters each time.

   For example, the following command string issues a query for all the authors whose last name is "Ringer":

   Command cmd = New ADODB.Command
   cmd.CommandText = "SELECT * from authors WHERE au_lname = ?"
   

3. Specify a Parameter object. Append it to the Parameters collection.

   Each '?' placeholder is replaced by the value of a corresponding Parameter object in the Command object Parameters collection. Create a Parameter object with Ringer as the value, then append it to the Parameters collection:

   Parameter prm = New ADODB.Parameter
   prm.Name = "au_lname"
   prm.Type = adVarChar
   prm.Direction = adInput
   prm.Size = 40
   prm.Value = "Ringer"
   cmd.Parameters.Append prm
   

4. Specify and append a Parameter object with the CreateParameter method.

   ADO now offers a convenient alternative means to create a Parameter object and append it to the Parameters collection in one step.

   cmd.Parameters.Append cmd.CreateParameter _
   "au_lname", adVarChar, adInput, 40, "Ringer"
   

   However, this tutorial won't use a parameterized command, because you must use the Command.Execute method to substitute the parameters for the æ?Æ placeholders. But that method wouldn't allow us to specify Recordset cursor type and lock options. For that reason, use this code:

   Command cmd = New ADODB.Command;
   cmd.CommandText = "SELECT * from authors"
   

For your information, this is the schema of the authors table:

Next Step 3