[RESOLVED] "Row cannot be located for updating" - when updating db recursively

[iPrank]

In the following code I'm recursively saving current vieworder of a Treeview's nodes to a Access2000 db. I'm using ADO 2.8. (The original code was for saving treeview vieworder in a text file. I found it somewhere on the web)

At first I was getting an error,
-2147217864 : Row cannot be located for updating. Some values may have been changed since it was last read.

I tried using .Requery, Close-Reopen, .Update, but nothing worked. Then finally the .Resync method worked. I'm not sure why it worked !

My problem is, this Requery process is painfully slow. Every time user adds a node and the treeview has, say, >10 nodes, it is taking 10-20 seconds ! Every time user adds/deletes/sorts/drags node, I have to update their viewing index in db.

I don't have very much experience in database programming. (My instinct says it may have something to do with recursion and stack.)

1. Can anyone please tell me why that Resync method works and why everything else fails even if I perform a CommitTrans ?

2. Can anyone plese help me make this code faster.

Thanks !

Friend Sub ParseTree(objNode As Node)
 
    ' Recursively updates TreeIndex field in DB according to the node's level
    
    '----------------------------------------
    ' Update DB
    rs.MoveFirst
    cnn.BeginTrans
    ' The Treeview Keys are  "_N21" or similar
    rs.Find "ID = " & Right$(objNode.Key, Len(objNode.Key) - 2)
    rs!TreeIndex = ViewIndex 'ViewIndex is a global var to track current node
    '                         placement in the treeview
    
    '[b]ToDo: This Resync process takes too much time.
    ' If I comment this:
    '[color=red] ERROR -2147217864 : Row cannot be located for updating. _[/color]
     '[color=red] Some values may have been changed since it was last read.[/color]
    rs.Resync [/b]
    
    cnn.CommitTrans
    '----------------------------------------
    
    ViewIndex = ViewIndex + 1
    
    ' Check to see if the current node has children
    If objNode.Children > 0 Then
        ' Pass the first child node to the print routine
        ParseTree objNode.Child
    End If
 
    ' Set the next node to print
    Set objNode = objNode.Next
    ' As long as we have not reached the last node in
    ' a branch, continue to call the print routine
 
    If TypeName(objNode) <> "Nothing" Then
        ParseTree objNode
    End If
 
End Sub

[si_the_geek]

1). You should be using .Update followed by CommitTrans.

If you are getting errors with the .Update, let us know (I don't use .Find myself, so that may cause issues).

[iPrank]

Yes I'm having problem with Update. That's why I used Resync.

[si_the_geek]

what problem(s) are you having with update? Is it the error you posted?

[iPrank]

Yes. It is the same error.

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

[iPrank]

ONLY resync works. But it is too slow.

[si_the_geek]

Well there's two possible issues I can think of, the first is that .Find is doing something to cause it (you could try updating the first record without it to see if that works).

The second thing I can think of is that you do not have a unique identifier for the row, so when you update a row which is similar to another, this error is shown to stop a possible update to the wrong row.

[iPrank]

Well there's two possible issues I can think of, the first is that .Find is doing something to cause it (you could try updating the first record without it to see if that works).

Thanks. I'll try it.

The second thing I can think of is that you do not have a unique identifier for the row, so when you update a row which is similar to another, this error is shown to stop a possible update to the wrong row.

Sorry I don't understand it. I have primary keys in the db (autonumber).

Here is my query:

With rs
        .ActiveConnection = cnn
        .LockType = adLockOptimistic
        .Open "SELECT * FROM tblNotes ORDER BY IsNote DESC,[TreeIndex]"
    End With

And here are the fields:

[si_the_geek]

In that case you are ok for that - primary keys are (by definition) unique identifiers.

[iPrank]

Omitting Find did the trick. Thanks !

( I'm still confused about why Find was causing the problem. A bug in ADO maybe ?)

Friend Sub ParseTree(objNode As Node)
 
    ' Recurstmpively updates TreeIndex field in DB according to the node's level
    Dim rsTmp As ADODB.Recordset
    Set rsTmp = New ADODB.Recordset
    
    '----------------------------------------
    ' Update DB
    With rsTmp
        .ActiveConnection = cnn
        .LockType = adLockOptimistic
        .Open "SELECT * FROM tblNotes WHERE ID = " _
           & Right$(objNode.Key, Len(objNode.Key) - 2) & _
           " ORDER BY IsNote DESC,[TreeIndex]"
    End With
 
    If rsTmp.RecordCount = 1 Then
        rsTmp.MoveFirst
        cnn.BeginTrans
        ' The Treeview Keys are  "_N21" or similar
        rsTmp!TreeIndex = ViewIndex 'ViewIndex is a global var to track current node
        '                         placement in the treeview
        rsTmp.Update
        cnn.CommitTrans
        rsTmp.Close
    ElseIf rsTmp.RecordCount > 1 Then
        MsgBox "Database Error ! More than one primary keys !!!!", vbCritical
        Exit Sub
    Else
        MsgBox "Database Error ! Can't find the node in datbase primary keys !", vbCritical
        Exit Sub
    End If
 
    '----------------------------------------
    
    ViewIndex = ViewIndex + 1
    
    ' Check to see if the current node has children
 
    If objNode.Children > 0 Then
        ' Pass the firstmpt child node to the print routine
        ParseTree objNode.Child
    End If
 
    ' Set the next node to print
    Set objNode = objNode.Next
    Set rsTmp = Nothing
 
    ' As long as we have not reached the last node in
    ' a branch, continue to call the print routine
    If TypeName(objNode) <> "Nothing" Then
        ParseTree objNode
    End If
 
End Sub

[si_the_geek]

Good stuff Could you mark the thread as resolved please? (on the Thread Tools menu above)

My guess is that the standard behaviour of Find invalidates the recordset, as it filters it (and as such effectively creates a new recordset).

[iPrank]

Good stuff Could you mark the thread as resolved please? (on the Thread Tools menu above)

Sorry. I was busy testing the code.

[dee-u]

Have you tried interchanging the positions of your code like the ff...?

rs.Find "ID = " & Right$(objNode.Key, Len(objNode.Key) - 2)
cnn.BeginTrans
rs!TreeIndex = ViewIndex