The Command Object - The NumericScale Property
(Page 5 of 8 )
This property indicates the scale of numeric values for the Parameter object.
Byte = Parameter.NumericScale
Parameter.NumericScale = Byte
The numeric scale indicates how many digits are to the right of the decimal point.
Both the NumericScale and Precision properties are required to process SQL data of numeric type correctly. Both Oracle and SQL Server 7.0 support numeric types, using both native OLE DB Providers and the OLE DB Provider for ODBC.
Furthermore, note that the NumericScale and Precision properties cannot be set via the Command object's CreateParameter method. If you need to set these properties (for example, in the scenario described in the previous paragraph), you must create an explicit Parameter object and set its properties before appending it to the Parameters collection.
The Precision Property This property indicates the degree of precision for numeric values in the Parameter object.
Byte = Parameter.Precision
Parameter.Precision = Byte
The precision indicates the maximum number of digits used to represent a numeric value.
The Precision property, like the NumericScale property, is required for the correct processing of SQL data of numeric type-as supported by both Oracle and SQL Server 7.0, using both native OLE DB Providers and the OLE DB Provider for ODBC. There's more about this in the previous section on the NumericScale property.
The Size Property This property indicates the maximum size, in bytes or characters, of a Parameter object.
Long = Parameter.Size
Parameter.Size = Long
Watch out for a few things when using the Size property:
- For variable-length parameters (such as character strings or binary data), you must always set the Size so that the provider knows how much space to allocate for the parameter. If you don't specify the size, an error is generated.
- If you use the Parameters collection's Refresh method to force the provider to fill in the parameter details, the Size for variable-length parameters may be at their maximum potential size, and memory may be allocated for these parameters accordingly.
- For binary data you have to be quite specific about the size of the parameter. For example, if a SQL Server stored procedure has a parameter of type image, and you Refresh the parameters, the size of this parameter is returned as 2147483647. If you create the parameters yourself and use this size, and then use AppendChunk to add the data to the parameter, there are no problems, but creating the parameter with the actual size of the binary data doesn't work. That's because Size is the maximum size of the parameter, not its actual size.
The Type Property This property indicates the data type of the Parameter object.
DataTypeEnum = Parameter.Type
Parameter.Type = DataTypeEnum
A full list of the DataTypeEnum values is shown in Appendix B, but the following table lists the data types used by the SQL Server and Jet providers and shows how the underlying data store's data types map to those of ADO. The empty table cells indicate that there is no direct mapping between the two database types.
There are a few interesting (and often confusing) things to note. For example, date parameters in SQL Server don't map to the obvious adDate data type, but rather to the adDBTimeStamp data type. Also, the timestamp maps to adVarBinary for SQL Server 6.5 and adBinary for SQL Server 7.0. You should also note that adInteger maps to Visual Basic's Long data type; it doesn't fit VB's Integer type. If the parameters are of the wrong type, they can cause your commands to fail.
If you wish to create your parameters by using the CreateParameter method, but you're having trouble matching data types or sizes, then the simplest fix is to temporarily call the Refresh method and examine the Parameters collection. You can do this in VBScript by looping through the collection or by using the Locals window in Visual Basic. You can then copy the values that ADO has used and amend your code accordingly. Don't forget to remove the Refresh once you've sorted out your parameters, because leaving it in will cause a performance penalty.
You can find more on data types in Appendix E.
The Value Property
This property indicates the value assigned to the Parameter object.
Variant = Parameter.Value
Parameter.Value = Variant
This is the default property and can be omitted if desired.
The value of a parameter can be read only once, and the recordset should be closed before you read the value (depending upon the Output Parameter Availability dynamic property of the Connection). Reading a value more than once returns an empty value. For more details on this, see the "Retrieving Output Parameters" section on parameter values at the end of this chapter.
For a parameter holding binary data, you can use the Value property to set its value instead of using the AppendChunk method. For example:
objRec.Parameters("@Logo").Value = varChunk
A full description of using binary data appears at the end of Chapter 8.
Next: The Parameters Collection >>
More Database Articles
More By Apress Publishing
|
 This article is excerpted from ADO Programmer's Reference by David Sussman (Apress, 2004; ISBN: 1590593421). Check it out at your favorite bookstore. Buy this book now.
|
|
