Update problem

When I try to update record, I kept getting this error:

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

No other users are accessing the database - only 1 user is accessing the
database.

I open the data using adLockOptimistic locking.

I am converting the app. from Access/MSSQL databases to MySQL and the same
code works for both accept MySQL.

Any help greatly appreciated.

Tks
John

Re: Update problem

More information on this problem.

This only happens on 3 records out of 30. I've tried repairing the database
and still same problem.

Tks
John


"zMisc" wrote in message
news:11RFf.245281$V7.205467@news-server.bigpond.net.au...
> When I try to update record, I kept getting this error:
>
> Row cannot be located for updating. Some values may have been changed
> since it was last read.
>
> No other users are accessing the database - only 1 user is accessing the
> database.
>
> I open the data using adLockOptimistic locking.
>
> I am converting the app. from Access/MSSQL databases to MySQL and the same
> code works for both accept MySQL.
>
> Any help greatly appreciated.
>
> Tks
> John
>
>

Re: Update problem

"zMisc" wrote in message
news:11RFf.245281$V7.205467@news-server.bigpond.net.au...
> When I try to update record, I kept getting this error:
>
> Row cannot be located for updating. Some values may have been changed
> since it was last read.

Here's a topic in the MySQL docs that addresses this issue:
http://dev.mysql.com/doc/refman/5.0/en/write-conflict.html

You need to enable the "Return Matching Rows" option in MyODBC. This is
required to use MS Access. I can't tell you what that is for.

The solution is also mentioned on this page about making MySQL & MyODBC work
with MS Access:
http://dev.mysql.com/doc/refman/5.0/en/programs-known-to-wor k-with-myodbc.html

Here's another reference, more complete with screen shots of the proper way
to set up the MyODBC DSN for MS Access:
http://sparxsystems.com.au/EAUserGuide/index.html?setupmysql odbcdriver.htm

Regards,
Bill K.

Re: Update problem

Hi Bill,

Once again thanks for your reply.

I've tried below and none works - I am using MyODBC to access MySQL not MS
Access. The error is happening on a MySQL table.

I tried change OPTION=2 as suggested in
http://dev.mysql.com/doc/refman/5.0/en/write-conflict.html and still get the
same error.

Do you know where I can find the documentation for all the options for
connection string?

Tks
John


"Bill Karwin" wrote in message
news:ds8qun1135h@enews2.newsguy.com...
> "zMisc" wrote in message
> news:11RFf.245281$V7.205467@news-server.bigpond.net.au...
>> When I try to update record, I kept getting this error:
>>
>> Row cannot be located for updating. Some values may have been changed
>> since it was last read.
>
> Here's a topic in the MySQL docs that addresses this issue:
> http://dev.mysql.com/doc/refman/5.0/en/write-conflict.html
>
> You need to enable the "Return Matching Rows" option in MyODBC. This is
> required to use MS Access. I can't tell you what that is for.
>
> The solution is also mentioned on this page about making MySQL & MyODBC
> work with MS Access:
> http://dev.mysql.com/doc/refman/5.0/en/programs-known-to-wor k-with-myodbc.html
>
> Here's another reference, more complete with screen shots of the proper
> way to set up the MyODBC DSN for MS Access:
> http://sparxsystems.com.au/EAUserGuide/index.html?setupmysql odbcdriver.htm
>
> Regards,
> Bill K.
>

Re: Update problem

"zMisc" wrote in message
news:r%9Gf.161$yK1.159@news-server.bigpond.net.au...
> I've tried below and none works - I am using MyODBC to access MySQL not MS
> Access. The error is happening on a MySQL table.

Woops! You mentioned MS Access, so I assumed you are still using Access as
a front-end to MySQL.

> I tried change OPTION=2 as suggested in
> http://dev.mysql.com/doc/refman/5.0/en/write-conflict.html and still get
> the same error.

Here's a thread that suggests that using floating-point or datetime columns
is your primary key can make it unreliable for the ADO layer to locate the
record by its primary key.
http://www.dbforums.com/archive/index.php/t-333957.html

By the way, can you confirm that the query you are trying to update from is
updateable?
In other words, are you including the primary key field in the query, and
are not doing any aggregates (GROUP BY), or other types of queries that
would make it non-updateable?

> Do you know where I can find the documentation for all the options for
> connection string?

This page looks like it has a good reference for MyODBC connection options:
http://dev.mysql.com/doc/refman/5.0/en/connection-parameters .html

Regards,
Bill K.

Re: Update problem

Hi Bill,

The primary key is a Long integer.

There's no GROUP BY. It is definitely updateable as this happens on 2 out of
3 records only (of the same record) - this is the strange part.

The exact same codes works with MS Access and MS SQL databases.

Rgds
Young


"Bill Karwin" wrote in message
news:dsbjnn0301m@enews4.newsguy.com...
> "zMisc" wrote in message
> news:r%9Gf.161$yK1.159@news-server.bigpond.net.au...
>> I've tried below and none works - I am using MyODBC to access MySQL not
>> MS Access. The error is happening on a MySQL table.
>
> Woops! You mentioned MS Access, so I assumed you are still using Access
> as a front-end to MySQL.
>
>> I tried change OPTION=2 as suggested in
>> http://dev.mysql.com/doc/refman/5.0/en/write-conflict.html and still get
>> the same error.
>
> Here's a thread that suggests that using floating-point or datetime
> columns is your primary key can make it unreliable for the ADO layer to
> locate the record by its primary key.
> http://www.dbforums.com/archive/index.php/t-333957.html
>
> By the way, can you confirm that the query you are trying to update from
> is updateable?
> In other words, are you including the primary key field in the query, and
> are not doing any aggregates (GROUP BY), or other types of queries that
> would make it non-updateable?
>
>> Do you know where I can find the documentation for all the options for
>> connection string?
>
> This page looks like it has a good reference for MyODBC connection
> options:
> http://dev.mysql.com/doc/refman/5.0/en/connection-parameters .html
>
> Regards,
> Bill K.
>

Re: Update problem

Young,
I have exactly the same problem with "row cannot be located..." I've trid all
the fixex suggested in replies to your post but, like you, have not got the
problem to go away.

Have you been able to find a fix or work around?

Thanks in advance,

Giles


zMisc wrote:
>Hi Bill,
>
>The primary key is a Long integer.
>
>There's no GROUP BY. It is definitely updateable as this happens on 2 out of
>3 records only (of the same record) - this is the strange part.
>
>The exact same codes works with MS Access and MS SQL databases.
>
>Rgds
>Young
>
>>> I've tried below and none works - I am using MyODBC to access MySQL not
>>> MS Access. The error is happening on a MySQL table.
>[quoted text clipped - 26 lines]
>> Regards,
>> Bill K.

Re: Update problem

Giles,

I've not been able to fix it.

Please see http://bugs.mysql.com/bug.php?id=17213 (I've reported this to
MySQL). Have not tried the possible fix on rounding the floating number to 2
decimal places yet.

Are you updating a floating point number (mine is)?

Rgds
Young


"gpatters888" wrote in message news:5be53ac1ffece@uwe...
> Young,
> I have exactly the same problem with "row cannot be located..." I've trid
> all
> the fixex suggested in replies to your post but, like you, have not got
> the
> problem to go away.
>
> Have you been able to find a fix or work around?
>
> Thanks in advance,
>
> Giles
>
>
> zMisc wrote:
>>Hi Bill,
>>
>>The primary key is a Long integer.
>>
>>There's no GROUP BY. It is definitely updateable as this happens on 2 out
>>of
>>3 records only (of the same record) - this is the strange part.
>>
>>The exact same codes works with MS Access and MS SQL databases.
>>
>>Rgds
>>Young
>>
>>>> I've tried below and none works - I am using MyODBC to access MySQL not
>>>> MS Access. The error is happening on a MySQL table.
>>[quoted text clipped - 26 lines]
>>> Regards,
>>> Bill K.

Re: Update problem

Hi Young,
My problem occurs when I attempt the update after changing a datetime field
that was previously contained a null value. (Everything works if the previous
value is not null...)

However I've managed a work around....

The work around involves using objconn.Excecute with an SQL UPDATE string....
This works well for most cases however I do not know how to correctly deal
with " characters occurin in the middl of long text values (e.g. in the
middle of a memo field) so I ended up using both methods. When the first
method throws an error then the error processing uses the second method to
handle the exception before returning control back.

Effectively...
Dim and set the ADODB connection object objconn
Dim and set an ADODB recordset rst

On err goto Err_Handler
Set up the SQL update string
For each record that has changed...
For each fld that needs updating...
objconn.Execute strSQL
Continue:
Next fld
Next record
Exit:
Close and nothing everything
Exit Sub
Err_Handler:
If err is SQL syntax error then
rst(fldname).Value = new value
rst.Update
Resume at Continue
Else
Report error
Resume Exit
End if
End Sub


I've attached a code sample below....Please note the the SQL method seemed to
work well so I used that as the main method and only use the ADO update
method when an SQL syntax error is thrown (due to those " characters...).
However no reason it would not work the other way around...

A couple of points to note:
1. In this case the connection object is public and is created/set elsewhere.
2. The software deals one at a time with each field in each record that needs
to be updated. This makes it easy to switch to the other method although it's
probably not the best for performance.

Hope all this helps your situation....


Giles


Function UpdateContact(strAction As String, rsSugar As ADODB.Recordset,
objContact As Object, strID As String)
On Error GoTo Err_UpdateContact

Dim db As DAO.Database
Dim rsFields As DAO.Recordset ' rsFields contains the names of the
fields for synching
Dim qdf As DAO.QueryDef

Dim i As Boolean

Dim strTextS, strItemS As String
Dim strTextO, strItemO As String
Dim lngIndexO As Long
Dim old As String
Dim strSQLUpdate As String

Dim objItems As Outlook.ItemProperties
Dim objItem As Outlook.ItemProperty

Set db = CurrentDb
Set qdf = db.QueryDefs("qryFieldsForContacts")
Set rsFields = qdf.OpenRecordset

UpdateContact = False ' Assume it did not update...

Select Case strAction
Case "UpdateSugar"
rsFields.MoveFirst
Do Until rsFields.EOF
strItemS = rsFields!SugarFieldName
If strItemS <> "name" Then
strItemO = rsFields!OutlookFieldIndex
lngIndexO = Val(strItemO)
Set objItems = objContact.ItemProperties
Set objItem = objItems.Item(lngIndexO)
strTextO = objItem.Value
If strTextO <> "" And Not IsNull(strTextO) Then
If rsSugar(strItemS) <> strTextO Then
old = """" ' sets up the SQL text delimiter
character...
strSQLUpdate = "UPDATE contacts SET contacts." &
strItemS
strSQLUpdate = strSQLUpdate & " = " & old &
strTextO & old & " WHERE (("
strSQLUpdate = strSQLUpdate & "Contacts.id)=" &
old & strID & old & ")"

pubconnCRM.Execute strSQLUpdate, ,
adExecuteNoRecords
Next_UpdateContact:
End If
End If
End If
rsFields.MoveNext
Loop
UpdateContact = True
End Select

Exit_UpdateContact:
qdf.Close
rsFields.Close
db.Close
Set qdf = Nothing
Set rsFields = Nothing
Set db = Nothing
Exit Function

Err_UpdateContact:
If Err.Number = -2147217900 Then
' This is an SQL syntax error, most likely caused by a " character in
a field
' Try to correct using the ADO update method...

rsSugar(strItemS) = strTextO
rsSugar.Update
Resume Next_UpdateContact
Else
MsgBox Err.Description
UpdateContact = False
Resume Exit_UpdateContact
End If
End Function

zMisc wrote:
>Giles,
>
>I've not been able to fix it.
>
>Please see http://bugs.mysql.com/bug.php?id=17213 (I've reported this to
>MySQL). Have not tried the possible fix on rounding the floating number to 2
>decimal places yet.
>
>Are you updating a floating point number (mine is)?
>
>Rgds
>Young
>
>> Young,
>> I have exactly the same problem with "row cannot be located..." I've trid
>[quoted text clipped - 27 lines]
>>>> Regards,
>>>> Bill K.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/mysql-general/20060 2/1

Re: Update problem

Hi Giles,

Tks for the tip.

I will try it on mine.

Rgds
Young

"gpatters888 via DBMonster.com" wrote in message
news:5bf55a6e5202c@uwe...
> Hi Young,
> My problem occurs when I attempt the update after changing a datetime
> field
> that was previously contained a null value. (Everything works if the
> previous
> value is not null...)
>
> However I've managed a work around....
>
> The work around involves using objconn.Excecute with an SQL UPDATE
> string....
> This works well for most cases however I do not know how to correctly deal
> with " characters occurin in the middl of long text values (e.g. in the
> middle of a memo field) so I ended up using both methods. When the first
> method throws an error then the error processing uses the second method to
> handle the exception before returning control back.
>
> Effectively...
> Dim and set the ADODB connection object objconn
> Dim and set an ADODB recordset rst
>
> On err goto Err_Handler
> Set up the SQL update string
> For each record that has changed...
> For each fld that needs updating...
> objconn.Execute strSQL
> Continue:
> Next fld
> Next record
> Exit:
> Close and nothing everything
> Exit Sub
> Err_Handler:
> If err is SQL syntax error then
> rst(fldname).Value = new value
> rst.Update
> Resume at Continue
> Else
> Report error
> Resume Exit
> End if
> End Sub
>
>
> I've attached a code sample below....Please note the the SQL method seemed
> to
> work well so I used that as the main method and only use the ADO update
> method when an SQL syntax error is thrown (due to those " characters...).
> However no reason it would not work the other way around...
>
> A couple of points to note:
> 1. In this case the connection object is public and is created/set
> elsewhere.
> 2. The software deals one at a time with each field in each record that
> needs
> to be updated. This makes it easy to switch to the other method although
> it's
> probably not the best for performance.
>
> Hope all this helps your situation....
>
>
> Giles
>
>
> Function UpdateContact(strAction As String, rsSugar As ADODB.Recordset,
> objContact As Object, strID As String)
> On Error GoTo Err_UpdateContact
>
> Dim db As DAO.Database
> Dim rsFields As DAO.Recordset ' rsFields contains the names of the
> fields for synching
> Dim qdf As DAO.QueryDef
>
> Dim i As Boolean
>
> Dim strTextS, strItemS As String
> Dim strTextO, strItemO As String
> Dim lngIndexO As Long
> Dim old As String
> Dim strSQLUpdate As String
>
> Dim objItems As Outlook.ItemProperties
> Dim objItem As Outlook.ItemProperty
>
> Set db = CurrentDb
> Set qdf = db.QueryDefs("qryFieldsForContacts")
> Set rsFields = qdf.OpenRecordset
>
> UpdateContact = False ' Assume it did not update...
>
> Select Case strAction
> Case "UpdateSugar"
> rsFields.MoveFirst
> Do Until rsFields.EOF
> strItemS = rsFields!SugarFieldName
> If strItemS <> "name" Then
> strItemO = rsFields!OutlookFieldIndex
> lngIndexO = Val(strItemO)
> Set objItems = objContact.ItemProperties
> Set objItem = objItems.Item(lngIndexO)
> strTextO = objItem.Value
> If strTextO <> "" And Not IsNull(strTextO) Then
> If rsSugar(strItemS) <> strTextO Then
> old = """" ' sets up the SQL text delimiter
> character...
> strSQLUpdate = "UPDATE contacts SET contacts."
> &
> strItemS
> strSQLUpdate = strSQLUpdate & " = " & old &
> strTextO & old & " WHERE (("
> strSQLUpdate = strSQLUpdate & "Contacts.id)=" &
> old & strID & old & ")"
>
> pubconnCRM.Execute strSQLUpdate, ,
> adExecuteNoRecords
> Next_UpdateContact:
> End If
> End If
> End If
> rsFields.MoveNext
> Loop
> UpdateContact = True
> End Select
>
> Exit_UpdateContact:
> qdf.Close
> rsFields.Close
> db.Close
> Set qdf = Nothing
> Set rsFields = Nothing
> Set db = Nothing
> Exit Function
>
> Err_UpdateContact:
> If Err.Number = -2147217900 Then
> ' This is an SQL syntax error, most likely caused by a " character
> in
> a field
> ' Try to correct using the ADO update method...
>
> rsSugar(strItemS) = strTextO
> rsSugar.Update
> Resume Next_UpdateContact
> Else
> MsgBox Err.Description
> UpdateContact = False
> Resume Exit_UpdateContact
> End If
> End Function
>
> zMisc wrote:
>>Giles,
>>
>>I've not been able to fix it.
>>
>>Please see http://bugs.mysql.com/bug.php?id=17213 (I've reported this to
>>MySQL). Have not tried the possible fix on rounding the floating number to
>>2
>>decimal places yet.
>>
>>Are you updating a floating point number (mine is)?
>>
>>Rgds
>>Young
>>
>>> Young,
>>> I have exactly the same problem with "row cannot be located..." I've
>>> trid
>>[quoted text clipped - 27 lines]
>>>>> Regards,
>>>>> Bill K.
>
> --
> Message posted via DBMonster.com
> http://www.dbmonster.com/Uwe/Forums.aspx/mysql-general/20060 2/1