PRB: Oracle Numeric Fields Must be Converted for Logical Tests

 Last reviewed: December 17, 1997
Article ID: Q178043
The information in this article applies to:
  • ActiveX Data Objects (ADO), versions 1.0, 1.5

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 above, making the following changes would correct the problem:

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

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

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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          : kb3rdparty VIADO VIODBC
Technology        : kbInetDev
Version           : WINDOWS:1.0,1.5
Platform          : WINDOWS
Issue type        : kbprb

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 17, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.