Type Mismatch Reading Number From Oracle

[Ahara]

Hello;

I am developping an app in ASP that connects to an Oracle database. I have just discovered the following problem:

When I read data from a column defined as data type 'Number' in Oracle, and I reference it in ADO say like...

if rs.Fields(2).Value <> 0 then


I get a type mismatch even though it would appear to be a number. If I do:

isNumeric(rs.Fields(2).Value) it returns false...BUT VB Script will allow me to do: Cint(rs.Fields(2).Value) and then it is a number that can be calculated. Very strange...I am new to Oracle and wondering if there is something I am missing. How can a field defined as 'number' be returned otherwise? And how is it that VB Script tells me it is not a number, but then goes ahead and converts it to an integer without throwing a type mismatch? Thanks much.

DA

[antares686]

What driver are you using to make the connection and have you validated the field itself is typed as numeric.

CInt will alow you to convert a number in a varchar2 field just fine but when you hit any alpha characters it will blow up your code.

Also, if you are using the MS For Oracle or an Older Oracle driver it may mistype the actual output. Make sure you are using the most current version of the Oracle drivers to ensure all old and newer datatypes are fully covered.

[Ahara]

Hi there;

I am using oraOLEDB driver, but I don't think this is the issue. The fields in question are defined as 'NUMBER' in the database. Notice I said 'fields' - this is occuring with all numeric fields. I use the CInt function and everything is fine - I would just like to know why. Is it possibly because the number is read back as a single and not an Integer? If I am comparing to variables, and the first variable is assigned the number 5, I am assuming that ASP's loosely typed framework will automatically see this variable as an integer from this point on. Maybe the number pulled from the Oracle table is seen as a single to ASP (??) ..... for eg.:

theVariable = 5

if theVariable = rs.Fields(0).Value then

......

rs.Fields(0).Value is definitely a number, but I get the type mismatch if I do not go Cint(rs.Fields(0).Value). Again, I just wanna know why!! Thanks.

DA

[antares686]

Duh, should have paid more attention. The reason I believe you have to do

Cint(rs.Fields(2).Value)

is because all datatypes returned thru ADO are of the Variant type (espeically ASP) which does not compare directly to a number. In the case of a number the Variant subtype is adNumeric.

CInt explicitly types the variant output to an integer.

Here is an MS KB Article on the problem.

PRB: VBScript "Type Mismatch" Error When Field Type Is adNumeric
ID: Q195180


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

Microsoft ODBC for Oracle (Build 2.573.2927), version 2.5
ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0
Microsoft Data Access Components versions 1.5, 2.0, 2.5
Microsoft Internet Information Server versions 3.0, 4.0
Active Server Pages
Microsoft ODBC Driver for Visual FoxPro, version 5.0

--------------------------------------------------------------------------------


SYMPTOMS
When you perform a numeric comparison or calculation on an adNumeric (131) field type using VBScript, the following errors may be returned:

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



CAUSE
The errors occur because VBScript cannot properly convert adNumeric values to a valid numeric type.



RESOLUTION
You can use either of the following two possible workarounds:



Convert the adNumeric field using CDbl() (or CInt()) as in the following example:

<%@ LANGUAGE="VBScript"%>
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "MyDSN", "MyUserID", "MyPassWord"
set oRS = oConn.Execute("Select list_price FROM DEMO.PRICE")
Response.Write("List Price * 100 = " & CDbl(oRS("list_price")) * 100)
%>


-or-


Use JScript, because JScript does not exhibit this behavior.