VBScript runtime error (Type Mismatch) when you perform a numeric comparison on an adNumeric field type
This article helps you to resolve an error (type mismatch) that occurs when you use Visual Basic Scripting Edition (VBScript) to perform a numeric comparison or calculation on an
adNumeric (131) field type.
Original product version: Visual Basic Script
Original KB number: 306916
When you use VBScript to perform a numeric comparison or calculation on an
adNumeric (131) field type, you may receive the following error messages:
Error message 1
Microsoft VBScript runtime error '800a000d'
Error message 2
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. This behavior is by design.
You can use one of the following two possible workarounds:
- Use the
CIntfunction to convert the
- Use JScript, which does not exhibit this 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.
Step 1 to reproduce the behavior: 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;
Step 2 to reproduce the behavior: Create the VBS file
In Notepad, create a new text document named Test.vbs, and paste the following code into test.
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.
On your desktop, double-click Test.vbs to run the code. You receive the Type Mismatch error message.
Uncomment the following line of code, which converts the
adNumericfield 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, then you receive two message boxes and no error messages.