Knowledge Base

FIX: Oracle Numeric Fields Must Be Converted for Logical Tests

Article ID: 178043

Article Last Modified on 3/2/2005

Microsoft ActiveX Data Objects 1.0
Microsoft ActiveX Data Objects 1.5
Microsoft ActiveX Data Objects 2.0
Microsoft ActiveX Data Objects 2.1 Service Pack 2
Microsoft ActiveX Data Objects 2.5

This article was previously published under Q178043

SYMPTOMS

When you use an Oracle numeric field a "Type Mismatch" error occurs.

The error occurs in the following two scenarios:

When you conduct a logical test comparing an Oracle numeric field with an integer value. The following is an example:
```
      If Oracle_Recordset("my_numeric_field")=1 then ...
```
-or-
When you perform integer arithmetic with an Oracle numeric field. The following is an example:
```
     x=Oracle_Recordset("my_numeric_field")+ 1 
```

CAUSE

The following two conditions cause this behavior:

Storing Integer data in an Oracle Numeric data type field.
Using the value of the Oracle Numeric field in computations with Integer data types.

Oracle does not have an Integer data type, only Numeric. The problem is that numeric types are data with exact precision and scale and must be converted to another data type comparable to integers. Floating point numbers are approximate values and can be compared to integers without exception. For numerics (or the older and equivalent decimal data type) if this conversion does not take place a type mismatch error occurs.

RESOLUTION

The following conversion functions return values that are comparable to integers:

CInt()
CLng()
CDbl()
CSng()

For maximum efficiency and flexibility, the CLng() function is recommended.

In the scenarios listed in the SYMPTOMS section, making the following changes correct the problem:

If CLng(Oracle_Recordset("my_numeric_field"))=1 then ...

x=CLng(Oracle_Recordset("my_numeric_field"))+ 1

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was corrected in MDAC 2.1 SP2.

MORE INFORMATION

Oracle uses a numeric data type. If an integer value is stored in a numeric field, some ODBC drivers (for example, Intersolv version 3.01) returns a data type of double-precision floating point (that is, VBScript VarType of 5); other drivers (for example, Microsoft ODBC for Oracle version 2.73.7269) more accurately returns numeric data type (that is, VBScript VarType 14). In all cases, the ADO Type property returns type 131 (that is, numeric).

Keywords: kbbug kbfix kboracle kbqfe kbmdac210sp2fix kbdatabase kb3rdparty kbado210sp2fix kbmdacnosweep KB178043