Q306916: PRB: VBScript "Type Mismatch" Error When Field Type Is adNumeric

Article: Q306916
Product(s): Open Database Connectivity (ODBC)
Version(s): 1.5,2.0,2.5,2.6,2.7
Operating System(s): 
Keyword(s): kbnokeyword kbGrpDSVBDB
Last Modified: 15-SEP-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Data Access Components versions 1.5, 2.0, 2.5, 2.6, 2.7 
- Microsoft Active Server Pages 
-------------------------------------------------------------------------------

SYMPTOMS
========

When you use Visual Basic Scripting Edition (VBScript) to perform a numeric
comparison or calculation on an adNumeric (131) field type, you may receive the
following error messages:

  Microsoft VBScript runtime error '800a000d'
  Type mismatch

  -and-

  Microsoft VBScript runtime error '800a01ca'
  Variable uses an Automation type not supported in VBScript

CAUSE
=====

These error messages occur because VBScript cannot properly convert adNumeric
values to a valid numeric type.

RESOLUTION
==========

You can use either of the following two possible workarounds:

- Use the CDbl or CInt function to convert the adNumeric field.

- Use JScript, which does not exhibit this behavior.

STATUS
======

This behavior is by design.

MORE INFORMATION
================

Steps to Reproduce the Behavior
-------------------------------

You can reproduce this behavior in an Active Server Pages (ASP) page or through a
simple Visual Basic Script (.vbs) file. The following steps demonstrate how to
reproduce the problem in a simple .vbs file.

Create the Oracle Table:

Run the following script on your Oracle server to create the sample table:

  DROP TABLE Cust;

              CREATE TABLE Cust
               (CustID     NUMBER(22,6) PRIMARY KEY,
                Name   VARCHAR2(50));

              INSERT INTO Cust VALUES(222,'Kent');

              INSERT INTO Cust VALUES(333,'Sally');

      COMMIT;
     / 

Create the VBS file:

1. In Notepad, create a new text document named Test.vbs, and paste the
  following code into Test.vbs:

       Set oConn = CreateObject("ADODB.Connection")
       oConn.open "Provider=MSDAORA;user id=User;" & _
       "password=password;data source=Oracle816Server;"

       set oRS = oConn.Execute("Select CustID FROM Cust")
       MsgBox "Numeric field type is 131." & vbcrlf & _
       "Field Type = " & ors.fields("CustID").type 

       MsgBox "Numeric field * 100 = " & oRS("CustID") * 100
      'MsgBox "Numeric field * 100 = " & cdbl(oRS("Custid")) * 100

2. Modify the connection string so it points to your Oracle server and provides
  a valid user name and password.

3. Save Test.vbs to your desktop. You should receive a warning that changing the
  extension may make the file unstable. Click OK to continue. If you do not see
  this warning, you may want to ensure that you are showing extensions for
  known file types.

4. Close Test.vbs.

5. On your desktop, double-click Test.vbs to run the code. You receive the "Type
  Mismatch" error message.

Workaround
----------

6. Uncomment the following line of code, which converts the adNumeric field to a
  double data type:

      'MsgBox "Numeric field * 100 = " & cdbl(oRS("Custid")) * 100

7. Comment the following line of code:

      MsgBox "Numeric field * 100 = " & oRS("CustID") * 100

8. Close and save Test.vbs.

9. On your desktop, double-click Test.vbs to run the code again. Notice that you
  receive two message boxes and no error messages.

Additional query words: 800a000d 800a01ca

======================================================================
Keywords          : kbnokeyword kbGrpDSVBDB 
Technology        : kbAudDeveloper kbASPsearch kbMDACSearch kbMDAC150 kbMDAC200 kbMDAC250 kbMDAC260 kbMDAC270
Version           : :1.5,2.0,2.5,2.6,2.7
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.

Copyright Microsoft Corporation 1986-2002.