ADO - ADO Parameter Data Types and Precision Handling

Introduction

In ActiveX Data Objects (ADO), parameters are used to pass values safely and efficiently from an application to a database through an ADO Command object. Every parameter has a data type that tells ADO and the database what kind of value is being supplied. For example, a parameter may contain an integer, string, date, decimal value, or binary data.

Parameter data type and precision handling becomes particularly important when working with numerical values such as currency, percentages, measurements, or financial calculations. If the parameter's data type, precision, or scale does not match the database column, values may be rounded, truncated, converted incorrectly, or rejected by the database.

1. What Is an ADO Parameter?

An ADO parameter represents a value that is supplied to a parameterized SQL statement or stored procedure.

For example:

Dim cmd As ADODB.Command
Dim param As ADODB.Parameter

Set cmd = New ADODB.Command

cmd.CommandText = _
    "SELECT * FROM Products WHERE Price > ?"

Set param = cmd.CreateParameter( _
    "MinPrice", _
    adCurrency, _
    adParamInput, _
    , _
    500)

cmd.Parameters.Append param

Here, MinPrice is an input parameter. Its value is 500, and its data type is adCurrency.

Instead of inserting the value directly into the SQL statement, the application passes it through the parameter. This provides better type control and helps avoid SQL injection problems associated with constructing SQL statements from untrusted text.

2. Why Parameter Data Types Matter

ADO supports several parameter data types through the DataTypeEnum enumeration.

Common examples include:

ADO Data Type Typical Purpose
adInteger Integer numbers
adSmallInt Smaller integer values
adBigInt Large integer values
adDouble Floating-point numbers
adSingle Single-precision floating-point numbers
adDecimal Fixed-precision decimal numbers
adNumeric Exact numeric values
adCurrency Currency and monetary values
adVarChar Variable-length character data
adVarWChar Variable-length Unicode text
adDate Date and time values
adBoolean True/false values
adBinary Binary data

Selecting the appropriate type helps ADO communicate the intended value correctly to the database provider.

For example, a monetary amount should generally not be treated as an ordinary string:

"1250.75"

It is better represented using an appropriate numeric or currency parameter type.

3. Precision and Scale

For decimal and numeric parameters, two properties are especially important:

  • Precision

  • Scale

Precision represents the total number of significant digits that can be stored.

Scale represents the number of digits allowed after the decimal point.

For example, consider:

12345.67

This value contains seven digits in total and two digits after the decimal point.

Therefore:

Precision = 7
Scale     = 2

A database column defined conceptually as:

DECIMAL(10,2)

can contain up to 10 total digits, with 2 digits after the decimal point.

Examples of values that could fit include:

12345678.90
99999999.99

The exact range also depends on the database system and its numeric implementation.

4. Setting Precision and Scale in ADO

When creating a decimal or numeric parameter, it may be necessary to explicitly configure precision and scale.

For example:

Dim p As ADODB.Parameter

Set p = cmd.CreateParameter( _
    "Amount", _
    adDecimal, _
    adParamInput)

p.Precision = 10
p.NumericScale = 2
p.Value = 1250.75

cmd.Parameters.Append p

In ADO, the property used for scale is NumericScale.

This tells the provider that the parameter is intended to represent a decimal value with the specified precision and number of decimal places.

5. Difference Between Precision and Scale

The distinction can be understood with the following example:

DECIMAL(12,3)

Here:

12 = Precision
 3 = Scale

This means that the value can have up to 12 digits in total, of which 3 can occur after the decimal point.

For example:

12345678.901

contains:

8 digits before the decimal point
3 digits after the decimal point
12 digits total

Therefore, it fits the DECIMAL(12,3) structure.

6. Why Incorrect Precision Can Cause Problems

Suppose a database expects:

DECIMAL(12,2)

but the application sends a parameter with inappropriate precision or scale.

Potential problems include:

  • Data being rounded.

  • Decimal digits being lost.

  • Conversion errors.

  • Overflow errors.

  • Provider-specific errors.

  • Unexpected results in calculations.

  • Values being stored differently from what the application intended.

For example, if an application intends to store:

1250.789

in a field designed for two decimal places, the database may round the value to:

1250.79

The exact behavior depends on the database and provider.

7. Precision Handling for Financial Applications

Precision is particularly important in applications involving:

  • Banking

  • Accounting

  • Invoicing

  • Payroll

  • Billing

  • Tax calculations

  • Financial reporting

Consider a product price:

1499.95

A parameter should represent this value using an appropriate exact numeric type rather than relying on an unsuitable floating-point representation.

For example:

Set p = cmd.CreateParameter( _
    "Price", _
    adDecimal, _
    adParamInput)

p.Precision = 12
p.NumericScale = 2
p.Value = 1499.95

cmd.Parameters.Append p

Using an exact decimal representation can help avoid some of the binary floating-point representation issues associated with types such as Double.

8. Precision Versus Floating-Point Types

ADO provides types such as adSingle and adDouble for floating-point values.

These types are useful for scientific and engineering calculations where a floating-point representation is appropriate.

However, floating-point numbers are not always ideal for financial values.

For example:

Dim amount As Double
amount = 0.1 + 0.2

The internal binary representation of floating-point numbers can produce a value that is extremely close to, but not necessarily exactly, the mathematical result expected.

For financial applications, fixed-precision numeric types such as decimal or currency are generally more appropriate.

9. Parameter Size Is Different from Precision

Another important distinction is between Size and Precision.

Size generally describes the maximum size of character or binary data.

For example:

Set p = cmd.CreateParameter( _
    "CustomerName", _
    adVarWChar, _
    adParamInput, _
    100, _
    "John Smith")

Here:

Data type = adVarWChar
Size      = 100

The Size property is concerned with the length of the text or binary value.

For numeric parameters, precision and scale are more relevant.

Therefore:

Text parameter  → Size
Decimal number  → Precision + NumericScale

These properties should not be confused with one another.

10. Matching ADO Parameters with Database Columns

A good practice is to make the ADO parameter compatible with the database column.

Suppose a database table contains:

ProductPrice DECIMAL(10,2)

The application should create a parameter with compatible characteristics:

Set p = cmd.CreateParameter( _
    "ProductPrice", _
    adDecimal, _
    adParamInput)

p.Precision = 10
p.NumericScale = 2
p.Value = 2499.50

cmd.Parameters.Append p

This minimizes unnecessary conversions between the application and database.

11. Parameter Type Conversion

ADO works through an OLE DB provider, so parameter values can sometimes undergo type conversion between the application, ADO, provider, and database.

For example, an application might supply:

"2500.50"

as a string even though the database expects a decimal value.

The provider may convert the string to a numeric value.

However, depending on the provider, locale, and database configuration, conversions can produce unexpected results.

For example, decimal separators can vary between locales:

2500.50

versus:

2500,50

Explicitly using an appropriate numeric parameter type reduces the dependence on implicit conversions.

12. Input, Output, and Return Parameters

Precision and data type considerations also apply to output parameters.

For example:

Set p = cmd.CreateParameter( _
    "TotalAmount", _
    adDecimal, _
    adParamOutput)

p.Precision = 12
p.NumericScale = 2

cmd.Parameters.Append p

The stored procedure can then return a decimal value through this parameter.

The parameter's data type should be compatible with the value returned by the stored procedure.

13. Common Mistakes

Using String Parameters for Numbers

A common mistake is passing numerical values as text:

Set p = cmd.CreateParameter( _
    "Amount", _
    adVarChar, _
    adParamInput, _
    20, _
    "1250.75")

Although conversion may work, this is less precise than using an appropriate numeric type.

Using Double for Monetary Values

Another common mistake is using:

adDouble

for every type of numerical value, including money.

For financial data, an exact decimal or currency representation is often preferable.

Ignoring Scale

Suppose the database expects:

DECIMAL(10,2)

but the application sends values with several decimal places without considering the scale.

This can result in rounding or truncation.

Relying Entirely on Implicit Conversion

Allowing the database provider to determine how a string should be converted into a numeric value can produce inconsistent behavior, especially when different database providers or regional settings are involved.

14. Best Practices

When working with ADO parameter data types and precision:

  1. Use a parameter type that matches the database column.

  2. Use decimal or currency types for monetary values where appropriate.

  3. Set precision explicitly for decimal and numeric parameters when required by the provider.

  4. Set NumericScale according to the database column's scale.

  5. Do not confuse Size with precision.

  6. Avoid unnecessary string-to-number conversions.

  7. Be careful when using floating-point types for financial calculations.

  8. Keep input and output parameter definitions compatible with the stored procedure.

  9. Test parameter behavior with the actual OLE DB provider and database system being used.

  10. Check for rounding, truncation, and overflow when handling values near the limits of the database column.

Conclusion

ADO parameter data types and precision handling determine how values are represented and transferred between an application and a database. While basic parameters may only require choosing an appropriate data type, decimal and numeric parameters require additional attention to precision and scale.

Understanding the difference between DataType, Size, Precision, and NumericScale helps developers prevent incorrect conversions, rounding problems, truncation, and database errors. This is especially important for financial and other applications where numerical accuracy is critical.