Microsoft KB Archive/262934

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

Article ID: 262934

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q262934



SYMPTOMS
When you call the Update method of an ActiveX Data Objects (ADO) recordset that is opened on a SQL statement that also contains a sub-select using the Microsoft ODBC Driver for Oracle, then the following error message occurs:

[Microsoft][ODBC driver for Oracle]&quot;Cannot use Keyset-driven cursor on join, with distinct clause, union, intersect or minus or on read only result set&quot; State:S1C00,Native:0

This error message does not occur if a SQL statement does not contain a sub-select.



RESOLUTION
Following are two possible work-arounds for this behavior:
 * Avoid sub-queries when you build an updateable rowset.
 * Set the cursor location to use client-side cursors.



MORE INFORMATION
This error message occurs with all Microsoft ODBC Driver for Oracle versions. The following is an example of SQL statement with a sub-select:

SELECT fld1, fld2E FROM table1 WHERE EXISTS (SELECT * FROM table2 WHERE table2.fld1 = table1.fld1) Microsoft ODBC Driver for Oracle returns the following run-time error when you use the Update method in ADO on a recordset using a SQL statement containing a sub-select, and when client side cursors are not used:

The operation requested by the application is not supported by the provider.

You can view this Microsoft ODBC Driver for Oracle error message in the ODBC trace logs.

Steps to Reproduce the Behavior
 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.  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');   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 =&quot;SELECT ROWID, ID, NAME FROM EMPTEMP&quot; Const SQL_BAD As String = &quot;SELECT ROWID, ID, NAME FROM EMPTEMP &quot; & _ &quot;WHERE EXISTS (SELECT * FROM JOBTEMP &quot; & _                         &quot;WHERE JOBTEMP.EMP_ID = EMPTEMP.ID AND &quot; & _                          &quot;JOBTEMP.JOB_NAME = 'work hard')&quot;

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 &quot;DSN=main80;UID=demo;PWD=demo&quot; '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 &quot;support updates&quot;, rs.Supports(adUpdate)

rs.Fields(&quot;name&quot;).Value = &quot;www&quot; rs.Update rs.Close cn.Close Exit Sub ErrorHandler:

Debug.Print &quot;run-time errors&quot; Debug.Print &quot;---&quot; With Err Debug.Print .Description Debug.Print .Number End With Debug.Print &quot;ado errors&quot; Debug.Print &quot;---&quot; For Each er In cn.Errors With er           Debug.Print &quot;Description:&quot;, .Description Debug.Print &quot;NativeError:&quot;, .NativeError Debug.Print &quot;Number:&quot;, .Number Debug.Print &quot;Source:&quot;, .Source Debug.Print &quot;SQLState:&quot;, .SQLState End With Next End Sub 

The Microsoft ODBC Driver for Oracle does not support positioned updates or deletes for SQL statements containing sub-selects.

Additional query words: updateable updatable nested sub

Keywords: kbnofix kboracle kbdatabase kbprb KB262934

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.