Problem updating Oracle table from Visual Basic

From: Don Reed <d_reed_at_hp.com>
Date: Sun, 16 Aug 1998 17:50:14 -0400
Message-ID: <35D75416.88328B4C@mindspring.com>

I'm using Oracle 7.3 on NT 4.0, and trying to write an application in Visual Basic 5.0 Professional Edition to update records in the demo database table SCOTT.EMP, after creating a System DSN name "Emp" pointing
to the ORCL SID.

Here's the VB5 code:

---

  Set wsOracle = CreateWorkspace("OraWorkSpace", "scott", "tiger", dbUseODBC)

  Workspaces.Append wsOracle

  Set conOracle = wsOracle.OpenConnection("OraConnection", dbDriverNoPrompt, _

     False, "ODBC;DATABASE=orcl;DSN=Emp")

  SQL$ = "SELECT empno,ename FROM Emp"
  Set rsOracle = conOracle.OpenRecordset(SQL$, dbOpenDynaset,0)   rsOracle.MoveFirst
  Debug.Print rsOracle!ename, rsOracle!empno

  rsOracle.Edit                                 <----- fails here

  rsOracle!ename = "SMYTHE"
  rsOracle.Update

---

The "debug.print" works, so I'm connecting succesfully.

The procedure fails at the 'Edit' statement with:

     "Run-time error '3027':
      Can't update.  Database or object is read-only."

User 'scott' can update the record in SQL Plus.

I have tried setting the OpenRecordset type parameter to dbOpenDynamic, dbOpenSnapshot,
and dbOpenForwardOnly with the same results.

I have tried setting the OpenRecordset options to dbExecDirect, and have tried all
the different OpenRecordset lockedit parameters as well.

Interestingly, I am able to update the record by substituting the following SQL and
OpenRecordset, and removing all the statements after OpenRecordset:

  SQL$ = "UPDATE Emp SET ename = 'SMYTHE' WHERE empno = 7369"   Set rsOracle = conOracle.OpenRecordset(SQL$, dbOpenDynaset,0)

I have been searching USENET and have found numerous posts from others having the
same problem, with numerous solutions offered. I have tried all the solutions and
none have fixed the problem.

Can anyone help?

Thanks,

Don Reed
reed_at_mindspring.com Received on Sun Aug 16 1998 - 16:50:14 CDT