Solved

ADO: Row cannot be located for updating. Some values may have been changed since it was last read.

Posted on 2003-12-11
19
321 Views
Last Modified: 2010-05-26
Hi
I'm using VB6 and access97,
I have an ADODC on one of my forms,
when I try to update with :
Adodc1.recordset.Update I get the following error:

Run time error
-2147217864
Row cannot be located for updating. Some values may have been changed since it was last read.

my form contains a comboBox  with user names, the adodc presents each user's records.
(by clicking the combo and choosing a different name I'm refreshing the adodc with a new query)

The interesting thing is:
THE ERROR OCCURS ONLY when trying to update the first usre's records,
for other users it works fine.

This is a microsoft bug, I read this article:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q300/5/86.ASP&NoWebContent=1
(microsof solution:
RESOLUTION: To resolve this problem, remove the default value that is specified for the numeric field in the Access database table.)

and did what was suggested, but it still doesnt work.
any ideas?
Thanks.
0
Comment
Question by:zolpo
19 Comments
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9919957
I believe that this may relate to a problem with the primary index field.  When updating the record are you absolutely sure that the primary index field is valid.
0
 
LVL 1

Author Comment

by:zolpo
ID: 9920083
Hi,
I dont have a promary key in that table,
Thanks
0
 
LVL 4

Accepted Solution

by:
TomLaw1999 earned 150 total points
ID: 9920218
That is why you are getting the error. Set one of the fields as a primary (if necessary add an autonumber field) and the problem should be resolved.
0
 
LVL 1

Author Comment

by:zolpo
ID: 9920273
Hi,
I will try that solution ,
But  how come I get the error only for the first set of records?
thanks
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9920338
I found the same problem in one of my projects, it seemed to be intermittent and varied according to which service pack I had installed. Setting a primary field solved it for me.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 9920394
reading..
0
 
LVL 1

Author Comment

by:zolpo
ID: 9920650
Hi,
I defined a primary key, now the error occurs only when changing one  of the fields,
(text box that holds the hour)
The error happens when trying to change that certain field, or when trying to change any field after
the error first time occured.
Thanks
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9920772
If you have set an auto number as the primary field then you need to check the hour field before it is updated. If necessary set a default value for this field before it is edited. It sounds like you may be passing an invalid value (possibly a null).

If the primary field is not an auto number then I suggest that you check both the value of the hour field and the index field.
0
 
LVL 1

Author Comment

by:zolpo
ID: 9920909
hi,
I have set the primary key as auto number,
what do you mean by: "check the hour field before it is updated" ?
what do I need to check? and how do I check it?

Thanks
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9921210
You need to check that the value you are passing from hour text box is appropriate for the Access data field. This can be accomplished in a number of ways depending upon the way your program has been set up. Things to check:

1) Is the access field a date/time field or a long/Integer?
2) Interrupt your program on the Adodc1.WillChangeRecord event and examine the value passed to the hour field before it is updated. In fact it would probably be wise to check the value of all fields before the update takes place.
0
 
LVL 1

Author Comment

by:zolpo
ID: 9937466
Hi,
I dont understand what the problem is, because I'm printing the hour value in Adodc1.willChangeRecord, and it seems alright, more then that, everything is working fine for all users I have (the user's records) except the first user's record.
any ideas?
thanks a lot!
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9937546
Are you using the latest service pack (VB6/SP5). There were a few problems with ADO originally but most of them got sorted out by SP5.
0
 
LVL 1

Author Comment

by:zolpo
ID: 9941361
hi,
Yes, I am using sp5,
Thanks
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9942182
Only other things that come to mind are:

1) Is the code short enough to post so that we can look at the problem directly.

2) Would upgrading the data to Access 2000 be a viable proposition?
0
 
LVL 1

Author Comment

by:zolpo
ID: 9942304
Private Sub txtEnter_GotFocus()
    oldVal = txtEnter.Text
End Sub
****************************************
on lost focus i check if the value entered to check box is legal, if not- remaining the last value
****************************************
Private Sub txtEnter_LostFocus()
    If Not IsDate(txtEnter.Text) Then
        'MsgBox ("ערך לא חוקי")
        Debug.Print "not legal"
        txtEnter.Text = Format(oldVal, "short time")
    Else
        Call setHours
    End If
End Sub
******************************************
calculating time difference exit-enter and returning in a
string format
******************************************
'setting (exit - enter) in lblHours and lblSumHours
Private Sub setHours()

Dim diff As Double
Dimm totShifHrs as Double
If IsDate(txtEnter.Text) And IsDate(txtExit.Text) Then
    diff = TimeMinuteDiff(txtEnter, txtExit)
    'סהכ שעות במשמרת
    totShifHrs = Format(diff / 60, "Standard")
    lblHours.Caption = hoursFromMinutes(diff)
    lblSumHours.Caption = totShifHrs
End If
End Sub
****************************************
'returning the hours as a string hh:mm
****************************************
Public Function hoursFromMinutes(total_min As Double) As String
    Dim hours As Double
    Dim mafrid As String
    Dim h As Integer
    Dim minutes As Integer
    mafrid = ":"
    hours = total_min \ 60
   
    minutes = total_min - (hours * 60)
    If minutes < 10 Then
        mafrid = mafrid & "0"
    End If
    hoursFromMinutes = CStr(hours) & mafrid & CStr(minutes)
End Function
******************************************
'returning startTime - endTime as minutes
******************************************
Public Function TimeMinuteDiff(Stime, Etime) As Long
Dim StartTime As Date, EndTime As Date

StartTime = TimeValue(Stime)
EndTime = TimeValue(Etime)
If EndTime < StartTime Then
    EndTime = DateAdd("d", 1, EndTime)
End If
TimeMinuteDiff = DateDiff("n", StartTime, EndTime)
End Function

*************************************************
setHours, hoursFromMinutes and TimeMinuteDiff are Just to return the time difference as a string.


Private Sub btn_Update_Click()
On Error GoTo errUpdate
     Adodc1.Recordset.Update *************** problem here
     Adodc1.Refresh
     Exit Sub

errUpdate:
    Debug.Print Err.Number
    Debug.Print Err.Description
         
End Sub

Results:

-2147217864
Row cannot be located for updating. Some values may have been changed since it was last read.

I dont think that other parts of the code are relevant couse the problem is only when
changing txtEnter

THANKS A LOT!!!
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9942554
A suggestion.
Just before the line

Adodc1.Recordset.Update *************** problem here

insert a messagebox to show the values of all of the fields in the relevant table (before the update happens). Do all the field values look OK?.
0
 
LVL 1

Author Comment

by:zolpo
ID: 9949262
Hi Tom,
I tried what u suggested but it all seemed to be allright,
I decided to delete the first record from the db,
and now it work for athor users.
Thanks A LOT for all your help!
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9949810
Thank you Zolpo - glad you got it sorted.
0
 

Expert Comment

by:bravekind
ID: 32849910
I had a similar problem and aloo found the article ID 300586 'row cannot be Located for updating" with the solution of removing the default value for the specified numeric fiield in the access database.
This did not work for me either. However, this solution did:
In the code where the update occurs (could also be a movefirst or a movelast), before updating, check that each numeric value has been assingned a value. I used 0 if blank. This worked for me.

    ' fix for 'row cannot be located for updating'. The default 0 does not work.
    ' See Article ID 300586 bug error 'Row cannot be located for updating'
    Dim i As Integer
    For i = 0 To 27
        If Len(Trim(txtSlot(i).Text)) = 0 Then
            txtSlot(i).Text = "0"
        End If
    Next

I am guessing that the bug is that the default numeric value set in the database design does not work, so you need to enter the data manually where needed.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now