ADO and the Command Object - CommandType is adCmdUnknown (Page 6 of 6 )
On SQL 2005 Server
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
