Microsoft KB Archive/245482

= PRB: Empty Strings Causes ADO Update Error Against Oracle Servers =

Article ID: 245482

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6

-



This article was previously published under Q245482



SYMPTOMS
When updating ADO recordsets against an Oracle database back end the following error may occur:

"The specified row could not be located for updating; Some values may have been changed since it was last read."



CAUSE
This error is seen because Oracle stores an empty string as a NULL value.



RESOLUTION
The following two workarounds are available:


 * 1) Explicitly convert empty strings to NULL before editing the recordset object.
 * 2) Call the Resynch method after editing the recordset.



STATUS
This behavior is by design.



MORE INFORMATION
This problem is seen due to the difference in behavior between the Oracle Storage Engine and ADO with respect to empty strings. Oracle is interpreting a zero length buffer as NULL. This problem can be reproduced with the following piece of code and can be replicated against any Oracle server irrespective of its version.

Execute the following ADO code against an Oracle server: Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection cnn.Open "Provider=MSDAORA;Data Source=myoracleserver;User ID=myuserid;Password=mypassword" cnn.Execute "CREATE TABLE mytable (a int primary key, b varchar2(5) null)" cnn.Execute "INSERT INTO mytable VALUES (5, 'value')"

Set rst = New ADODB.Recordset rst.CursorLocation = adUseClient rst.Open "SELECT * FROM mytable", cnn, adOpenStatic, adLockOptimistic rst.MoveFirst rst(1) = "" rst.Update rst(1) = "new" rst.Update ' Error occurs here

rst.Close cnn.Execute "DROP TABLE mytable" cnn.Close

Set rst = Nothing Set cnn = Nothing

The error is seen at the second update statement in the code. This is because the Oracle Engine converts the empty string to NULL. The ADO Recordset stores the empty string as a zero length buffer.

Keywords: kbnofix kboracle kbprovider kbdatabase kbprb KB245482

-

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

© Microsoft Corporation. All rights reserved.