Listing 8: Running a Stored Procedure

' Declare connection and connection string variables
Dim conSqlServer, csSqlServer

' Declare command variable
Dim cmdVacation

' Declare variable for number of rows updated 
Dim RowsUpdated

' Declare argument constants
Const adCmdStoredProc = 4
Const adInteger = 3
Const adExecuteNoRecords = &H00000080
Const adStateOpen = 1

' Create Connection object
Set conSqlServer = CreateObject("ADODB.Connection")

' Define connection string
csSqlServer = "Provider='sqloledb';Data Source='ws04';" & _
  "Integrated Security='SSPI';Initial Catalog='AdventureWorks';"

' Open connection
conSqlServer.Open csSqlServer
        
' Create Command object
Set cmdVacation = CreateObject("ADODB.Command")

' Define Command object properties
With cmdVacation

  ' Set the connection
  .ActiveConnection = conSqlServer

  ' Specify name of stored procedure
  .CommandText = "UpdateVacationHours"

  ' Specify command type
  .CommandType = adCmdStoredProc

  ' Append parameters
  .Parameters.Append .CreateParameter("@EmpID", adInteger)
  .Parameters.Append .CreateParameter("@HoursUsed", adInteger)

  ' Assign parameter values
  .Parameters("@EmpID") = 1
  .Parameters("@HoursUsed") = 1
End With

' Execute command
cmdVacation.Execute RowsUpdated, , adExecuteNoRecords

' Display number of records updated
MsgBox RowsUpdated & " record(s) updated."
  
' Clean up
If conSqlServer.State = adStateOpen then
  conSqlServer.Close
End If

Set conSqlServer = Nothing
Set csSqlServer = Nothing
Set cmdVacation = Nothing
Set RowsUpdated = Nothing