ADO and the Command Object - CommandType is adCmdStoredProc
(Page 4 of 6 )
On SQL 2005 Server
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
Next: CommandType is adCmdTable >>
More Database Articles
More By Jayaram Krishnaswamy
