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

NameTypeDescription
HelpContextPropertyReturns the context ID of a topic in the Microsoft Windows help system
HelpFileProperty 
Native ErrorProperty 
NumberPropertyReturns a unique number identifying the error
SourceProperty 
DescriptionProperty 
SQL StatePropertyReturns a 5 character SQL error number
AppendPropertyAdds a new parameter to the Collection


Type
Value
Size
Direction


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


ParameterValueDescriptionPL / SQL TypeVBA Type
adArray   array
adBigInt208 byte signed integer  
adBinary128a binary value  
adBoolean11a boolean value boolean
adByRef    
adBSTR8a null terminated character stringvarchar2 
adChapter1364 byte character value for a child recordset  
adChar129a string value (ensure it is long enough)varcharstring
adCurrency6currency format currency
adDate7a date value, number of days since 12/30/1899 date
adDBDate133YYYYMMDD date format  
adDBFileTime    
adDBTime    
adDBTimeStamp    
adDecimal*14number with fixed precision and scale decimal
adDouble5double precidion floating point double
adEmpty0no value  
adError032 bit error code  
adFileTime64number of 100-nanosecond intervals since 1/1/1601  
adGUID72global unique identifier  
adIDispatch9currently not supported by ADO  
adInteger3a signed integer (4 bytes)natural, numberinteger
adIUnknown13currently not supported by ADO  
adLongVarBinary205   
adLongVarChar201   
adLongVarWChar203   
adNumeric*131   
adPropVariant138   
adSingle4  single
adSmallInt22 byte signed integerintegerinteger
adTinyInt161 byte signed integer  
adUnsignedBigInt218 byte unsigned integer  
adUnsignedInt194 byte unsigned integer  
adUnsignedSmallInt182 byte unsigned integer  
adUnsignedTinyInt171 byte unsigned integer  
adUserDefined132user defined variable  
adVarBinary204binary value  
adVarChar200stringvarchar 
adVariant12automation variant  
adVarChar    
adVarNumeric139variable width exact numeric with signed scale  
adVarWChar202null - terminated Unicode character string  
adWChar130null - 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



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext