ADO Error on .update VB6/MySQL
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.
Quote:
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:
Code:
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
Re: ADO Error on .update VB6/MySQL
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)
Quote:
Originally Posted by Wahnsinnig
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.
Quote:
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.
Re: ADO Error on .update VB6/MySQL
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.
Code:
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...
Re: ADO Error on .update VB6/MySQL
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.
Re: ADO Error on .update VB6/MySQL
That makes perfect sense, I would expect leaving that field out to cause the kind of error you had.
