FIX: Oracle Numeric Fields Must be Converted for Logical Tests

ID: Q178043

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0, 2.1 SP2

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 ...

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 this to be a problem in the Microsoft products listed at the beginning of this article.

This problem has been resolved 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).

Additional query words:

Keywords : kb3rdparty kbADO100 kbADO150 kbADO200 kbDatabase kbOracle kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2fix kbMDAC210SP2fix
Version : WINDOWS:1.0,1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbbug