Parameter

This contains all the parameter objects associated with the Command object. Paramters are used to pass arguments to the SQL statements and stored procedures as well as to receive output and return values from stored procedures

Updating the parameters collection

Specify a parameterized command string with the '?' placeholder.
This allows the "?" placeholders to be replaced with parameters when a command is executed
dbADOCommand.CommandText = "SELECT * from authors WHERE au_lname = ?"

Specify a Parameter object. Append it to the Parameters collection.

The parameters collection is associated with a specific command object, which uses the collection to pass parameters in and out of stored procedures and queries.
Parameters can be used to create Parameterized commands. These commands are (after they have been defined and stored) using parameters to alter some details of the command beforeit is executed. For example, an SQL Select statement could use a parameter to define the criteria of a WHERE clause
There are four types of parameters: input parameters, output parameters, input/output parameters and return parameters

When you specify a parameter you must append it to the parameter collection
You must have ADO 2.5+ to be able to return a cursor as an out parameter

Parameter Properties and Methods

Name	Type	Description
HelpContext	Property	Returns the context ID of a topic in the Microsoft Windows help system
HelpFile	Property
Native Error	Property
Number	Property	Returns a unique number identifying the error
Source	Property
Description	Property
SQL State	Property	Returns a 5 character SQL error number
Append	Property	Adds a new parameter to the Collection

Type
Value
Size
Direction

Direction	adParamInput (default) - represents a parameter into a procedure or function
	adParamInputOutput - represents a parameter both into and out of a procedure or function
	adParamOutput - represents a parameter out of a procedure or function
	adParamReturnValue - represents a return value by a function
	adParamUnknown - represents that the parameter if unknown

Parameter	Value	PL / SQL Type	VBA Type	Description
adArray			array
adBigInt	20			8 byte signed integer
adBinary	128			a binary value
adBoolean	11		boolean	a boolean value
adByRef
adBSTR	8	varchar2		a null terminated character string
adChapter	136			4 byte character value for a child recordset
adChar	129	varchar	string	a string value (ensure it is long enough)
adCurrency	6		currency	currency format
adDate	7		date	a date value, number of days since 12/30/1899
adDBDate	133			YYYYMMDD date format
adDBFileTime
adDBTime
adDBTimeStamp
adDecimal*	14		decimal	number with fixed precision and scale
adDouble	5		double	double precidion floating point
adEmpty	0			no value
adError	0			32 bit error code
adFileTime	64			number of 100-nanosecond intervals since 1/1/1601
adGUID	72			global unique identifier
adIDispatch	9			currently not supported by ADO
adInteger	3	natural, number	integer	a signed integer (4 bytes)
adIUnknown	13			currently not supported by ADO
adLongVarBinary	205
adLongVarChar	201
adLongVarWChar	203
adNumeric*	131
adPropVariant	138
adSingle	4		single
adSmallInt	2	integer	integer	2 byte signed integer
adTinyInt	16			1 byte signed integer
adUnsignedBigInt	21			8 byte unsigned integer
adUnsignedInt	19			4 byte unsigned integer
adUnsignedSmallInt	18			2 byte unsigned integer
adUnsignedTinyInt	17			1 byte unsigned integer
adUserDefined	132			user defined variable
adVarBinary	204			binary value
adVarChar	200	varchar		string
adVariant	12			automation variant
adVarChar
adVarNumeric	139			variable width exact numeric with signed scale
adVarWChar	202			null - terminated Unicode character string
adWChar	130			null - terminated Unicode character string

* Note: If you specify either adDecimal or adNumeric you must also specify the NumericScale and Precision properties o fthe Parameter object.

Example - How to pass Null

Example - Creating & Appending a parameter in one step

Example - Stored Procedure - No Parameters

When calling stored procedures you always have to assign a recordset when executing the command

Example - Stored Procedure - 1 Parameter

Example - Stored Function - 1 Parameter

Example - Stored Function - 2 Parameters

The order of the input and the output parameters does not matter.