Updating a recordset
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. In ADO, this object is the most important and the one used most often to manipulate data from a database.When you first open a Recordset, the current record pointer will point to the first record and the BOF and EOF properties are False.-- PRB: Oracle Servers Convert Empty Strings to NULL The information in this article applies to: Active X Data Objects (ADO), versions 1.5, 2.0, 2.01, 2.1, 2.1 SP2, 2.5 SYMPTOMS The Visual Basic Run-time error 80040e38, which follows, occurs when updating a record containing a variable-length string field previously set to the empty string: The specified row could not be located for updating; Some values may have been changed since it was last read. Close Set rst = Nothing Set cnn = Nothing The simplest work around for this problem is to use the Resync method on the recordset object as illustrated by this code example: Dim cnn As ADODB. Execute "INSERT INTO mytable VALUES (100,'value')" Set rst = New ADODB. 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. I'll stick with my own Clone RSStructure which is fine apart from the initial hit on loading which I can live with.
MORE INFORMATION The following Visual Basic (Active X Data Objects [ADO]) code illustrates the problem. Note that although this sample uses the Microsoft OLE DB Provider for Oracle, this behavior also occurs with the Microsoft ODBC Driver for Oracle. Execute "INSERT INTO mytable VALUES (100,'value')" Set rst = New ADODB.
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. Open "Provider=MSDAORA; Data Source=mydatasource; User ID=myid; Password=mypassword" cnn.
It works 100%, until i correct an error in the "Order Qty" field, in the "Fis_Capture T" table. I may start to capture data and on completion of that specific row, I may notice an error.
My question - how can i change the "Order Qty" in the main capturing table - " Fis_Capture T" and then it updates this same record in realtime in the 2nd table - "Fis_Capt Data T", without creating another record when changing it. I then need to go back to the row with the error, change the quantity and it is updated to the 2nd table.