DreamingEagle
asked on
ADO: ROW CANNOT BE LOCATED FOR UPDATING...SOME VALUES MAY
Here is the entire error message: "Error -2147217864. Row cannot be located for updating. Some values may have changed since it was last read."
I have written a system for a small business using VB with and .MDB on the backend which is addressed using the data environment. Despite the usual minor errors in the code which occur with any new software, the system is solid, effective, and meets the business objectives---were it not for his error.
The error occurs in different modules at different times. Sometimes simply restarting the program on the same workstation and going back to the same module and editing the same record will result in flawless execution. It always occurs on an edit.
The application is brower based using DataGrid and ADO data controls. The error always occurs on an edit of a record selected from the grid. The present coding pattern is as follows: Open the record set using the DE. Pass the DE.recordset to the ADO data control. Address the DE.recordset .row when populating the controls on the edit form and update the DE.recordset.row from the controls on a Save. I have also tried addressing the ADO.Datacontrol.Recordset.
Getting everyone out and repairing/compacting the .mdb always makes the problem disappear for a while. Tentative conclusion: something in either the Data Environment or the code is allowing the .mdb to get corrupted. But what?
Is this a bug in the code? A bug in the data enrivonment? A bug in ADO.
I would add that we have run the latest Service Pack, have created new set-up programs and run them on all workstations. The Server is an NT server, and the workstations run under Windows 98.
The problem can only very rarely be duplicated when running the app from VB, and the few times it has occurred, the .update was the highlighted line. But it also occurs sometimes when the edit form is first called, before any .updates are attempted.
I have written a system for a small business using VB with and .MDB on the backend which is addressed using the data environment. Despite the usual minor errors in the code which occur with any new software, the system is solid, effective, and meets the business objectives---were it not for his error.
The error occurs in different modules at different times. Sometimes simply restarting the program on the same workstation and going back to the same module and editing the same record will result in flawless execution. It always occurs on an edit.
The application is brower based using DataGrid and ADO data controls. The error always occurs on an edit of a record selected from the grid. The present coding pattern is as follows: Open the record set using the DE. Pass the DE.recordset to the ADO data control. Address the DE.recordset .row when populating the controls on the edit form and update the DE.recordset.row from the controls on a Save. I have also tried addressing the ADO.Datacontrol.Recordset.
Getting everyone out and repairing/compacting the .mdb always makes the problem disappear for a while. Tentative conclusion: something in either the Data Environment or the code is allowing the .mdb to get corrupted. But what?
Is this a bug in the code? A bug in the data enrivonment? A bug in ADO.
I would add that we have run the latest Service Pack, have created new set-up programs and run them on all workstations. The Server is an NT server, and the workstations run under Windows 98.
The problem can only very rarely be duplicated when running the app from VB, and the few times it has occurred, the .update was the highlighted line. But it also occurs sometimes when the edit form is first called, before any .updates are attempted.
That's a good article Azra, but kinda long. The main point to note in summary...
There are some limitations to client-side cursors: They're static and lack support for pessimistic locking. Once the Open method on a recordset with a client-side cursor returns, the only server reference that's retained is the server connection, and even that is released for a disconnected recordset. Any server-side locks or cursor features used to create the recordset are freed up immediately.
Basically for a multi-user application where there is the possibility of multiple users may have overlapping/conflicting recordset updates, I think you save trouble by using Server-Side cursors instead of client side, as that way the server manages the conflict. Of course, I mostly think client side cursors are a bad idea anyway, and that this in fact inhibits scalability.
There are some limitations to client-side cursors: They're static and lack support for pessimistic locking. Once the Open method on a recordset with a client-side cursor returns, the only server reference that's retained is the server connection, and even that is released for a disconnected recordset. Any server-side locks or cursor features used to create the recordset are freed up immediately.
Basically for a multi-user application where there is the possibility of multiple users may have overlapping/conflicting recordset updates, I think you save trouble by using Server-Side cursors instead of client side, as that way the server manages the conflict. Of course, I mostly think client side cursors are a bad idea anyway, and that this in fact inhibits scalability.
These are from MSDN
PRB: Update of Same Record 2x w/Disconnect ADO Recordset Fails
Article ID: Q193515
See this article at
http://support.microsoft.com/support/kb/articles/Q193/5/15.ASP
These may be one the reason of the problem
For further reason see this article at the specified address
ADO recordsets maintain a copy of the original values that were returned from the database in the OriginalValue property. When the client updates a disconnected recordset locally, the Value property is modified and the OriginalValue property is left unchanged to reflect the value that was originally in the database. When the recordset is reconnected and an UpdateBatch is performed, the row to be updated is located on the server and the new value is changed and saved. The method by which this occurs may differ slightly depending on the version of the server database engine being used, and whether ODBC or OLE-DB is being used. In all cases though, the row is ultimately located and changed by using the value of the OriginalValue property in the WHERE clause of an UPDATE statement.
PRB: Update of Same Record 2x w/Disconnect ADO Recordset Fails
Article ID: Q193515
See this article at
http://support.microsoft.com/support/kb/articles/Q193/5/15.ASP
These may be one the reason of the problem
For further reason see this article at the specified address
ADO recordsets maintain a copy of the original values that were returned from the database in the OriginalValue property. When the client updates a disconnected recordset locally, the Value property is modified and the OriginalValue property is left unchanged to reflect the value that was originally in the database. When the recordset is reconnected and an UpdateBatch is performed, the row to be updated is located on the server and the new value is changed and saved. The method by which this occurs may differ slightly depending on the version of the server database engine being used, and whether ODBC or OLE-DB is being used. In all cases though, the row is ultimately located and changed by using the value of the OriginalValue property in the WHERE clause of an UPDATE statement.
<ping>
ASKER
I should have also provided the following information:
- The command objects in the Data Environment has the following settings on the Advanced Tab: 1) Cursor Type: Static; 2) Lock Type: Optimistic; 3) Cursor Location: Use ServerSide cursors. Recordset returning is checked. The command configuration frame contains default settings.
- Record sets are opened by first changing the .source property to the desired SQL statement, then .open is called. The recordset is then passed to the ADO Data control, and the ADO Data control recordset is, in turn, passed to the Datagrid.
- My own research led me to the article referenced in your posts, but I am not using disconnected recordsets. Should I be?
- The command objects in the Data Environment has the following settings on the Advanced Tab: 1) Cursor Type: Static; 2) Lock Type: Optimistic; 3) Cursor Location: Use ServerSide cursors. Recordset returning is checked. The command configuration frame contains default settings.
- Record sets are opened by first changing the .source property to the desired SQL statement, then .open is called. The recordset is then passed to the ADO Data control, and the ADO Data control recordset is, in turn, passed to the Datagrid.
- My own research led me to the article referenced in your posts, but I am not using disconnected recordsets. Should I be?
Learning. -e2
ASKER
Upping the points...this problem is still persisting, and I'm hoping for more ideas.
Hi, DreamingEagle.
I noticed that you are using a DataGrid and an ADO object. There is an issue related to this.
#Try This
1. "Datagrid"
http://209.207.250.147/showthread.php?threadid=14005
MyRecordset.Move (0) 'save to database
MyRecordset.Resync 'requery
'rebind your bound control
Set DataGrid1.DataSource = DataGrid1.DataSource
Still Looking. -e2
I noticed that you are using a DataGrid and an ADO object. There is an issue related to this.
#Try This
1. "Datagrid"
http://209.207.250.147/showthread.php?threadid=14005
MyRecordset.Move (0) 'save to database
MyRecordset.Resync 'requery
'rebind your bound control
Set DataGrid1.DataSource = DataGrid1.DataSource
Still Looking. -e2
ASKER
G2, it's too early to be sure, but yesterday morning I implemented that technique with the grid and a variation of the update methods used in the link you supplied. Had a peaceful day for a change. If another day or two passes, I'm going to accept your comment as the answer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep, thanks very much...!
Hi, DreamingEagle.
Just Curious.
Why the "B"?
Bye. -e2
Just Curious.
Why the "B"?
Bye. -e2
Grading tips...
Tip One: Always give an 'A' grade unless you have a clear reason not to do so.
Tip Two: Before giving less than an 'A' grade, tell the Experts that the solution is less than excellent and what grade you are prepared to assign. Give them the chance to improve their response for a better grade.
Tip Three: Always post a comment when accepting a grade. Update the Experts and let them know that the solution worked and why it worked.
Tip Four: Give your time to the Experts. Experts Exchange is a completely free site for you. The only thing we ask of you is a small amount of your time when you are managing your questions. In exchange for your time you get some of the very best help available in the world. It really requires very little time to post a comment but the pay off is well worth it when the Experts feel appreciated - see Tip One.
Tip Five: Always post a response to every post that each Expert makes. This keeps the Experts informed of your progress and the current state of the problem. You will never regret keeping the Experts informed on the status of your problem that they are trying to fix.
That is it. If you will follow those five tips, you will get the most out of Experts Exchange - The Best Site On The Internet!
Tip One: Always give an 'A' grade unless you have a clear reason not to do so.
Tip Two: Before giving less than an 'A' grade, tell the Experts that the solution is less than excellent and what grade you are prepared to assign. Give them the chance to improve their response for a better grade.
Tip Three: Always post a comment when accepting a grade. Update the Experts and let them know that the solution worked and why it worked.
Tip Four: Give your time to the Experts. Experts Exchange is a completely free site for you. The only thing we ask of you is a small amount of your time when you are managing your questions. In exchange for your time you get some of the very best help available in the world. It really requires very little time to post a comment but the pay off is well worth it when the Experts feel appreciated - see Tip One.
Tip Five: Always post a response to every post that each Expert makes. This keeps the Experts informed of your progress and the current state of the problem. You will never regret keeping the Experts informed on the status of your problem that they are trying to fix.
That is it. If you will follow those five tips, you will get the most out of Experts Exchange - The Best Site On The Internet!
http://www.pinnaclepublishing.com/VB/VBmag.nsf/37fd7b62c1dfc5d88525689600515109/5827bb239ff78638852568cd006c9f8a!OpenDocument