Type Mismatch Reading Number From Oracle
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: Type Mismatch Reading Number From Oracle

Hybrid View

  1. #1
    Join Date
    Nov 2002
    Location
    Oakville, Ontario
    Posts
    48

    Type Mismatch Reading Number From Oracle

    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

  2. #2
    Join Date
    Jan 2003
    Location
    North Carolina
    Posts
    309
    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.

  3. #3
    Join Date
    Nov 2002
    Location
    Oakville, Ontario
    Posts
    48
    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

  4. #4
    Join Date
    Jan 2003
    Location
    North Carolina
    Posts
    309
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center