IsaacRU
asked on
Updating ADO Recordset
I have an ADO Recordset, and i'm trying to edit a record in it. The problem is that when I try to edit a newly added record I receive this error message:
Run-time error'-2147217864 (80040e38)':
The specified row could not be located for updating: Some values may have been changed since it was last read.
However, if I restart my project and then try to edit the record it works.
I'm willing to give 100 points to the person who can tell me how the fix this problem.
Run-time error'-2147217864 (80040e38)':
The specified row could not be located for updating: Some values may have been changed since it was last read.
However, if I restart my project and then try to edit the record it works.
I'm willing to give 100 points to the person who can tell me how the fix this problem.
try using the .Update method after adding the new record
Do a recordset.Resynch method after your Addnew. From MSDN:
-------------------------- ---------- --
PRB: Oracle Servers Convert Empty Strings to NULL
The information in this article applies to:
ActiveX 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.
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=mypasswor d"
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 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.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.Open "Provider=MSDAORA;Data Source=mydatasource;User ID=myid;Password=mypasswor d"
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
--------------------------
PRB: Oracle Servers Convert Empty Strings to NULL
The information in this article applies to:
ActiveX 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.
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=mypasswor
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 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.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.Open "Provider=MSDAORA;Data Source=mydatasource;User ID=myid;Password=mypasswor
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If using a Database different than Access (like SqlSerrver or Oracle) ensure that you a unique key on the Table
I agree with wsh2.
I had the same problem and resync solved it.
I had the same problem and resync solved it.
i HAD THIS WITH Sybase and the primary key did the job
Does that mean I get my first points ???
I am brand-new here
I am brand-new here
ASKER
Thanks for all your comments. I'll try each one out and then give out the points
Thanks
Thanks
ASKER
By the way AzraSound. I did call the update method after I added the record. But this did not solve the problem
ASKER
angelIII i'm using Access is my database system. Will i still need a primary key?
wsh2's answer solved another question that was the exact same as yours just a few minutes ago...i imagine that is it
ASKER
When I tried to use the ADO Recordsets Resync method I got this error:
Run-time error '-2147467259 (80004005)':
Insufficient key column infomation for updating or refreshing.
Run-time error '-2147467259 (80004005)':
Insufficient key column infomation for updating or refreshing.
its a combination of errors produced from
a) what wsh2 said
b) what angelIII said
a) what wsh2 said
b) what angelIII said
ASKER
Good point AzraSound.
For your question of Primary Key:
Yes, you need a key!
You should have Primary Keys on every table...
I think you shouldn't need Resync...
Yes, you need a key!
You should have Primary Keys on every table...
I think you shouldn't need Resync...
well someone just posted a question about getting the exact same error:
The specified row could not be located for updating.Some
values may have been changed since it was last read
and the solution was to use resynch
The specified row could not be located for updating.Some
values may have been changed since it was last read
and the solution was to use resynch
ASKER
I agree with you angelIII cause when i tried resync i got errors. AzraSound what was the title of the question you were talking about?
Hi AzraSound,
I assume that you know ADO :-)
If yes, you certainly run already into the nice thing that ADO raises many errors, but with the same error number.
Even the error text is sometimes the same grrrr
More even, the error text is depending on same language settings, wich makes it difficult to simply analyse the Err.Description....
(developer's) life would be too easy, and our income too low :-)
I assume that you know ADO :-)
If yes, you certainly run already into the nice thing that ADO raises many errors, but with the same error number.
Even the error text is sometimes the same grrrr
More even, the error text is depending on same language settings, wich makes it difficult to simply analyse the Err.Description....
(developer's) life would be too easy, and our income too low :-)
its now in the "answered section" the accepted answer was the exact post the wsh2 has on this question...as a matter of fact, upon seeing the question i simply posted a link over to this question for the questioner to reference...i believe the question was named ".Update method and ADO Recordset"
you have an income? well we're two different entities all together then my friend
ASKER
angelIII you were right, when I added a primary key to the table it allowed the updates. Thanks Alot
ASKER
Adjusted points from 100 to 110
It was a pleasure
ASKER
AzraSound, even though your answer was did not work for me, I was impressed how you got that information. Thanks