ADO's command object can be used in several different ways to query a database. This basic tutorial presents some of these ways with examples. The Microsoft Access 2003 program is also used for the sake of the interface.
The place of the command object in relation to the other objects in the ADO object model is shown in the next picture. The command object can query the data source and return a RecordSet Object. By providing the name of a parametrized query, or the name of a table in the database, or a stored procedure, the command object can be used to execute instructions and also return the results of the execution.
The picture shown next also shows that the Parameters collection and Parameter object are related to the Command object. While the command object can execute a query repeatedly, it can also execute multiple queries.
Properties and Methods of the Command Object
Provide a reference to ADO 2.8 library
Similar to the other articles on ADO at the ASP Free site by this author, MS Access 2003 will be used to look at some of the properties and methods of the command object. Before looking at any of the ADO objects, it is necessary to add a reference to the ADO library. By default, it may already have been added (ADO2.1) and it will be necessary to change it to a version higher than 2.5.
For this tutorial, the latest version (ADO 2.8) will be used as shown in the next picture. This window may be accessed by clicking on the Components drop-down menu when you click the menu item Tools when you are in the Microsoft Visual Basic screen.
Objective of the tutorial
The main objective of this basic tutorial on ADO's Command object is to show some of the ways it can be used to query the database and present each of the ways by an example. MS Access 2003 program is used basically to utilize the Microsoft Visual Basic interface it provides. Even if one does not have the VB 6.0 the MS Access program can be used to follow these examples.
The command object exposes a number of properties and methods as shown in the next picture.
Before any command can be executed, there needs to be an active connection. A command is known to the program by the Name property. What and how a command can execute is set by the pair of properties CommandType and CommandText. The CommandText is a string that can be specified for the command, or it could be the name of a stored procedure -- a CommandText which is stored on the data source. The CommandTimeout with a default value of 30 seconds is the duration for which the ADO can wait for results to be returned before the operation is canceled. If a value of 0 is set, it sits up waiting indefinitely. The reason a query or an execution will timeout depends on many factors, including the CursorLocation.
CommandTypeEnum
We have come across the Enums in our previous tutorials. As with other objects, the ADO uses these enumerations (Enums) in its syntax extensively. Here are the CommandTypeEnums and what they imply. These are also listed in the Object Browser.
adCmdText : 1 The query will not modify data.
adCmdTable: 2 The query will append the table name to "Select * from "
adCmdstoredProcedure: 4 ADO will format the query to call a stored procedure on the server.
adCmdUnknown: 8(default value) Tries different methods until the query succeeds.
adCmdFile: 256(&H100) Not applicable to command object.
adCmdTableDirect: 512(&p00) Not applicable to command object.
In the next example, an active connection will be made to the SQL 2005 Server and the various CommandType assignments will be examined using the SQL Native Client.
CommandType is adCmdText
The most common type of command text when the CommandType is assigned to adCmdText is the Select statement. This statement can be used to select a number of columns (or fields) from a table. It can also be modified with various clauses to sort, select those satisfying a criterion, summarizing the fields, and so forth. We will look at a simple example which returns the CompanyName, City, and Postal code of customers who live in Brazil in the Northwind database's Customers table, which is shown here. This table is from the "Customers" table in the Northwind database on the SQL 2005 Server.
On SQL 2005 Server
The following query on the SQL 2005 server returns all the customers living in "Brazil."
Using ADO
In order to return the same records using ADO, the properties CommandType and CommandText have to be appropriately set. The next code listing in the click event of a Command Button does exactly that as shown by the returned recordset that follows the code listing.
Private Sub Command0_Click()
'declare Connection, Command and RecordSet variables
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim rst As ADODB.Recordset
'create new instances of the variables
Set oConn = New ADODB.Connection
Set oCmd = New ADODB.Command
'Open the connection
oConn.Open "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=False; Initial Catalog=Northwind;" & _
"Data Source=Hodentekmysorian"
'set the command's Active Connection
oCmd.ActiveConnection = oConn
'set the command's CommandText property
oCmd.CommandText = "Select CompanyName, City, Postalcode " & _
"from Customers where Country='Brazil'"
'set the Command's CommandType property
oCmd.CommandType = adCmdText
'execute the command for the Recordset
Set rst = oCmd.Execute
'get the three fields in CommandText by ordinal reference
Do Until rst.EOF
Debug.Print (rst.Fields(0).Value & " , " & _
rst.Fields(1).Value & " , " & _
rst.Fields(2).Value & vbCrLf)
rst.MoveNext
Loop
MsgBox (rst.Fields.Count)
oConn.Close
End Sub
The result printed out to the immediate screen is shown in the next paragraph.
Comércio Mineiro , Sao Paulo , 05432-043
Familia Arquibaldo , Sao Paulo , 05442-030
Gourmet Lanchonetes , Campinas , 04876-786
Hanari Carnes , Rio de Janeiro , 05454-876
Que Delícia , Rio de Janeiro , 02389-673
Queen Cozinha , Sao Paulo , 05487-020
Ricardo Adocicados , Rio de Janeiro , 02389-890
Tradição Hipermercados , Sao Paulo , 05634-030
Wellington Importadora , Resende , 08737-363
Again for comparison purposes we take a look at the following stored procedure on the SQL 2005 Server called the getCityEmployee. This stored procedure when executed on the server returns the result which consists of all the employees from "London" as shown.
If you take a look at the script of this procedure shown in the next listing, you notice that it takes no arguments, but returns the results of a Select query. It's basically a select statement created as a stored procedure.
Use Northwind
Go
create procedure [dbo].[getCityEmployee]
as
select LastName, BirthDate, City
from Employees where City='London'
Go
Using ADO
For this case, again the proper values for the CommandType and CommandText have to be set appropriately as shown in the next code listing in the click event of a command button. In coding this you must use the intellisense automatically provided as shown.
Private Sub Command1_Click()
'declare Connection, Command and RecordSet variables
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim rst As ADODB.Recordset
'create new instances of the variables
Set oConn = New ADODB.Connection
Set oCmd = New ADODB.Command
'Open the connection
oConn.Open "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=False; Initial Catalog=Northwind;" & _
"Data Source=Hodentekmysorian"
oCmd.ActiveConnection = oConn
'set the CommandType to stored procedure
oCmd.CommandType = adCmdStoredProc
'set the command text to the name of the stored procedure
'on the server as a string
oCmd.CommandText = "getCityEmployee"
Set rst = oCmd.Execute
Do Until rst.EOF
Debug.Print (rst.Fields(0).Value & " , " & _
rst.Fields(1).Value & " , " & _
rst.Fields(2).Value & vbCrLf)
rst.MoveNext
Loop
MsgBox (rst.Fields.Count)
oConn.Close
End Sub
The result printed out to the immediate screen when the Command button is clicked is shown in the next paragraph.
Buchanan , 1955/03/04 , London
Suyama , 1963/07/02 , London
King , 1960/05/29 , London
Dodsworth , 1966/01/27 , London
When the CommandType is assigned to the adCmdTable, what is returned is equivalent to returning all the columns from the named table as shown in the next picture.
Using ADO
In order to return the same records using ADO, the properties CommandType and CommandText have to be appropriately assigned. The next code listing in the click event of a Command Button does exactly that as shown by the returned recordset that follows the code listing.
Private Sub Command2_Click()
'declare Connection, Command and RecordSet variables
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim rst As ADODB.Recordset
'create new instances of the variables
Set oConn = New ADODB.Connection
Set oCmd = New ADODB.Command
'Open the connection
oConn.Open "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=False; Initial Catalog=Northwind;" & _
"Data Source=Hodentekmysorian"
oCmd.ActiveConnection = oConn
'set command's CommandType property to adCmdTable
oCmd.CommandType = adCmdTable
'set the Command's Command text property to a name of a table
'as a string
oCmd.CommandText = "Employees"
Set rst = oCmd.Execute
'note although all 18 columns are returned, only three of them are
'printed to the immediate screen.
Do Until rst.EOF
Debug.Print (rst.Fields(0).Value & " , " & _
rst.Fields(1).Value & " , " & _
rst.Fields(2).Value & vbCrLf)
rst.MoveNext
Loop
MsgBox (rst.Fields.Count)
MsgBox (oCmd.CommandText)
oConn.Close
End Sub
The result printed out to the immediate screen when the Command button is clicked is shown in the next paragraph.
1 , Davolio , Nancy
2 , Fuller , Andrew
3 , Leverling , Janet
4 , Peacock , Margaret
5 , Buchanan , Steven
6 , Suyama , Michael
7 , King , Robert
8 , Callahan , Laura
9 , Dodsworth , Anne
There is no equivalent in SQL 2005 Server. What this means is that the ADO will try every method for which it has access to retrieve the results. Obviously the process is burdened with the the onus of finding a suitable method.
Using ADO
As explained earlier ADO will try to guess and return the results if it can find a method. On the SQL 2005 Server there is a stored procedure called getEmployee in the Northwind database. For the CommandType property adCmdUnknown will be assigned, and for the CommandText property the name of this stored procedure will be assigned. The next code listing in the click event of a Command Button does exactly that as shown by the returned recordset that follows the code listing.
Private Sub Command3_Click()
'declare Connection, Command and RecordSet variables
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim rst As ADODB.Recordset
'create new instances of the variables
Set oConn = New ADODB.Connection
Set oCmd = New ADODB.Command
'Open the connection
oConn.Open "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=False; Initial Catalog=Northwind;" & _
"Data Source=Hodentekmysorian"
oCmd.ActiveConnection = oConn
'assigning adCmdUnknown to the CommandType
oCmd.CommandType = adCmdUnknown
'assign 'getEmployee' to the CommandText property
oCmd.CommandText = "getEmployee"
Set rst = oCmd.Execute
'return only 3 fields
Do Until rst.EOF
Debug.Print (rst.Fields(0).Value & " , " & _
rst.Fields(1).Value & " , " & _
rst.Fields(2).Value & vbCrLf)
rst.MoveNext
Loop
MsgBox (rst.Fields.Count)
oConn.Close
End Sub
The result printed out to the immediate screen when the Command button is clicked is shown in the next paragraph. In this case at least ADO made a correct guess, but it must have searched for a table with a name when you compare it with the result from the stored procedure.
Buchanan , 1955/03/04 , London
Now the stored procedure getEmployee, when executed on the SQL 2005 Server, will return the following result.
Summary
This basic tutorial considers some of the ways the ADO's command object can be executed to query the database. When the CommandType was assigned the adCmdUnknown, ADO did return the results correctly, making the correct guess. The Parameters collection and the Parameter object have not been considered. The CommandTypes adCmdFile and adCmdTableDirect are not used with the Command object, but rather with the RecordSet object when it is open. The CommandType adCmdFile is used with a persisted recordset to a file, and uses the file name for the CommandText. The CommandType, adCmdTableDirect is also used with the open Recordset. The CommandText in this case will be intuitively the name of a table. It is interesting to note that when the CommandType is adCmdTable and the CommandText is assigned to the name of a table, the code MsgBox (cmd.CommandText) returns the full Select statement.
Bookmarking and Moving through Records with ADO
