Re: Is MDAC2.1 working with Oracle 7.3.4 ?

From: Sheldon Wang <swang_at_online-can.com>
Date: Fri, 31 Mar 2000 09:42:08 -0700
Message-ID: <38e4d51d_5@news.cadvision.com>

Thanks very much for your response.

I found the kb article q188574. It appears that my situation is different from the case in the article. I do not use stored procedures, no ADO command objects are involved.

I just want to use an ADO recordset to insert a number into a NUMBER field in a Oracle table (Personal Oracle 7.3.4). The procedures to recreate the problem are:

1. Create a table, MY_TEST, with a field, TEST1 NUMBER(4,2)
2. Create a Data Source Name using ODBC adminstrator, mydatabase
3. Start a new VB project, set reference to Microsoft ActiveX Data Objects 2.1 Libray
4. put the following code in the click event of a Command button

Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "select * from MY_TEST", _

        "DSN=mydatabase;UID=dba/dba_admin", _
        adOpenDynamic, adLockOptimistic
rs.AddNew

rs.Fields("test1").Value = 33.55
rs.Update

End Sub

4. Run the VB project, as soon as the rs.Update is executed, the error will occur as follows:

Run-time error '-2147217917 (80040e03)' [Microsoft][ODBC driver for Oracle][Oracle] ORA-01722: invalid number

I am quite puzzled why this does not work. This works fine with MDAC2.0, but not MDAC2.1.

I would appreciate very much any help.

Sheldon Wang

K Stahl <BlueSax_at_Unforgetable.com> wrote in message news:38E3BF75.1DE83CB1_at_Unforgetable.com...
> Sheldon Wang wrote:
> >
> > I ran into an interesting problem with my VB5.0 app that uses MDAC2.1 to
> > connect to Oracle 7.3.4. The app uses an ADODB.RECORDSET to update a
table
> > in Oracle 7.3.4. However, it seems that the RECORDSET can not update a
> > number field in the table if the number had decimals. For instance, the
> > field is defined as NUMBER(4.2), the following update causes error:
> > ORA-01722 invalid number.
> >
> > recordset.Fields(FieldName).Value = 1.01 (no problem if this value
is
> > a whole number, say, 29)
> > recordset.Update
> >
> > Strangely, previously I was using MDAC2.0. Everything worked fine. The
> > version of msorcl32.dll in MDAC2.0 is 2.573.2927.0 which works. The
version
> > of msorcl32.dll in MDAC2.1 is 2.573.4202.0 which does not work.
> >
> > Thanks very much in advance.
> >
> > Sheldon Wang
>
> Try looking at these kb article: q216978, q188574.
>
> The latter one suggests that you need to set the NumericScale property
> correctly for the column that contains the decimal value.
Received on Fri Mar 31 2000 - 10:42:08 CST