Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 306916 - Last Review: December 3, 2003 - Revision: 3.1
PRB: VBScript "Type Mismatch" Error When Field Type Is adNumeric
This article was previously published under Q306916
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
These error messages occur because VBScript cannot properly convert
adNumeric values to a valid numeric type.
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.
This behavior is by design.
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
- 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
- Modify the connection string so it points to your Oracle server and provides a valid user name and password.
- 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.
- Close Test.vbs.
- On your desktop, double-click Test.vbs to run the code. You receive the "Type Mismatch" error message.
Workaround
- Uncomment the following line of code, which converts the adNumeric field to a double data type:
'MsgBox "Numeric field * 100 = " & cdbl(oRS("Custid")) * 100
- Comment the following line of code:
MsgBox "Numeric field * 100 = " & oRS("CustID") * 100
- Close and save Test.vbs.
- On your desktop, double-click Test.vbs to run the code again. Notice that you receive two message boxes and no error messages.
APPLIES TO
- Microsoft Data Access Components 1.5
- Microsoft Data Access Components 2.0
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft Active Server Pages 4.0
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate