ADO Parameter Object Properties and Collections

[Previous] [Next]

The purpose of the Parameter object is to enable you to reuse a query while changing a small piece of the query. For example, you could build a query such as the following to select a single customer record based on the value of the CustomerID field, and then execute that query multiple times, changing only the value of the CustomerID each time the query is executed:

 SELECT * FROM Customers WHERE CustomerID = ? 

If you're using a parameterized query, you can change the value without having to rebuild the entire query string. In short, the Command object can use parameters the same way a function can use parameters. The following table shows the properties and collections of the Parameter object.

Parameter Object Properties and Collections
Property or Collection Name	Data Type	Description
Attributes	Long	Describes some of the characteristics of the Parameter object
Direction	ParameterDirectionEnum	Indicates which type of parameter you're using—input, output, input/output, or return
Name	String	Contains the name of the Parameter object
NumericScale	Byte	Indicates the numeric scale for numeric data
Precision	Byte	Indicates the precision for numeric data
Properties	Collection of Property objects	Contains dynamic properties
Size	Long	Returns the defined size for a field
Type	DataTypeEnum	Returns the data type for a field
Value	Variant	Contains the current value for a field

Attributes Property

The Attributes property describes some of the properties of the parameter, such as whether it accepts Null values. The Attributes property uses a long integer to store the sum of values from the ParameterAttributesEnum enumeration shown in this next table. This property is read/write.

ParameterAttributesEnum Values
Constant	Value	Description
adParamSigned	16	The data type of the parameter is signed; it applies to numeric data types.
adParamNullable	64	The parameter accepts Null as its value.
adParamLong	128	The parameter accepts long string or binary data.

Direction Property

Most programming languages have different parameter types that control whether data is passed to and/or from a procedure. Many databases also utilize these concepts. ADO uses the Direction property on the Parameter object to denote the direction in which data will be passed.

Not all databases support all the settings available in ADO. You might need to supply parameter direction information even if you're using the Refresh method on the Parameters collection to retrieve information about your parameters. For example, SQL Server lets you use the OUTPUT keyword to specify that the parameter can return data. However, there is no way to specify whether the parameter is used for output only or for both input and output.

The SQL Server OLE DB provider (or ODBC driver) will assume that the parameter is for both input and output. If you neglect to supply a value for the parameter before executing the query, you'll receive an error. You can set the Direction property to any of the values in ParameterDirectionEnum, shown in the following table. If you want the parameter to be output-only, you'll need to manually set the Direction property on the Parameter object to adParamOutput. Of course, you're better off populating the Parameters collection to begin with, so this shouldn't be an issue for you, right?

ParameterDirectionEnum Values
Constant	Value	Description
adParamUnknown	0	Direction is unknown.
adParamInput	1	Default; parameter is input-only.
adParamOutput	2	Parameter is output-only.
adParamInputOutput	3	Parameter is input/output.
adParamReturnValue	4	Parameter is the return value for a stored procedure.

Name Property

The Name property can help you locate the Parameter object in the Parameters collection and improve the readability of your code. Beyond that, you don't need to use it. As long as the parameters on your SQL Server stored procedures have names, you do not need to set the Name property of your Parameter objects to the same value.

NumericScale and Precision Properties

Like the Field object (discussed in Chapter 4), the Parameter object exposes NumericScale and Precision properties. If you're using variable-length numeric data types, you'll want to set the NumericScale and Precision properties accordingly. These are probably the only properties on the Parameter object that you might want to set that aren't available on the CreateParameter method of the Command object. For more information on these properties, see the discussion of the Field object in Chapter 4.

If you're having trouble setting these properties correctly, here are a couple of suggestions for finding the correct settings:

• If the property corresponds to a field in one of your tables, query that table and examine the settings for the NumericScale and Precision properties on the Field object in the returned Recordset.
• Call Parameters.Refresh once (if your OLE DB provider or ODBC driver supports that functionality), and check the value of the NumericScale and Precision properties.

Use the values you retrieve from either of these suggestions to set these properties in your code.

Properties Collection

Like most ADO objects, the Parameter object also exposes a Properties collection. I have yet to see this collection populated, but if a provider wanted to expose some provider-specific properties, that information could be available in this collection.

Size Property

The Parameter object's Size property corresponds to the Field object's DefinedSize property (discussed in Chapter 4): it applies to variable length string and binary data types. For ANSI and Unicode strings, the value of this property determines the number of characters, rather than the number of bytes, that the Parameter object can store.

Type Property

The Type property for the Parameter object corresponds to the Type property for the Field object. If you're unsure of the data type to use for a parameter, use one of the two guidelines listed above for the NumericScale and Precision properties (but query the Type property, of course).

Value Property

The Value property stores the value of the parameter in a Variant, similar to the Value property on the Field object. Like the Field object, the default property on the Parameter object is the Value property.