Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

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

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
zolpo
Asked:
zolpo
1 Solution
 
TomLaw1999Commented:
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
 
zolpoAuthor Commented:
Hi,
I dont have a promary key in that table,
Thanks
0
 
TomLaw1999Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
zolpoAuthor Commented:
Hi,
I will try that solution ,
But  how come I get the error only for the first set of records?
thanks
0
 
TomLaw1999Commented:
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
 
ajexpertCommented:
reading..
0
 
zolpoAuthor Commented:
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
 
TomLaw1999Commented:
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
 
zolpoAuthor Commented:
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
 
TomLaw1999Commented:
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
 
zolpoAuthor Commented:
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
 
TomLaw1999Commented:
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
 
zolpoAuthor Commented:
hi,
Yes, I am using sp5,
Thanks
0
 
TomLaw1999Commented:
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
 
zolpoAuthor Commented:
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
 
TomLaw1999Commented:
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
 
zolpoAuthor Commented:
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
 
TomLaw1999Commented:
Thank you Zolpo - glad you got it sorted.
0
 
bravekindCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now