Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. PRB: Numbered Positioned Updates Against Oracle8i Database Server Fails

PRB: Numbered Positioned Updates Against Oracle8i Database Server Fails

View products that this article applies to.

Symptoms

When you attempt to update or insert records into an Oracle8i server by using a NUMBER(x,y) datatype field, you receive the following error:
[Microsoft][ODBC Driver for Oracle][Oracle]ORA-01722: invalid number

↑ Back to the top

Cause

The Microsoft Oracle ODBC Driver incorrectly parses for the decimal separator when connecting to an Oracle8i server.

↑ Back to the top

Resolution

The current versions of the Microsoft Oracle Open Database Connectivity (ODBC) Driver and OLE DB Providers were not supported in an Oracle8i environment. Use the Microsoft OLE DB Provider for Oracle to work around this INSERT problem; however, you may run into other incompatibilities. You may also hard code the INSERT and UPDATE statements and run them off the command object.

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

More information

Steps to Reproduce Behavior

The following Visual Basic code shows the problem. The Oracle server must be an 8i server or the error does not occur. Place the code in the Visual Basic Form_Load method. Make sure to add the Microsoft ActiveX Data Objects 2.1 Library on the Project, References menu. 

    Dim cn As New Connection, rs As New Recordset
    
    strTable = "ztest"
    'Using the OLEDB Provider instead of the ODBC Driver
    'allows you to get around this "invalid number" error, i.e.
    'cn.ConnectionString = "Provider=MSDAORA.1;Password=yourpwd;User ID=yourUID;Data Source=myDSN;"
    cn.ConnectionString = "Provider=MSDASQL.1;Password=yourpwd;User ID=yourUID;Data Source=myDSN;"
    cn.Open
    
    On Error Resume Next
    cn.Execute "drop table " & strTable
    On Error GoTo 0
    cn.Execute "Create Table " & strTable & "(FNUMB NUMBER(38, 4) NULL, PrimaryKey NUMBER(19, 0) PRIMARY KEY)"
    rs.CursorLocation = adUseClient
    rs.Open strTable, cn, adOpenKeyset, adLockOptimistic
    rs.AddNew
    rs.Fields("fnumb").Value = 22.22
    rs.Fields("PrimaryKey").Value = 1
    rs.Update                       '<-- error!  (invalid number)

↑ Back to the top

Keywords: KB238849, kbprb, kboracle, kbdriver, kbdatabase

↑ Back to the top

Article Info
Article ID : 238849
Revision : 4
Created on : 12/5/2003
Published on : 12/5/2003
Exists online : False
Views : 455