Link to home
Start Free TrialLog in
Avatar of ict-torquilclark
ict-torquilclark

asked on

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

I am using the attatched cde to update a DB table but i get the folloowing error

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

can anybody help?
Dim rs As New ADODB.Recordset
        Dim sqlstr As String

        rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        sqlstr = "SELECT * FROM PensionCategories WHERE PensionCategoryRef = 0"
        rs.Open(sqlstr, sqlCnn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
        rs.AddNew()
        rs.Fields("PensionCategoryName").Value = CategoryName
        rs.Fields("BandEmployer").Value = BandErs
        rs.Fields("BandEmployee").Value = BandEes
        rs.Fields("ActualEmployer").Value = ActualErs
        rs.Fields("ActualEmployee").Value = ActualEes
        rs.Fields("EmployerRef").Value = EmployerRef
        rs.Update()
        rs.Close()

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of oleggold
oleggold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ict-torquilclark
ict-torquilclark

ASKER

the only thing i can see from those is that it doesnt update the record because there is no promary key

shouldnt rs.addnew() deal with that?

i have used this method for years and have never had to add a primary key manutally before?
I usually use BatchOptimistic:

        oRs.Open(SQL, Connection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic)

Then the update instead is

        oRs.UpdateBatch()

The Dynamic flag instead of the Static may help as well