ADO and the Command Object - Examples using the CommandType property (Page 3 of 6 )
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
Next: CommandType is adCmdStoredProc >>
More Database Articles
More By Jayaram Krishnaswamy
