PRB: ActiveX Data Objects Update Method Fails When the SELECT Statement Contains a Subquery

• Avoid sub-queries when you build an updateable rowset.
• Set the cursor location to use client-side cursors.

SELECT fld1, fld2E FROM table1 WHERE EXISTS 
		(SELECT * FROM table2 WHERE table2.fld1 = table1.fld1)
				

Steps to Reproduce the Behavior

1. Create a system data source names (DSN) to connect to an Oracle 7.3.x/8x server that uses Microsoft ODBC Driver for Oracle (Msorcl32.dll) version 2.573.4303 or earlier.
2. Use SQL*PLUS, an Oracle command line utility, to run the following SQL statements:

   
   CREATE TABLE EMPTEMP(ID NUMBER(18, 0) CONSTRAINT PK_EMP 
   PRIMARY KEY,NAME VARCHAR2(255));
   CREATE TABLE JOBTEMP(EMP_ID NUMBER(18, 0) CONSTRAINT NL_JOB_1 
   NOT NULL,
       JOB_NAME VARCHAR2(255));
   ALTER TABLE JOBTEMP ADD CONSTRAINT FK_JOB_EMP_ID FOREIGN KEY (EMP_ID)
        REFERENCES EMPTEMP (ID) ON DELETE CASCADE;
   INSERT INTO EMPTEMP VALUES (1, 'Marinko');
   INSERT INTO EMPTEMP VALUES (2,  'Andreas');
   
   INSERT INTO JOBTEMP VALUES (1, 'work hard');
   INSERT INTO JOBTEMP VALUES (1, 'drink a coffee');
   INSERT INTO JOBTEMP VALUES (2, 'work a lot');
   						

3. Use the following code to create an ADO-based Visual Basic 6.0 project with a command button on the form. (See the comments in the code for information on how to reproduce and resolve the error.)

   Option Explicit
   Const SQL_GOOD As String ="SELECT ROWID, ID, NAME FROM EMPTEMP"
   Const SQL_BAD As String = "SELECT ROWID, ID, NAME FROM EMPTEMP " & _
                             "WHERE EXISTS (SELECT * FROM JOBTEMP " & _
                             "WHERE JOBTEMP.EMP_ID = EMPTEMP.ID AND " & _
                             "JOBTEMP.JOB_NAME = 'work hard')"
   
   Private Sub Command1_Click()
   
   
       Dim cn As New ADODB.Connection
       Dim rs As New ADODB.Recordset
       Dim er As ADODB.Error
       
       On Error GoTo ErrorHandler
       ' You need to change the DSN to valid the DSN on your system.
       cn.Open "DSN=main80;UID=demo;PWD=demo"
      
       'If this is uncommented, you do not encounter any problems.
   irrespective of the sql statement
       'rs.CursorLocation = adUseClient 
   
       'Toggle between the two SQL statements by 
   interchanging the comment on the two statements.
   
       'rs.Open SQL_BAD, cn, adOpenKeyset, adLockOptimistic
       rs.Open SQL_GOOD, cn, adOpenKeyset, adLockOptimistic    
   
       Debug.Print "support updates", rs.Supports(adUpdate)
   
       rs.Fields("name").Value = "www"
       rs.Update
       
       rs.Close
       cn.Close
       
   Exit Sub
       
   ErrorHandler:
   
       Debug.Print "run-time errors"
       Debug.Print "---------------"
       With Err
           Debug.Print .Description
           Debug.Print .Number
       End With
       
       Debug.Print "ado errors"
       Debug.Print "---------------"
       For Each er In cn.Errors
           With er
               Debug.Print "Description:", .Description
               Debug.Print "NativeError:", .NativeError
               Debug.Print "Number:", .Number
               Debug.Print "Source:", .Source
               Debug.Print "SQLState:", .SQLState
           End With
       Next
       
   End Sub