Parameters

This contains all the paramter 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

dbADOCommand.parameters.append dbADOParameter

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.

Set dbADOParameter = New ADODB.Parameter

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	Description	PL / SQL Type	VBA Type
adArray				array
adBigInt	20	8 byte signed integer
adBinary	128	a binary value
adBoolean	11	a boolean value		boolean
adByRef
adBSTR	8	a null terminated character string	varchar2
adChapter	136	4 byte character value for a child recordset
adChar	129	a string value (ensure it is long enough)	varchar	string
adCurrency	6	currency format		currency
adDate	7	a date value, number of days since 12/30/1899		date
adDBDate	133	YYYYMMDD date format
adDBFileTime
adDBTime
adDBTimeStamp
adDecimal*	14	number with fixed precision and scale		decimal
adDouble	5	double precidion floating point		double
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	a signed integer (4 bytes)	natural, number	integer
adIUnknown	13	currently not supported by ADO
adLongVarBinary	205
adLongVarChar	201
adLongVarWChar	203
adNumeric*	131
adPropVariant	138
adSingle	4			single
adSmallInt	2	2 byte signed integer	integer	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	string	varchar
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

Set dbADOParameter = dbADOCommand.CreateParameter("TypeName", adVarChar, adParamInput, 129, Null) 
dbADOCommand.Parameters.Append dbADOParameter

Example � Creating & Appending a parameter in one step

Public dbADOCommand as ADODB.Command 

dbADOCommand.CommandText = "ProcedureName" 
dbADOCommand.CommandType = adCmdStoredProc 
dbADOCommand.ActiveConnection = dbADOConnection 
dbADOCommand.Parameters.Append dbADOCommand.CreateParameter("ParameterName", _ 
                                                         datatype, _ 
                                                        adParamInput, _ 
                                                        12, _ 
                                                        "ParameterValue") 
dbADORecordSet.Open dbADOCommand

Example � Stored Procedure - No Parameters

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

Public dbADOCommand as ADODB.command 
Public dbADORecordset as ADODB.recordset 

Set dbADOCommand = New ADODB.Command 
dbADOCommand.Name = "CommandName" 
dbADOCommand.CommandText = "ProcedureName" 
dbADOCommand.CommandType = ADODB.adCmdStoredProc 
dbADOCommand.ActiveConnection = ADODB.dbADOConnect 

Set dbADORecordSet = New ADODB.Recordset             
Set dbADORecordSet = dbADOCommand.Execute 

Set dbADOCommand = Nothing 
Set dbADORecordset = Nothing

Example � Stored Procedure - 1 Parameter

Public dbADOCommand as ADODB.command 
Public dbADORecordset as ADODB.recordset 
Public dbADOParamter as ADODB.Parameter 

Set dbADOCommand = New ADODB.Command                  
dbADOCommand.CommandText = "ProcedureName" 
dbADOCommand.CommandType = adCmdStoredProc 
dbADOCommand.ActiveConnection = dbADOConnect 

Set dbADOParameter = New ADODB.Parameter            
Set dbADOParameter = dbADOCommand.CreateParameter("ParameterName1", _ 
                                                  datatype, _ 
                                                  ADODB.adParamInput, _ 
                                                  size, _ 
                                                  "ParameterValue1") 
dbADOCommand.Parameters.Append dbADOParameter 
Set dbADOParamter = Nothing 

Set dbADORecordSet = New ADODB.Recordset             
Set dbADORecordSet = dbADOCommand.Execute 

Set dbADOCommand = Nothing 
Set dbADORecordset = Nothing

Example � Stored Procedure - 2 Parameters

Example � Stored Function - 1 Parameter

Public dbADORecordset as ADODB.recordset 
Public dbADOCommand as ADODB.command 
Public dbADOParameter as ADODB.parameter 
Public vReturnValue as Variant 

Set dbADOPCommand = New ADODB.Command              
dbADOCommand.CommandText = "FunctionName" 
dbADOCommand.CommandType = adCmdStoredProc 

Set dbADOParameter = New ADODB.Parameter              
Set dbADOParameter = dbADOCommand.CreateParameter("ReturnedName", _ 
                                                  datatype, _ 
                                                  ADODB.adParamOutput, _ 
                                                  size) 
dbADOCommand.Parameters.Append dbADOParameter 
Set dbADOParameter = Nothing 

Set dbADOParameter = dbADOCommand.CreateParameter("ParameterName1", _ 
                                                  datatype, _ 
                                                  ADODB.adParamInput, _ 
                                                  size, _ 
                                                  "ParameterValue1") 
dbADOCommand.Parameters.Append dbADOParameter 
Set dbADOParameter = Nothing 

Set dbADORecordSet = New ADODB.Recordset             
dbADORecordSet = dbADOCommand.Execute 
vReturnValue = dbADOCommand("ReturnedName") 

Set dbADOCommand = Nothing 
Set dbADORecordset = Nothing

Example - Stored Function - 2 Parameters

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

Public dbADORecordset as ADODB.recordset 
Public dbADOCommand as ADODB.command 
Public dbADOParameter as ADODB.parameter 
Public vReturnValue as Variant 

Set dbADOCommand = New ADODB.Command                
dbADOCommand.CommandText = "FunctionName" 
dbADOCommand.CommandType = adCmdStoredProc 

Set dbADOParameter = New ADODB.Parameter               
Set dbADOParameter = dbADOCommand.CreateParameter("ParameterName1", _ 
                                                  datatype, _ 
                                                  ADODB.adParamInput, _ 
                                                  size, _ 
                                                  "ParameterValue1") 
dbADOCommand.Parameters.Append dbADOParameter 
Set dbADOParameter = Nothing 

Set dbADOParameter = dbADOCommand.CreateParameter("ParameterName2", _ 
                                                  datatype, _ 
                                                  ADODB.adParamInput, _ 
                                                  size, _ 
                                                  "ParameterValue2") 
dbADOCommand.Parameters.Append dbADOParameter 
Set dbADOParameter = Nothing 

Set dbADOParameter = dbADOCommand.CreateParameter("ReturnedName", _ 
                                                  datatype, _ 
                                                  ADODB.adParamOutput) 

dbADOCommand.Parameters.Append dbADOParameter 
Set dbADOParameter = Nothing 

Set dbADORecordSet = dbADOCommand.Execute 
vReturnValue = dbADOCommand("ReturnedName") 

Set dbADOCommand = Nothing 
Set dbADORecordset = Nothing