VB6 + MySql: Row cannot be located for updating

[mastertorr]

I posted the following under mysql, but was directed here, hopefully someone can assist me

When trying to update a table i get the following error on the .update command:
Row cannot be located for updating. Some values may have been changed since it was last read.


I've tried changing the option in the connection string to option=2;....and it didn't work. I also tried checking "row update" checkbox under the connection it self (under datasources) as others have suggested, but no luck there either.

What are some things i can do/check to resolve this issue??

Thanks

 

[huggyboy]

You have probably solved this yourself by now - but i have just hit this problem and it seems that when the .update method runs there must have been a change in the value of at least one of the fields in the recordset row - just moving in the same value doesnt work!

dont know whether this is a vb6 or odbc bug - see the mysql forum below for a discussion on it

http://forums.mysql.com/read.php?37,63471,65182#msg-65182

I only said I was smart - I never said I was wise

 

[mastertorr]

Im not sure if you found a solution yourself, but i managed to get around this by: dim a boolean.
check to see if the value in the table is different from the new value (say on your form), if they are different, set the boolean to true.

at the bottom of my code, where its quite lengthy, if the boolean is true, then perform the .update, else cancel the connection with .cancel.

The reason for the error is that if the value hasn't changed, then mysql doesnt want to perform the update. It appears to be a bug, but thats my quick work around.

Example:
rs.open "Select....."

If rs!new_value_on_form <> rs!current_value_in_table or isnull(rs!current_value_in_table) then
rs!value_on_form = rs!value_in_table
boolean = true
End if

If boolean = true then
rs.update
rs.close
else
rs.cancel
End if


hope this helps

 

[mastertorr]

Mistake should be:
If rs!new_value_on_form <> rs!current_value_in_table or isnull(rs!current_value_in_table) then
rs!value_in_table=rs!value_on_form
boolean = true
End if

NOT:
If rs!new_value_on_form <> rs!current_value_in_table or isnull(rs!current_value_in_table) then
rs!value_on_form = rs!value_in_table
boolean = true
End if

Sorry

 

[mastertorr]

Mistake should be:
If rs!new_value_on_form <> rs!current_value_in_table or isnull(rs!current_value_in_table) then
rs!current_value_in_table=rs!value_on_form
boolean = true
End if

NOT:
If rs!new_value_on_form <> rs!current_value_in_table or isnull(rs!current_value_in_table) then
rs!value_on_form = rs!current_value_in_table
boolean = true
End if

Sorry

 

[SBerthold]

Does the table have a unique Key field? If not, then create one, update the field values for the new unique column, and then try it.