Figure 1   Updating Author Information


---------------------------------------------------------------------
---- Create the stored procedure to update the author table
---------------------------------------------------------------------
CREATE PROCEDURE prUpdateAuthor
    @chvFirstName varchar(20), 
    @chvLastName varchar(40),
    @chrPhone char(12),
    @chvAddress varchar(40),
    @chvCity varchar(20),
    @chrState char(2),
    @chrZip char(5),
    @bitContract bit,
    @chrID char(1)
AS

UPDATE authors
SET     
    au_fname = @chvFirstName, 
    au_lname = @chvLastName,
    phone = @chrPhone,
    address = @chvAddress,
    city = @chvCity,
    state = @chrState,
    zip = @chrZip,
    contract = @bitContract
WHERE (au_id = @chrID)


Figure 2   Creating and Adding Parameters


'--------------------------------------------------------------------
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
     
'--------------------------------------------------------------------
'--- Open the connection to the pubs database
'--------------------------------------------------------------------
Set objConn = New ADODB.Connection
objConn.Provider = "SQLOLEDB"
objConn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=pubs;Data Source=papanotebook;User ID=sa;Password="
objConn.Open
    
'--------------------------------------------------------------------
'--- Set the command object up to run the stored procedure
'--------------------------------------------------------------------
Set objCmd = New ADODB.Command
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "prUpdateAuthor"
Set objCmd.ActiveConnection = objConn
    
'--------------------------------------------------------------------
'--- Create and add the parameters
'--------------------------------------------------------------------
objCmd.Parameters.Append objCmd.CreateParameter("@chvFirstName", adVarChar, 
    adParamInput, 20, "John")
objCmd.Parameters.Append objCmd.CreateParameter("@chvLastName", adVarChar, 
    adParamInput, 40, "White")
objCmd.Parameters.Append objCmd.CreateParameter("@chrPhone", adChar, adParamInput, 
    12, "408 496-7223")
objCmd.Parameters.Append objCmd.CreateParameter("@chvAddress", adVarChar, 
    adParamInput, 40, "10932 Bigge Rd.")
objCmd.Parameters.Append objCmd.CreateParameter("@chvCity", adVarChar,  
    adParamInput, 20, "Menlo Park")
objCmd.Parameters.Append objCmd.CreateParameter("@chrState", adChar, adParamInput, 
    2, "CA")
objCmd.Parameters.Append objCmd.CreateParameter("@chrZip", adChar, adParamInput, 
    5, "94025")
objCmd.Parameters.Append objCmd.CreateParameter("@bitContract", adBoolean, 
    adParamInput, , 1)
objCmd.Parameters.Append objCmd.CreateParameter("@chrID", adChar, adParamInput, 
    1, "172-32-1176")

'--------------------------------------------------------------------
'--- Execute the stored procedure
'--------------------------------------------------------------------
objCmd.Execute
    
objConn.Close
Set objCmd = Nothing
Set objConn = Nothing
'--------------------------------------------------------------------


Figure 3   Refreshing the Parameters


'--------------------------------------------------------------------
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
     
'--------------------------------------------------------------------
'--- Open the connection to the pubs database
'--------------------------------------------------------------------
Set objConn = New ADODB.Connection
objConn.Provider = "SQLOLEDB"
objConn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=pubs;Data Source=papanotebook;User ID=sa;Password="
objConn.Open
    
'--------------------------------------------------------------------
'--- Set the command object up to run the stored procedure
'--------------------------------------------------------------------
Set objCmd = New ADODB.Command
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "prUpdateAuthor"
Set objCmd.ActiveConnection = objConn
    
'--------------------------------------------------------------------
'--- Refresh the Parameters collection
'--------------------------------------------------------------------
objCmd.Parameters.Refresh
objCmd.Parameters("@chvFirstName") = "John"
objCmd.Parameters("@chvLastName") = "White"
objCmd.Parameters("@chrPhone") = "408 496-7223"
objCmd.Parameters("@chvAddress") = "10932 Bigge Rd."
objCmd.Parameters("@chvCity") = "Menlo Park"
objCmd.Parameters("@chrState") = "CA"
objCmd.Parameters("@chrZip") = "94025"
objCmd.Parameters("@bitContract") = 1
objCmd.Parameters("@chrID") = "172-32-1176"

'--------------------------------------------------------------------
'--- Execute the stored procedure
'--------------------------------------------------------------------
objCmd.Execute
    
objConn.Close
Set objCmd = Nothing
Set objConn = Nothing
'--------------------------------------------------------------------


Figure 4   prStoredProcedureParameters


CREATE PROCEDURE prStoredProcedureParameters
    @chvProcedureName varchar(30)
AS

SELECT     
    0 AS ColIndex, 
    'RETURN_VALUE' AS Name, 
    3 AS Type,
    0 AS Size,
    0 AS NumericScale,
    10 AS Prec,
    4 AS Direction

UNION

SELECT     
    sc.colid AS ColIndex, 
    sc.name AS Name, 
    CASE sc.usertype
        WHEN 16 THEN 11    --adBoolean, bit
        WHEN 21 THEN 6     --adCurrency, smallmoney
        WHEN 11 THEN 6     --adCurrency, money
        WHEN 22 THEN 133   --adDBDate, smalldatetime
        WHEN 12 THEN 133   --adDBDate, datetime
        WHEN 24 THEN 14    --adDecimal, decimal
        WHEN 10 THEN 14    --adDecimal, numeric
        WHEN 7 THEN 3      --adInteger, int
        WHEN 23 THEN 4     --adSingle, real
        WHEN 8 THEN 4      --adSingle, float
        WHEN 6 THEN 2      --adSmallInt, smallint
        WHEN 5 THEN 17     --adUnsignedTinyInt, tinyint
        WHEN 3 THEN 204    --adVarBinary, binary
        WHEN 4 THEN 204    --adVarBinary, varbinary
        WHEN 80 THEN 204   --adVarBinary, timestamp
        WHEN 20 THEN 204   --adVarBinary, image
        WHEN 1 THEN 200    --adVarChar, char
        WHEN 2 THEN 200    --adVarChar, varchar
        WHEN 19 THEN 200   --adVarChar, text
    END AS Type,
    sc.length AS Length,
    sc.scale AS NumericScale,
    sc.prec AS Prec,
    CASE sc.status & 64
        WHEN 64 THEN 3     --adParamOuput
        ELSE 1             --adParamInputOutput
    END AS Direction
FROM     syscolumns sc 
    INNER JOIN sysobjects so ON sc.id = so.id
WHERE     so.name = @chvProcedureName

ORDER BY ColIndex


Figure 5   Parameter Output


ColIndex    Name             Type    Size    NumericScale    Prec    Direction
----------- ------------------ ----------- ----------- ------------ ----------- 
0           RETURN_VALUE      3       0          0            10         4
1           @chvFirstName     200     20         NULL         20         1
2           @chvLastName      200     40         NULL         40         1
3           @chrPhone         200     12         NULL         12         1
4           @chvAddress       200     40         NULL         40         1
5           @chvCity          200     20         NULL         20         1
6           @chrState         200     2          NULL         2          1
7           @chrZip           200     5          NULL         5          1
8           @bitContract      11      1          0            1          1
9           @chrID            200     11         NULL         11         1

(10 row(s) affected)



Figure 6   Custom Refresh Method


Public Sub Refresh(ByRef objCmdNeedsRefreshing As ADODB.Command)
    Dim objCmdRefreshParms As ADODB.Command
    Dim objParm As ADODB.Parameter
    Dim objRSParms As ADODB.Recordset
    Dim lngPos As Long
    Dim strStoredProcName As String
    
    '-----------------------------------------------------
    '--- Create a command object to call the stored
    '--- procedure (spStoredProcParams).
    '-----------------------------------------------------
    Set objCmdRefreshParms = CreateObject("ADODB.Command")
    Set objParm = CreateObject("ADODB.Parameter")
    
    With objCmdRefreshParms
        .CommandText = " prStoredProcedureParameters"
        .CommandType = adCmdStoredProc
        Set .ActiveConnection = objCmdNeedsRefreshing.ActiveConnection
        Set objParm = .CreateParameter("@strProcedureName")
    End With
    
    '--- Pass the stored procedure's name whose parameters we want.
    With objParm
        .Type = adVarChar
        .Size = 30
        .Direction = adParamInput
        lngPos = Len("{ call ") + 1
        strStoredProcName = Mid$(objCmdNeedsRefreshing.CommandText, lngPos)
        strStoredProcName = Trim$(Left$(strStoredProcName, 
            Len(strStoredProcName) - 1))
        .Value = strStoredProcName
    End With
    
    objCmdRefreshParms.Parameters.Append objParm
    
    '-------------------------------------------------
    '--- Now execute spStoredProcParams. It returns
    '--- all of the parameters, in order, for the
    '--- given stored proc.
    '-------------------------------------------------
    Set objRSParms = objCmdRefreshParms.Execute

    With objRSParms
        '-------------------------------------------------
        '--- Loop through the recordset returned so we
        '--- can add the parameters to the stored proc
        '--- passed in to this routine.
        '-------------------------------------------------
        Do Until objRSParms.EOF
            Set objParm = CreateObject("ADODB.Parameter")
            Set objParm = objCmdNeedsRefreshing.CreateParameter(
                .Fields("Name").Value)
            objParm.Type = .Fields("Type")
            objParm.Size = .Fields("Size")
            objParm.Precision = .Fields("Prec")
            If Not IsNull(.Fields("NumericScale")) Then
                objParm.NumericScale = .Fields("NumericScale")
            End If
            objParm.Direction = .Fields("Direction")
            If objParm.Direction = adParamInputOutput Then
                objParm.Value = Null
            End If
            objCmdNeedsRefreshing.Parameters.Append objParm
            Set objParm = Nothing
            .MoveNext
        Loop
    End With

    objRSParms.Close
    Set objRSParms = Nothing
    Set objCmdRefreshParms = Nothing
End Sub