---------------------------------------------------------------------
---- 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