PRB: Oracle Servers Convert Empty Strings to NULL (225070)


The information in this article applies to:

  • ActiveX Data Objects (ADO) 1.5
  • ActiveX Data Objects (ADO) 2.0
  • ActiveX Data Objects (ADO) 2.01
  • ActiveX Data Objects (ADO) 2.1
  • ActiveX Data Objects (ADO) 2.1 SP2
  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.6
  • ActiveX Data Objects (ADO) 2.7
This article was previously published under Q225070

SYMPTOMS

When you update a record that contains a variable-length string field that was previously set to the empty string, you receive the following Visual Basic run-time error 80040e38:
The specified row could not be located for updating; Some values may have been changed since it was last read.

CAUSE

When setting a field to the empty string, Oracle servers automatically convert the empty string to NULL, but the object requesting the edit is not notified. The next time an edit is performed, the empty string is used to identify the record to update, and it is not found because the server now contains a NULL.

RESOLUTION

Two methods to resolve this follow:
  • Convert empty strings to NULL before editing a recordset object and submitting the update.

    -or-
  • Execute the Recordset object's Resync method after editing.

MORE INFORMATION

The following Visual Basic (ActiveX Data Objects [ADO]) code illustrates the problem. The error occurs at the second Update method call.

Note that although this sample uses the Microsoft OLE DB Provider for Oracle, this behavior also occurs with the Microsoft ODBC Driver for Oracle. It is also possible to see the server's behavior with Oracle's SQL Plus. Issue an INSERT statement involving an empty string (''), and then issue a SELECT statement. The results of the SELECT will show NULL.

Steps to Reproduce Behavior

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.Open "Provider=MSDAORA;Data Source=mydatasource;User ID=myid;Password=mypassword"
cnn.Execute "CREATE TABLE mytable (a NUMBER PRIMARY KEY, b VARCHAR2(10))"
cnn.Execute "INSERT INTO mytable VALUES (100,'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

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

Set rst = Nothing
Set cnn = Nothing
The simplest workaround for this problem is to use the Resync method on the recordset object as illustrated by this code example: 
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.Open "Provider=MSDAORA;Data Source=mydatasource;User ID=myid;Password=mypassword"
cnn.Execute "CREATE TABLE mytable (a NUMBER PRIMARY KEY, b VARCHAR2(10))"
cnn.Execute "INSERT INTO mytable VALUES (100,'value')"

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

rst.Resync

rst(1) = "new"
rst.Update

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

Set rst = Nothing
Set cnn = Nothing
Modification Type:MajorLast Reviewed:8/23/2001
Keywords:kbDatabase kbDSupport kbOracle kbprb KB225070
©2002 Microsoft Corporation. All rights reserved.