By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,702 Members | 1,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,702 IT Pros & Developers. It's quick & easy.

Update problem

P: n/a
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
Feb 6 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <yo********@hotmail.com> wrote in message
news:11********************@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

Feb 7 '06 #2

P: n/a
"zMisc" <yo********@hotmail.com> wrote in message
news:11********************@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/...-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/...th-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/EAUserGui...odbcdriver.htm

Regards,
Bill K.
Feb 7 '06 #3

P: n/a
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/...-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" <bi**@karwin.com> wrote in message
news:ds*********@enews2.newsguy.com...
"zMisc" <yo********@hotmail.com> wrote in message
news:11********************@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/...-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/...th-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/EAUserGui...odbcdriver.htm

Regards,
Bill K.

Feb 7 '06 #4

P: n/a
"zMisc" <yo********@hotmail.com> wrote in message
news:r%***************@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/...-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/...arameters.html

Regards,
Bill K.
Feb 8 '06 #5

P: n/a
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" <bi**@karwin.com> wrote in message
news:ds*********@enews4.newsguy.com...
"zMisc" <yo********@hotmail.com> wrote in message
news:r%***************@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/...-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/...arameters.html

Regards,
Bill K.

Feb 8 '06 #6

P: n/a
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.

Feb 15 '06 #7

P: n/a
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" <u18732@uwe> 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.

Feb 15 '06 #8

P: n/a
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....neral/200602/1
Feb 16 '06 #9

P: n/a
Hi Giles,

Tks for the tip.

I will try it on mine.

Rgds
Young

"gpatters888 via DBMonster.com" <u18732@uwe> 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....neral/200602/1

Feb 16 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.