Page 516 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

The Refresh method populates the Parameters collection using the name of the stored proce­
dure from the CommandText property. This can be a useful shortcut that avoids the extra
code to define all the parameters manually.

Note Although you can use the Refresh method to get a copy of the parameters for a
stored procedure directly from the database, you might not want to do this each time you
call the stored procedure. There’s a fair amount of overhead associated with retrieving the
parameters from the database, which could add up if you’re calling the stored procedure
from within a loop.

Using the Parameter Object

The Parameter object contains a number of properties that describe a specific parameter that
is passed to a parameterized query or a stored procedure. (See Table 23-7.)

Table 23-7. Key Properties of the Parameter Object
Property Description
Direction Indicates whether the parameter is an input (adParamInput),
output (adParamOutput), or input/output (adParamInputOutput)
parameter to the stored procedure.
Name Contains the name of the parameter.
NumericScale Contains the number of digits to the right of the decimal point for a
numeric field.
Precision Contains the total number of digits in a numeric field.
Type Contains the data type associated with the parameter. Some
common values are adSmallInt, adInteger, asSingle, adDouble,
adCurrency, adDate, adBSTR, adBoolean, adDecimal, adBigInt,
adBinary, adChar, adWChar, adNumeric, adDBDate, adDBTime,
adVarNumeric, adVarChar, adLongVarChar, adVarWChar,
adLongVarWChar, adVarBinary, and asLongVarBinary.
Value Contains the value of the parameter. For input parameters and
input/output parameters, this value will be passed to the stored
procedure. For input/output and output parameters, this value is
set after the stored procedure is executed.
Chapter 23
Each Parameter object describes a single parameter to a stored procedure. The Name prop­
erty must match the parameter name defined in the stored procedure. You must specify the
database type associated with the parameter, along with which direction the value is passed.
The Value property contains the value that’s passed and/or returned from the stored proce­
dure. Typically, you define a Command object with all its associated parameters only once.
Then you modify the set of Value properties so that you pass the appropriate information to
the stored procedure.

490
Part 6: Excel and the Outside World: Collaborating Made Easy