ADO Error on .update VB6/MySQL

[Wahnsinnig]

Hi everyone,

i am seeking advise on a problem, that has been discussed here a couple of times, still i couldnt find an answer to my problem. I found a couple of solutions but they didnt work in my case.

Maybe someone can point out what is going wrong. I am working on an old company application moving it from oracle with rdo to mysql with ado 2.8.
ODBC 3.51 Connector is used since the 5.0 crashes my Visual Basic gui instantly on .open "select....".

Selects and executes work great and i seem to have no issues here. But i get an odd error on the .updates.

Run-time Error '-2147217864'
Row cannot be located for updating, some values may have been changed since it was last read.

The Application is used by arround 10 Users here simultaniously, records that are "in use" are marked in a seperate table, to avoid simultanious overwrites.

A typical update in the application looks like this:

    
    Dim adoTMP As ADODB.Recordset

    adoTMP.CursorType = adOpenDynamic
    adoTMP.CursorLocation = adUseClient
    adoTMP.LockType = adLockOptimistic

    adoTMP.ActiveConnection = dbs

    adoTMP.Open "SELECT NamenID, woerter, woerterp FROM NAMEN WHERE NamenID = " & NamenID

    With adoTMP
        .Fields("woerter").Value = wörter
        .Fields("woerterP").Value = wörterP
        .Update
        .Close
    End With

The table had open connections from other recordsets but they are all closed at the time i update these fields. The "fun" thing is, all the data gets saved perfectly fine. I tried updates for the whole table several times doing ~3000 .updates all bringing the error and still they where saved fined.

All Selects that do not need update are "readonly" recordsets.

I could use .execute for the update, but would like to find a solution for this awell. I hate having unsolved problems

Another slight issues i have is err. "3219" not allowing me to close a recordset, eventhough nothing has been changed...but i havent had time to go through the search results of the forums, so i may have a question about that later

Maybe someone can help me to find a solution for this.

thx alot

[si_the_geek]

One likely cause is that you are setting .CursorLocation to adUseClient. Is there a particular reason for doing that? (it should generally be avoided, as it is slower, and increases issues like this because the database system is not in control of the records)

I could use .execute for the update, but would like to find a solution for this awell. I hate having unsolved problems

I understand about unsolved problems, but for the work you are doing there is not a valid technical reason for using a recordset.

Another slight issues i have is err. "3219" not allowing me to close a recordset, eventhough nothing has been changed...but i havent had time to go through the search results of the forums, so i may have a question about that later

3219?

I'm fairly sure that one is not "My trousers are full of spoons", but I'm not sure which of the millions of possible errors it is - but it might be covered by the FAQ article that the "fixing common DB related errors" link in my signature points to.

[Wahnsinnig]

Hi,

thank you so much for your answer.

I wanted to use .adUseServer but i had several problems, the application i am redoing here has a multiplechoice test which uses .bookmark and .recordcount quiet often and it would be alot of work to redo all that code completly. I could get around the .recordcounts by using "Select Count (*)" but still i would have problems with the .bookmark, i couldnt get them working on .adUseServer.

That`s why i was using .adUseClient. In this example a .bookmark is used to move to a certain position in the table to update a couple of rows that are on one page in the mutliplechoice test. Only using executes here would cause alot of work on the whole structure of that mutiplechoice test system.

I even created a temporary table and opened it with the adlockpessimistic, only i knew the tmptable name and only that recordset was using the table and still i get the damn -2147217864 Row cannot be located for updating, some values may have been changed since it was last read.

Dim adoTMP As ADODB.Recordset

    adoTMP.CursorType = adOpenDynamic
    adoTMP.CursorLocation = adUseClient
    adoTMP.LockType = adLockPessimistic

    adoTMP.ActiveConnection = dbs

    adoTMP.open "Select " & FB_Fields & " FROM tmp_be"

   ....

    adoTMP.Bookmark = bkm(curNr)

   dbs.begintrans
    For Fid = 1 To DSPos2 - DSPos1
        adoTMP.Fields("Txt").Value = TextH(Fid).Text
        adoTMP.Fields("FNote").Value = Val(TxtNote(Fid).Text)
        adoTMP.Fields("Hken").Value = Checkbox(Fid).Value
        'adoTMP.Update not really needed
        adoTMP.MoveNext
    Next Fid
  dbs.comittrans

   adoTMP.close

It was a really bad idea how it was done be the original author who developed that tool here a couple of years ago...and i have to suffer now.

Thx again for your input
oli

Edit: thx for your database tutorials i will start going through them...

[Wahnsinnig]

Hi,

so i worked on my problem abit more...it seems that i found a quiet simple solution.

I tried a .resync and an error occured telling me that there are no valid primarykeys for the resync.

Structur of the table is like this:
PrimKey, UserID, QuestionNr, Answerfield, Number, Checkbox

I selected for my Recordset: QuestionNr, Answerfield, Number, Checkbox where UserID = XY to get the whole stuff i need.

If i add the PrimKey to the Recordset it works.

Maybe that was the whole problem.

[si_the_geek]

That makes perfect sense, I would expect leaving that field out to cause the kind of error you had.