Error Row cannot be located for updating. Some values may have been changed since it was last read
fniles
users use the application.
Sometimes at the .Update method I get the error -2147217864 (Row cannot be
located for updating. Some values may have been changed since it was last
read).
The table it tries to update has an Identity column. I am using LockType
adLockOptimistic and cursorType adOpenKeyset.
What causes the error and how can I fix it ?
THank you very much.
Bob Barrows [MVP]
This is usually due to a lack of a primary key on the table. If your table
does have a primary key, the problem can also be caused by not including the
primary key field(s) in the select clause of the query used to open the
recordset.
This sample chapter from David Sceppa's "Programming ADO" book will help you
understand why the error can occur:
http://web.archive.org/web/20021222065228/http://www.microsoft.com/mspress/books/sampchap/3445.asp
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
fiefie...@gmail.com
The table does have a primary key called [Order] a varchar(50), but
the identity column is another column called [ID], and [ID] is not the
primary key.
The sql statement include all columns:
select * from myTable where [order] = '" & (sOrder) & "'
What chapter and section from David Sceppa's "Programming ADO" book
that talks about it ?
Do you think I should either:
1. call up a stored Procedure to update the table
or
2. trap the error and loop while error until say 5 times and try to
update like the following:
on error goto myErr
.Update
myErr:
NR = 0
Do While err.number = -2147217864 And NR < 5
NR = NR + 1
Sleep &O1140
.Update
Loop
?
THanks again.
On Apr 30, 9:52 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> fniles wrote:
> > I am using VB6 connecting to a SQL Server 2005 database using ADO.
> > Multiple users use the application.
> > Sometimes at the .Update method I get the error -2147217864 (Row
> > cannot be located for updating. Some values may have been changed
> > since it was last read).
> > The table it tries to update has an Identity column. I am using
> > LockType adLockOptimistic and cursorType adOpenKeyset.
> > What causes the error and how can I fix it ?
> > THank you very much.
>
> This is usually due to a lack of a primary key on the table. If your table
> does have a primary key, the problem can also be caused by not including the
> primary key field(s) in the select clause of the query used to open the
> recordset.
> This sample chapter from David Sceppa's "Programming ADO" book will help you
> understand why the error can occur:http://web.archive.org/web/20021222065228/http://www.microsoft.com/ms...
Bob Barrows [MVP]
> THank you for your reply.
> The table does have a primary key called [Order] a varchar(50), but
> the identity column is another column called [ID], and [ID] is not the
> primary key.
Are you allowing the value of Order to be updated? If so, it's not
really a good candidate for the table's primary key ...
> The sql statement include all columns:
> select * from myTable where [order] = '" & (sOrder) & "'
Selstar is not a good programming practice. You should make a point of
explicitly naming the columns in your select clause so that
1) you only retrieve the data you need, and
2) you don't force ADO to generate metadata, which sometimes requires an
extra trip to the database
>
> What chapter and section from David Sceppa's "Programming ADO" book
> that talks about it ?
???
The chapter I provided a link to ... particularly the first chapter
(chapter 11, I think)
Here is the link again. It should appear on a single line. If your
newsreader is breaking the line, you will need to reassemble the link:
http://web.archive.org/web/20021222065228/http://www.microsoft.com/mspress/books/sampchap/3445.asp
>
> Do you think I should either:
> 1. call up a stored Procedure to update the table
That would be my preference, except that you must have had some reason
to choose an expensive keyset cursor ... are you keeping the recordset
open long enough to cause concerns about concurrency? Recordsets, while
inefficient for updating data, do provide good functionality for
handling concurrency issues. These issues are ones you would need to
handle yourself if you resorted to a stored procedure. That chapter I
linked to also covers this topic.
> or
> 2. trap the error and loop while error until say 5 times and try to
> update like the following:
Have you tried this to verify that it would work?
I think you should run SQL Profiler to get a trace showing the sql
statement that generated the error.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
fniles
I have changed the query to explicitly naming the columns in the select
clause.
The "Programming ADO" book talks about clientside cursor, I use adUseClient
cursorLocation for the connection, and I did not set/change the cursor
location for the recordset. Shall I use server side cursor on the connection
?
Please let me know if I understand it correctly.
The error that I got is causeed because in between executing the query
"select [Order],Status,Price,Location from myTable where [order] = '" &
(sOrder) & "' and calling the .Update method somebody else already update
either Status,Price,or Location column. Do I understand it correctly ?
If I understand it correctly, does it mean that I can trap this error and
then resubmit the same query and then do update again ?
Say the previous value are the following: Status = 0, Price = 2000, Location
= 'TX'
Is it correct that on the query above, the ADO Cursor Engine builds a query
like
Update myTable SET Status = 1, Price = 2500, Location = 'IL' where [Order] =
'D100-12345' and Status = 0 and Price = 2000 and Location = 'TX' ?
>> 1. call up a stored Procedure to update the table
> That would be my preference, except that you must have had some reason to
> choose an expensive keyset cursor ... are you keeping the recordset open
> long enough to cause concerns about >concurrency? Recordsets, while
> inefficient for updating data, do provide good functionality for handling
> concurrency issues. These issues are ones you would need to
> handle yourself if you resorted to a stored procedure. That chapter I
> linked to also covers this topic.
I am not clear how I should handle concurrency issues if I call a stored
procedure to do the update. Do you mean I need to check the RecordsAffected
value, and if it is 0 I should resubmit Re-Excute ?
Thanks a lot for your help.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:%23Ajk1Y$iHHA...@TK2MSFTNGP06.phx.gbl...
Bob Barrows [MVP]
> Thank you.
> I have changed the query to explicitly naming the columns in the
> select clause.
>
> The "Programming ADO" book talks about clientside cursor, I use
> adUseClient cursorLocation for the connection, and I did not
> set/change the cursor location for the recordset. Shall I use server
> side cursor on the connection ?
>
Oh, but you were requesting a keyset cursor. With aduseClient, the only
cursor type you will get is static (adOpenStatic)
Your decision as to cursor type depends on your need for functionality.
It is really up to you which type you need since i don't know the
requirements of your application. In general, if you are opening a
cursor, making immediate modifications and closing the cursor, you might
as well use either a server or client-side static cursor, or use SQL DML
(preferably in a stored procedure) to make the changes. on the other
hand if you are keeping a recordset open for any length of time,
requiring you to worry about what other users are doing, then you need a
server side cursor, either keyset or dynamic.
> Please let me know if I understand it correctly.
> The error that I got is causeed because in between executing the query
> "select [Order],Status,Price,Location from myTable where [order] = '"
> & (sOrder) & "' and calling the .Update method somebody else already
> update either Status,Price,or Location column. Do I understand it
> correctly ?
That could be the case
> If I understand it correctly, does it mean that I can trap this error
> and then resubmit the same query and then do update again ?
Read the chapter - Dave Sceppa goes into detail how to handle
concurrency issues
>
> Say the previous value are the following: Status = 0, Price = 2000,
> Location = 'TX'
> Is it correct that on the query above, the ADO Cursor Engine builds a
> query like
> Update myTable SET Status = 1, Price = 2500, Location = 'IL' where
> [Order] = 'D100-12345' and Status = 0 and Price = 2000 and Location =
> 'TX' ?
It could, depending on the settings that Dave talks about. There is a
setting to tell ADO to use the primary key only.
>
>>> 1. call up a stored Procedure to update the table
>> That would be my preference, except that you must have had some
>> reason to choose an expensive keyset cursor ... are you keeping the
>> recordset open long enough to cause concerns about >concurrency?
>> Recordsets, while inefficient for updating data, do provide good
>> functionality for handling concurrency issues. These issues are ones
>> you would need to
>> handle yourself if you resorted to a stored procedure. That chapter I
>> linked to also covers this topic.
> I am not clear how I should handle concurrency issues if I call a
> stored procedure to do the update. Do you mean I need to check the
> RecordsAffected value, and if it is 0 I should resubmit Re-Excute ?
No, I mean you need to keep track of the state of the field values when
they were originally retrieved and before performing the update, compare
them to the current field values to see if they've changed in the
meantime.
fniles
>(adOpenStatic)
>on the other hand if you are keeping a recordset open for any length of
>time,
> requiring you to worry about what other users are doing, then you need a
> server side cursor, either keyset or dynamic.
Are you referring to the connection cursor location ? Did you mean that with
connection cursor location = aduseClient, I can not use cursor type
adOpenKeySet ? I am using adOpenKeySet, why doesn't it give an error if the
only one I will get is adOpenStatic ?
I open the recordset just to change several column values (and nothing
else), then I do the .Update.
If I open the connection using adUseClient, can I just for this recordset
use server side cursor ?
Thank you.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:erk7aODj...@TK2MSFTNGP04.phx.gbl...
Ken Halter
news:uCW5zxDj...@TK2MSFTNGP04.phx.gbl...
>
> Are you referring to the connection cursor location ? Did you mean that
> with connection cursor location = aduseClient, I can not use cursor type
> adOpenKeySet ? I am using adOpenKeySet, why doesn't it give an error if
> the only one I will get is adOpenStatic ?
ADO 2.7 API Reference (which should be installed on your hard-drive...
mine's here
"C:\Program Files\Microsoft Visual Studio\MSDN\2001OCT\1033\ADO270.CHM"
Says:
<quote>
Use the CursorType property to specify the type of cursor that should be
used when opening the Recordset object.
Only a setting of adOpenStatic is supported if the CursorLocation property
is set to adUseClient. If an unsupported value is set, then no error will
result; the closest supported CursorType will be used instead.
If a provider does not support the requested cursor type, it may return
another cursor type. The CursorType property will change to match the actual
cursor type in use when the Recordset object is open.
To verify specific functionality of the returned cursor, use the Supports
method. After you close the Recordset, the CursorType property reverts to
its original setting.
</quote>
> I open the recordset just to change several column values (and nothing
> else), then I do the .Update.
>
> If I open the connection using adUseClient, can I just for this recordset
> use server side cursor ?
>
> Thank you.
--
Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
In Loving Memory - http://www.vbsight.com/Remembrance.htm
Stephen Howe
> with connection cursor location = aduseClient, I can not use cursor type
> adOpenKeySet ? I am using adOpenKeySet, why doesn't it give an error if
> the only one I will get is adOpenStatic ?
Because ADO will coerce CursorType and LockType properties into whatever is
supported for that CursorLocation. It is not an error.
After you do a successful RecordSet Open(), print out CursorType and
LockType. The values might be different from what is specified.
Bob is right. For Client-sided cursors, it will always be adOpenStatic that
is used.
For Server-sided cursors, it depends on Database, Provider and other
factors.
For SQL Server, I have printed out all combinations of what you get back for
all combinations of CursorLocation, CursorType and LockType.
For some of the Server-sided cursors, the Cursor Type depends on whether a
table is indexed.
> If I open the connection using adUseClient, can I just for this recordset
> use server side cursor ?
You can. The Connections CursorLocation property is used to determine the
location of a returned Recordset when executing a Command object.
Stephen Howe
Bob Barrows [MVP]
<snip>
The other guys dealt with your other questions (thanks Ken and Steve), so I
will tackle this one:
> If I open the connection using adUseClient, can I just for this
> recordset use server side cursor ?
Yes.
The connection's CursorLocation property only controls the default cursor
location for recordsets that are opened without specifying the location. A
Recordset object also has a CursorLocation property which can be used to
override the value set in the connection. You have to instantiate the
recordset object, set the CursorLocation (of the recordset), and then use
Open to open it. Using Execute, which always creates a new recordset object,
will result in the default CursorLocation set at the connection level.
Alternatively, you could set the connection's CursorLocation property to
adUseServer before using Execute to retrieve a recordset.
--
Bob Barrows [MVP]
<oops, i missed this:>
> I open the recordset just to change several column values (and nothing
> else), then I do the .Update.
>
Then I would use SQL DML rather than a cursor (in my preference encapsulated
in a stored procedure).
--
fniles
apprecaite it.
Use SQL DML means call the Execute method to call the stored procedure,
right ?
If I use Execute method of the connection to call a stored procedure, as you
mentioned earlier, I need to tackle the concurrency issue.
You mentioned that "you need to keep track of the state of the field values
when they were originally retrieved and before performing the update,
compare
them to the current field values to see if they've changed in the meantime."
So, if I am modifying 5 field values in the table, I need to keep track of
the value of all 5 field, right ?
Since I will only call Execute method (a 1 line code) (instead of open the
recordset, update all 5 fields, and call Update method), thus I do not
retrieve the values. Using Execute method when do you compare the field
values ? If you don't mind, can you post a snipet of codes ?
Thanks again. I appreciate it.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:OLCC8sF...@TK2MSFTNGP05.phx.gbl...
fniles
> After you do a successful RecordSet Open(), print out CursorType and
> LockType. The values might be different from what is specified.
is adOpenStatic and the LockType is adLockOptimistic
"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message
news:uUcnETEj...@TK2MSFTNGP02.phx.gbl...
Bob Barrows [MVP]
> Thank you very much for all your help. You've been very helpful, I
> apprecaite it.
>
> Use SQL DML means call the Execute method to call the stored
> procedure, right ?
>
> If I use Execute method of the connection to call a stored procedure,
> as you mentioned earlier, I need to tackle the concurrency issue.
> You mentioned that "you need to keep track of the state of the field
> values when they were originally retrieved and before performing the
> update, compare
> them to the current field values to see if they've changed in the
> meantime." So, if I am modifying 5 field values in the table, I need
> to keep track of the value of all 5 field, right ?
> Since I will only call Execute method (a 1 line code) (instead of
> open the recordset, update all 5 fields, and call Update method),
> thus I do not retrieve the values. Using Execute method when do you
> compare the field values ? If you don't mind, can you post a snipet
> of codes ?
>
From your description, it did not appear that you were worried about
concurrency. Are you worried about updating changes made by other users
between the time the current user viewed the data and the time you are
sending the current user's changes to the database?
fniles
> between the time the current user viewed the data and the time you are
> sending the current user's changes to the database?
the last user, I guess not. If user A read the screen, then user B update
it, then user A update it, I want user A to see user A's changes.
But, for other application I might want to worry about the concurrency
issue. Thus, my questions below.
1. Does using SQL DML means calling the Execute method to call the stored
procedure ?
2. You mentioned that "you need to keep track of the state of the field
values when they were originally retrieved and before performing the
update, compare them to the current field values to see if they've changed
in the meantime." So, if I am modifying 5 field values in the table, is it
correct that I need
to keep track of the value of all 5 field ?
If I only call Execute method (a 1 line code) (instead of open the
recordset, update all 5 fields, and call Update method), I do not retrieve
the values, right ? When using Execute method, when do you compare the field
values ? If you don't mind, can you post a snipet of codes ?
Thanks a lot.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:uXS6NFNj...@TK2MSFTNGP05.phx.gbl...
Bob Barrows [MVP]
>> Are you worried about updating changes made by other users
>> between the time the current user viewed the data and the time you
>> are sending the current user's changes to the database?
> For this part of the program, since I always want the latest change
> made by the last user, I guess not. If user A read the screen, then
> user B update it, then user A update it, I want user A to see user
> A's changes.
> But, for other application I might want to worry about the concurrency
> issue. Thus, my questions below.
>
> 1. Does using SQL DML means calling the Execute method to call the
> stored procedure ?
Yes, or using my preferred procedure-as-connection-method technique.
See:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en
>
> 2. You mentioned that "you need to keep track of the state of the
> field values when they were originally retrieved and before
> performing the update, compare them to the current field values to
> see if they've changed in the meantime." So, if I am modifying 5
> field values in the table, is it correct that I need
> to keep track of the value of all 5 field ?
Yes.
Alternatively, you could add a timestamp column to the table in the
database. Despite its name, timestamp has nothing to do with time (or
date). You should look it up in BOL, but for now, think of it as a
column whose value changes whenever anything in the current row changes.
To use it, retrieve the value of that column when initially retrieving
data. When sending mods to the stored procedure, send the timestamp
value also. The stored procedure compares the current timestamp column
value with the one passed to it - if they are the same, it performs the
update and retrieves and returns the new timestamp value (probably via
an output parameter). If they are different, ... well, do whatever you
want. Some options are:
a) raise an error
b) perform the update but raise an error
c) retrieve the new row values and return them to the caller so the user
can see them and decide whether to perform his update
> If I only call Execute method (a 1 line code) (instead of open the
> recordset, update all 5 fields, and call Update method), I do not
> retrieve the values, right ?
Right
> When using Execute method, when do you
> compare the field values ?
In the stored procedure.
> If you don't mind, can you post a snipet of codes ?
>
I'm not sure which code you are asking about. The link I posted above
explains how to execute stored procedures. Do you need help with writing
a stored procedure?
fniles
> Alternatively, you could add a timestamp column to the table in the
> database. .. To use it, retrieve the value of that column when initially
> retrieving
> data. When sending mods to the stored procedure, send the timestamp value
> also. The stored procedure compares the current timestamp column
> value with the one passed to it - if they are the same, it performs the
> update and retrieves and returns the new timestamp value (probably via
> an output parameter). If they are different,...
I do something like this, but it gives me the error "Unclosed quotation mark
after the character string ''" in VB6 when I run it.
Dim rstHistTrades As New ADODB.Recordset
Dim rstHistTrades2 As New ADODB.Recordset
Dim sSQL As String
Dim vChange As Variant
sSQL = "select change from histtradesorig where [order] = 'D1003-205365'"
rstHistTrades.Open sSQL, g_adoCon
If Not (rstHistTrades.EOF) Then
vChange = rstHistTrades.Fields("Change")
g_adoCon.Execute "updateHisttradesorig @Price=1600,
@OrderID='D1003-205365', @ts='" & vChange & "'" --> ERROR here
End If
This is the Stored procedure:
create procedure UpdateHisttradesOrig
@Price float,
@OrderID varchar(50),
@ts as varchar(50)
as
declare @CurrentChange timestamp
select @CurrentChange = (select Change from Histtradesorig where [order] =
@OrderID)
if convert(timestamp,@ts) = @CurrentChange
update Histtradesorig set fillprice = @Price where [order] = @OrderID
else
raiserror ('Timestamp is different',16,1)
Do I do it incorrectly ?
Thanks again.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:u0b5TbNj...@TK2MSFTNGP04.phx.gbl...
Bob Barrows [MVP]
> g_adoCon.Execute "updateHisttradesorig @Price=1600,
> @OrderID='D1003-205365', @ts='" & vChange & "'" --> ERROR here
> End If
What is the entire text of the error? I need to know if it's a VB
compiler or runtime error, or an error returned from ADO.
Since you are not using any output parameters, I would have executed it
like this:
adoCon.updateHisttradesorig 1600,"D1003-205365", vChange
However, to debug dynamic sql, you need to look at the result of the
dynamic sql:
dim sql as string
sql="updateHisttradesorig @Price=1600, " & _
"@OrderID='D1003-205365', @ts='" & vChange & "'"
debug.print sql
g_adoCon.Execute sql,,adCmdText
(always tell ADO what the command type is)
After running the code, copy the result from the Immediate window and
verify whether it runs without error in SQL Query Analyzer
fniles
This is from the Immediate window:
updateHisttradesorig @Price=1600,@OrderID='D1003-205365', @ts=' ?'
Then, when it executes the Execute command I get the error:
Run-time error '-2147217900 (80040e14':
Unclosed quotation mark after the character string
In the SQL Server Management Studio (SQL Enterprise Manager in SQL 2000)
when I execute the query
updateHisttradesorig @Price=1600,@OrderID='D1003-205365', @ts=' ?'
it returns the following:
Msg 50000, Level 16, State 1, Procedure UpdateHisttradesOrig, Line 11
Timestamp is different
Is my Stored Procecure correct ? (notice in the SP the parameter @ts = as
varchar(50), then later on I say:
if convert(timestamp,@ts) = @CurrentChange
update Histtradesorig set fillprice = @Price where [order] = @OrderID
else
raiserror ('Timestamp is different',16,1)
From VB6 I would like to already pass @ts as a timestamp datatype, but I
don't know how to pass a timestamp variable from VB6.
Thank you.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:ufFj16Oj...@TK2MSFTNGP02.phx.gbl...
Bob Barrows [MVP]
> Thanks.
> This is from the Immediate window:
> updateHisttradesorig @Price=1600,@OrderID='D1003-205365', @ts=' ?'
>
> Then, when it executes the Execute command I get the error:
> Run-time error '-2147217900 (80040e14':
> Unclosed quotation mark after the character string
>
> In the SQL Server Management Studio (SQL Enterprise Manager in SQL
> 2000) when I execute the query
> updateHisttradesorig @Price=1600,@OrderID='D1003-205365', @ts=' ?'
> it returns the following:
> Msg 50000, Level 16, State 1, Procedure UpdateHisttradesOrig, Line 11
> Timestamp is different
>
Oh! @ts is the timestamp! It needs to be declared with the proper
datatype (timestamp) in the CREATE PROCEDURE statement:
Carl Prothman compiled a very useful datatype cross-reference here:
http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
Looking there, you will see it has to be passed as a Variant. You may
need to use an explicit Command object. This KB article may help:
http://support.microsoft.com/kb/q196590/
fniles
Dim rstHistTrades As ADODB.Recordset
Dim rstHistTrades2 As New ADODB.Recordset
Dim param As New ADODB.Parameter
Dim sSQL As String
Dim Affected As Long
Dim vChange As Variant
sSystem = GetSetting("BestDirect", "Startup Files", "System")
DBInit GetSystem(sSystem)
Set rstHistTrades = New ADODB.Recordset
sSQL = "select change from histtradesorig where [order] = 'D1003-205365'"
rstHistTrades.Open sSQL, g_adoCon
If Not (rstHistTrades.EOF) Then
vChange = rstHistTrades.Fields("Change")
cmd.ActiveConnection = g_adoCon
cmd.CommandType = adCmdText
cmd.CommandText = "execute updateHisttradesorig ?,?,?"
With cmd
.Parameters.Append .CreateParameter("Price", adDouble,
adParamInput, 8, 1601)
.Parameters.Append .CreateParameter("OrderID", adVarChar,
adParamInput, 50, "D1003-205365")
.Parameters.Append .CreateParameter("ts", adVarBinary,
adParamInput, 8, vChange)
End With
On Error GoTo err
Set rstHistTrades2 = cmd.Execute(Affected)
Is it correct that I
1. first query the timestamp from the database (sSQL = "select change from
histtradesorig where [order] = 'D1003-205365'" like above)
2. then submit that to the stored procedure
3. in the stored procedure i do a comparison of that value with the current
timestamp like so:
create procedure UpdateHisttradesOrig
@Price float,
@OrderID varchar(50),
@ts as timestamp
as
declare @CurrentChange timestamp
select @CurrentChange = (select Change from Histtradesorig where [order] =
@OrderID)
if @ts = @CurrentChange ---> COMPARING THE current TimeStamp with the one
coming in the input parameter
update Histtradesorig set fillprice = @Price where [order] = @OrderID
else
raiserror ('Timestamp is different',16,1)
Is there a way to make it work using procedure-as-connection-method
technique ?
Thanks.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:ON7HodPj...@TK2MSFTNGP04.phx.gbl...
Bob Barrows [MVP]
> Is it correct that I
> 1. first query the timestamp from the database (sSQL = "select change
> from histtradesorig where [order] = 'D1003-205365'" like above)
Well, sort of. This should have been done when you first retrieved the
record to display to the user. Doing it now is a little late. You should be
passing that timestamp value to the method that contains this code.
> 2. then submit that to the stored procedure
Yes
> 3. in the stored procedure i do a comparison of that value with the
> current timestamp like so:
Seems right to me
>
> Is there a way to make it work using procedure-as-connection-method
> technique ?
>
I'm not sure, never having tried it. Normally, when using this technique, I
also use output parameters requiring an explicit command object .
Why don't you try and let me know?
fniles
>> from histtradesorig where [order] = 'D1003-205365'" like above)
>
> Well, sort of. This should have been done when you first retrieved the
> record to display to the user. Doing it now is a little late. You should
> be passing that timestamp value to the method that contains this code.
Regarding the procedure-as-connection-method I will try it later, now that
the method that I use is working.
Thanks a lot for your help. You are very helpful. Thanks !
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:OnpLylQj...@TK2MSFTNGP06.phx.gbl...
whitsnell
Any help much appreciated.
Bob Barrows [MVP]
http://www.microsoft.com/dynamics/rms/Default.mspx
If so, we're not going to be able to help - MS knows more about the code
than we can ever know.
If not, could you show a little bit of the code that causes the error?
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
fahimahmad
b4 rs.open write "Rs.CursorLocation=2" it will work
Bob Barrows
> Dear All,
>
> b4 rs.open write "Rs.CursorLocation=2" it will work
What problem is this advice intended to solve? There are situations where I
would recommend against following this advice.
Do you know what setting the CursorLocation to 2 actually does?
The ADO documentation can be found here:
http://msdn2.microsoft.com/en-us/library/ms675532.aspx
danielarnaldo
Than you to the post.
Best Regards
Daniel Arnaldo
Delphi programmer
Nisar
how I was thiking something wrong with programming code
and i was trying to solve it from morning but as i changed
it work
DanielleVBANewbie
I am getting this error on a Infopath form that is attached to a Access
Database and Sharepoint Library. I see that the resolution is below. Can
someone tell me exactly where I change that?
Is it changed in the Infopath Form or in the Access Database? Where exactly
do you go to update it?
-- cursorlocation=clUseServer
Thanks
Danielle :<)
"unknown" wrote:
>
hishamallham
don't use recordset to update your record try to make it coding
Ex: Sql="Update My table
Set Myfiled=NewData
Where Mykey=UpdatedKey"
Db.excute Sql
I hope if it'll help
fniles wrote:
Error Row cannot be located for updating. Some values may have been changed since it was last read
30-Apr-07
I am using VB6 connecting to a SQL Server 2005 database using ADO. Multiple
users use the application.
Sometimes at the .Update method I get the error -2147217864 (Row cannot be
located for updating. Some values may have been changed since it was last
read).
The table it tries to update has an Identity column. I am using LockType
adLockOptimistic and cursorType adOpenKeyset.
What causes the error and how can I fix it ?
THank you very much.
Previous Posts In This Thread:
On Monday, April 30, 2007 10:04 PM
fniles wrote:
Error Row cannot be located for updating. Some values may have been changed since it was last read
I am using VB6 connecting to a SQL Server 2005 database using ADO. Multiple
users use the application.
Sometimes at the .Update method I get the error -2147217864 (Row cannot be
located for updating. Some values may have been changed since it was last
read).
The table it tries to update has an Identity column. I am using LockType
adLockOptimistic and cursorType adOpenKeyset.
What causes the error and how can I fix it ?
THank you very much.
On Monday, April 30, 2007 10:52 PM
Bob Barrows [MVP] wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
fniles wrote:
This is usually due to a lack of a primary key on the table. If your table
does have a primary key, the problem can also be caused by not including the
primary key field(s) in the select clause of the query used to open the
recordset.
This sample chapter from David Sceppa's "Programming ADO" book will help you
understand why the error can occur:
http://web.archive.org/web/20021222065228/http://www.microsoft.com/mspress/books/sampchap/3445.asp
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
On Tuesday, May 01, 2007 3:57 AM
Jan Hyde (VB MVP) wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
"fniles" <fni...@pfmail.com>'s wild thoughts were released
on Mon, 30 Apr 2007 21:04:18 -0500 bearing the following
fruit:
Since your using optimistic locking your *hoping* no-one
else will change the record before you do. Someone probably
has changed the record and since your changing the identity
column (which seems very odd to me) then the error is most
likely correct, the item doesn't exist any more (or rather
it does exist but not with that value in it's identity
column.
On Tuesday, May 01, 2007 7:31 AM
fiefie.nile wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
THank you for your reply.
The table does have a primary key called [Order] a varchar(50), but
the identity column is another column called [ID], and [ID] is not the
primary key.
The sql statement include all columns:
select * from myTable where [order] = '" & (sOrder) & "'
What chapter and section from David Sceppa's "Programming ADO" book
that talks about it ?
Do you think I should either:
1. call up a stored Procedure to update the table
or
2. trap the error and loop while error until say 5 times and try to
update like the following:
on error goto myErr
.Update
myErr:
NR = 0
Do While err.number = -2147217864 And NR < 5
NR = NR + 1
Sleep &O1140
.Update
Loop
?
THanks again.
On Apr 30, 9:52 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
On Tuesday, May 01, 2007 9:43 AM
Bob Barrows [MVP] wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
fiefie...@gmail.com wrote:
Are you allowing the value of Order to be updated? If so, it's not
really a good candidate for the table's primary key ...
Selstar is not a good programming practice. You should make a point of
explicitly naming the columns in your select clause so that
1) you only retrieve the data you need, and
2) you don't force ADO to generate metadata, which sometimes requires an
extra trip to the database
???
The chapter I provided a link to ... particularly the first chapter
(chapter 11, I think)
Here is the link again. It should appear on a single line. If your
newsreader is breaking the line, you will need to reassemble the link:
http://web.archive.org/web/20021222065228/http://www.microsoft.com/mspress/books/sampchap/3445.asp
That would be my preference, except that you must have had some reason
to choose an expensive keyset cursor ... are you keeping the recordset
open long enough to cause concerns about concurrency? Recordsets, while
inefficient for updating data, do provide good functionality for
handling concurrency issues. These issues are ones you would need to
handle yourself if you resorted to a stored procedure. That chapter I
linked to also covers this topic.
Have you tried this to verify that it would work?
I think you should run SQL Profiler to get a trace showing the sql
statement that generated the error.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
On Tuesday, May 01, 2007 2:50 PM
fniles wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
Thank you.
I have changed the query to explicitly naming the columns in the select
clause.
The "Programming ADO" book talks about clientside cursor, I use adUseClient
cursorLocation for the connection, and I did not set/change the cursor
location for the recordset. Shall I use server side cursor on the connection
?
Please let me know if I understand it correctly.
The error that I got is causeed because in between executing the query
"select [Order],Status,Price,Location from myTable where [order] = '" &
(sOrder) & "' and calling the .Update method somebody else already update
either Status,Price,or Location column. Do I understand it correctly ?
If I understand it correctly, does it mean that I can trap this error and
then resubmit the same query and then do update again ?
Say the previous value are the following: Status = 0, Price = 2000, Location
= 'TX'
Is it correct that on the query above, the ADO Cursor Engine builds a query
like
Update myTable SET Status = 1, Price = 2500, Location = 'IL' where [Order] =
'D100-12345' and Status = 0 and Price = 2000 and Location = 'TX' ?
I am not clear how I should handle concurrency issues if I call a stored
procedure to do the update. Do you mean I need to check the RecordsAffected
value, and if it is 0 I should resubmit Re-Excute ?
Thanks a lot for your help.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:%23Ajk1Y$iHHA...@TK2MSFTNGP06.phx.gbl...
On Tuesday, May 01, 2007 5:02 PM
Bob Barrows [MVP] wrote:
LRe: Error Row cannot be located for updating. Some values may have been changed since it was last read
fniles wrote:
Oh, but you were requesting a keyset cursor. With aduseClient, the only
cursor type you will get is static (adOpenStatic)
Your decision as to cursor type depends on your need for functionality.
It is really up to you which type you need since i don't know the
requirements of your application. In general, if you are opening a
cursor, making immediate modifications and closing the cursor, you might
as well use either a server or client-side static cursor, or use SQL DML
(preferably in a stored procedure) to make the changes. on the other
hand if you are keeping a recordset open for any length of time,
requiring you to worry about what other users are doing, then you need a
server side cursor, either keyset or dynamic.
That could be the case
Read the chapter - Dave Sceppa goes into detail how to handle
concurrency issues
It could, depending on the settings that Dave talks about. There is a
setting to tell ADO to use the primary key only.
No, I mean you need to keep track of the state of the field values when
they were originally retrieved and before performing the update, compare
them to the current field values to see if they've changed in the
meantime.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
On Tuesday, May 01, 2007 6:02 PM
fniles wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
Are you referring to the connection cursor location ? Did you mean that with
connection cursor location = aduseClient, I can not use cursor type
adOpenKeySet ? I am using adOpenKeySet, why doesn't it give an error if the
only one I will get is adOpenStatic ?
I open the recordset just to change several column values (and nothing
else), then I do the .Update.
If I open the connection using adUseClient, can I just for this recordset
use server side cursor ?
Thank you.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:erk7aODj...@TK2MSFTNGP04.phx.gbl...
On Tuesday, May 01, 2007 6:18 PM
Ken Halter wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
"fniles" <fni...@pfmail.com> wrote in message
news:uCW5zxDj...@TK2MSFTNGP04.phx.gbl...
ADO 2.7 API Reference (which should be installed on your hard-drive...
mine's here
"C:\Program Files\Microsoft Visual Studio\MSDN\2001OCT\1033\ADO270.CHM"
Says:
<quote>
Use the CursorType property to specify the type of cursor that should be
used when opening the Recordset object.
Only a setting of adOpenStatic is supported if the CursorLocation property
is set to adUseClient. If an unsupported value is set, then no error will
result; the closest supported CursorType will be used instead.
If a provider does not support the requested cursor type, it may return
another cursor type. The CursorType property will change to match the actual
cursor type in use when the Recordset object is open.
To verify specific functionality of the returned cursor, use the Supports
method. After you close the Recordset, the CursorType property reverts to
its original setting.
</quote>
--
Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
In Loving Memory - http://www.vbsight.com/Remembrance.htm
On Tuesday, May 01, 2007 7:02 PM
Stephen Howe wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
Because ADO will coerce CursorType and LockType properties into whatever is
supported for that CursorLocation. It is not an error.
After you do a successful RecordSet Open(), print out CursorType and
LockType. The values might be different from what is specified.
Bob is right. For Client-sided cursors, it will always be adOpenStatic that
is used.
For Server-sided cursors, it depends on Database, Provider and other
factors.
For SQL Server, I have printed out all combinations of what you get back for
all combinations of CursorLocation, CursorType and LockType.
For some of the Server-sided cursors, the Cursor Type depends on whether a
table is indexed.
You can. The Connections CursorLocation property is used to determine the
location of a returned Recordset when executing a Command object.
Stephen Howe
On Tuesday, May 01, 2007 9:40 PM
Bob Barrows [MVP] wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
fniles wrote:
<snip>
The other guys dealt with your other questions (thanks Ken and Steve), so I
will tackle this one:
Yes.
The connection's CursorLocation property only controls the default cursor
location for recordsets that are opened without specifying the location. A
Recordset object also has a CursorLocation property which can be used to
override the value set in the connection. You have to instantiate the
recordset object, set the CursorLocation (of the recordset), and then use
Open to open it. Using Execute, which always creates a new recordset object,
will result in the default CursorLocation set at the connection level.
Alternatively, you could set the connection's CursorLocation property to
adUseServer before using Execute to retrieve a recordset.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
On Tuesday, May 01, 2007 9:43 PM
Bob Barrows [MVP] wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
fniles wrote:
<oops, i missed this:>
Then I would use SQL DML rather than a cursor (in my preference encapsulated
in a stored procedure).
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
On Wednesday, May 02, 2007 10:42 AM
fniles wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
Thank you very much for all your help. You've been very helpful, I
apprecaite it.
Use SQL DML means call the Execute method to call the stored procedure,
right ?
If I use Execute method of the connection to call a stored procedure, as you
mentioned earlier, I need to tackle the concurrency issue.
You mentioned that "you need to keep track of the state of the field values
when they were originally retrieved and before performing the update,
compare
them to the current field values to see if they've changed in the meantime."
So, if I am modifying 5 field values in the table, I need to keep track of
the value of all 5 field, right ?
Since I will only call Execute method (a 1 line code) (instead of open the
recordset, update all 5 fields, and call Update method), thus I do not
retrieve the values. Using Execute method when do you compare the field
values ? If you don't mind, can you post a snipet of codes ?
Thanks again. I appreciate it.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:OLCC8sF...@TK2MSFTNGP05.phx.gbl...
On Wednesday, May 02, 2007 10:45 AM
fniles wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
Thank you everybody.
You are all correct. I check CursorType after the recordset is open, and it
is adOpenStatic and the LockType is adLockOptimistic
"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message
news:uUcnETEj...@TK2MSFTNGP02.phx.gbl...
On Wednesday, May 02, 2007 11:46 AM
Bob Barrows [MVP] wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
fniles wrote:
From your description, it did not appear that you were worried about
concurrency. Are you worried about updating changes made by other users
between the time the current user viewed the data and the time you are
sending the current user's changes to the database?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
On Wednesday, May 02, 2007 12:03 PM
fniles wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
For this part of the program, since I always want the latest change made by
the last user, I guess not. If user A read the screen, then user B update
it, then user A update it, I want user A to see user A's changes.
But, for other application I might want to worry about the concurrency
issue. Thus, my questions below.
1. Does using SQL DML means calling the Execute method to call the stored
procedure ?
2. You mentioned that "you need to keep track of the state of the field
values when they were originally retrieved and before performing the
update, compare them to the current field values to see if they've changed
in the meantime." So, if I am modifying 5 field values in the table, is it
correct that I need
to keep track of the value of all 5 field ?
If I only call Execute method (a 1 line code) (instead of open the
recordset, update all 5 fields, and call Update method), I do not retrieve
the values, right ? When using Execute method, when do you compare the field
values ? If you don't mind, can you post a snipet of codes ?
Thanks a lot.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:uXS6NFNj...@TK2MSFTNGP05.phx.gbl...
On Wednesday, May 02, 2007 12:31 PM
Bob Barrows [MVP] wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
fniles wrote:
Yes, or using my preferred procedure-as-connection-method technique.
See:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en
Yes.
Alternatively, you could add a timestamp column to the table in the
database. Despite its name, timestamp has nothing to do with time (or
date). You should look it up in BOL, but for now, think of it as a
column whose value changes whenever anything in the current row changes.
To use it, retrieve the value of that column when initially retrieving
data. When sending mods to the stored procedure, send the timestamp
value also. The stored procedure compares the current timestamp column
value with the one passed to it - if they are the same, it performs the
update and retrieves and returns the new timestamp value (probably via
an output parameter). If they are different, ... well, do whatever you
want. Some options are:
a) raise an error
b) perform the update but raise an error
c) retrieve the new row values and return them to the caller so the user
can see them and decide whether to perform his update
Right
In the stored procedure.
I'm not sure which code you are asking about. The link I posted above
explains how to execute stored procedures. Do you need help with writing
a stored procedure?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
On Wednesday, May 02, 2007 3:01 PM
fniles wrote:
Thank you.
Thank you.
I do something like this, but it gives me the error "Unclosed quotation mark
after the character string ''" in VB6 when I run it.
Dim rstHistTrades As New ADODB.Recordset
Dim rstHistTrades2 As New ADODB.Recordset
Dim sSQL As String
Dim vChange As Variant
sSQL = "select change from histtradesorig where [order] = 'D1003-205365'"
rstHistTrades.Open sSQL, g_adoCon
If Not (rstHistTrades.EOF) Then
vChange = rstHistTrades.Fields("Change")
g_adoCon.Execute "updateHisttradesorig @Price=1600,
@OrderID='D1003-205365', @ts='" & vChange & "'" --> ERROR here
End If
This is the Stored procedure:
create procedure UpdateHisttradesOrig
@Price float,
@OrderID varchar(50),
@ts as varchar(50)
as
declare @CurrentChange timestamp
select @CurrentChange = (select Change from Histtradesorig where [order] =
@OrderID)
if convert(timestamp,@ts) = @CurrentChange
update Histtradesorig set fillprice = @Price where [order] = @OrderID
else
raiserror ('Timestamp is different',16,1)
Do I do it incorrectly ?
Thanks again.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:u0b5TbNj...@TK2MSFTNGP04.phx.gbl...
On Wednesday, May 02, 2007 3:22 PM
Bob Barrows [MVP] wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
fniles wrote:
What is the entire text of the error? I need to know if it's a VB
compiler or runtime error, or an error returned from ADO.
Since you are not using any output parameters, I would have executed it
like this:
adoCon.updateHisttradesorig 1600,"D1003-205365", vChange
However, to debug dynamic sql, you need to look at the result of the
dynamic sql:
dim sql as string
sql="updateHisttradesorig @Price=1600, " & _
"@OrderID='D1003-205365', @ts='" & vChange & "'"
debug.print sql
g_adoCon.Execute sql,,adCmdText
(always tell ADO what the command type is)
After running the code, copy the result from the Immediate window and
verify whether it runs without error in SQL Query Analyzer
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
On Wednesday, May 02, 2007 3:50 PM
fniles wrote:
Thanks.
Thanks.
This is from the Immediate window:
updateHisttradesorig @Price=1600,@OrderID='D1003-205365', @ts=' ?'
Then, when it executes the Execute command I get the error:
Run-time error '-2147217900 (80040e14':
Unclosed quotation mark after the character string
In the SQL Server Management Studio (SQL Enterprise Manager in SQL 2000)
when I execute the query
updateHisttradesorig @Price=1600,@OrderID='D1003-205365', @ts=' ?'
it returns the following:
Msg 50000, Level 16, State 1, Procedure UpdateHisttradesOrig, Line 11
Timestamp is different
Is my Stored Procecure correct ? (notice in the SP the parameter @ts = as
varchar(50), then later on I say:
if convert(timestamp,@ts) = @CurrentChange
update Histtradesorig set fillprice = @Price where [order] = @OrderID
else
raiserror ('Timestamp is different',16,1)
From VB6 I would like to already pass @ts as a timestamp datatype, but I
don't know how to pass a timestamp variable from VB6.
Thank you.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:ufFj16Oj...@TK2MSFTNGP02.phx.gbl...
On Wednesday, May 02, 2007 4:24 PM
Bob Barrows [MVP] wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
fniles wrote:
Oh! @ts is the timestamp! It needs to be declared with the proper
datatype (timestamp) in the CREATE PROCEDURE statement:
Carl Prothman compiled a very useful datatype cross-reference here:
http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
Looking there, you will see it has to be passed as a Variant. You may
need to use an explicit Command object. This KB article may help:
http://support.microsoft.com/kb/q196590/
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
On Wednesday, May 02, 2007 5:18 PM
fniles wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
I can get it work using command object like the following:
Dim rstHistTrades As ADODB.Recordset
Dim rstHistTrades2 As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
Dim sSQL As String
Dim Affected As Long
Dim vChange As Variant
sSystem = GetSetting("BestDirect", "Startup Files", "System")
DBInit GetSystem(sSystem)
Set rstHistTrades = New ADODB.Recordset
sSQL = "select change from histtradesorig where [order] = 'D1003-205365'"
rstHistTrades.Open sSQL, g_adoCon
If Not (rstHistTrades.EOF) Then
vChange = rstHistTrades.Fields("Change")
cmd.ActiveConnection = g_adoCon
cmd.CommandType = adCmdText
cmd.CommandText = "execute updateHisttradesorig ?,?,?"
With cmd
.Parameters.Append .CreateParameter("Price", adDouble,
adParamInput, 8, 1601)
.Parameters.Append .CreateParameter("OrderID", adVarChar,
adParamInput, 50, "D1003-205365")
.Parameters.Append .CreateParameter("ts", adVarBinary,
adParamInput, 8, vChange)
End With
On Error GoTo err
Set rstHistTrades2 = cmd.Execute(Affected)
Is it correct that I
1. first query the timestamp from the database (sSQL = "select change from
histtradesorig where [order] = 'D1003-205365'" like above)
2. then submit that to the stored procedure
3. in the stored procedure i do a comparison of that value with the current
timestamp like so:
create procedure UpdateHisttradesOrig
@Price float,
@OrderID varchar(50),
@ts as timestamp
as
declare @CurrentChange timestamp
select @CurrentChange = (select Change from Histtradesorig where [order] =
@OrderID)
if @ts = @CurrentChange ---> COMPARING THE current TimeStamp with the one
coming in the input parameter
update Histtradesorig set fillprice = @Price where [order] = @OrderID
else
raiserror ('Timestamp is different',16,1)
Is there a way to make it work using procedure-as-connection-method
technique ?
Thanks.
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:ON7HodPj...@TK2MSFTNGP04.phx.gbl...
On Wednesday, May 02, 2007 6:30 PM
Bob Barrows [MVP] wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
fniles wrote:
Well, sort of. This should have been done when you first retrieved the
record to display to the user. Doing it now is a little late. You should be
passing that timestamp value to the method that contains this code.
Yes
Seems right to me
I'm not sure, never having tried it. Normally, when using this technique, I
also use output parameters requiring an explicit command object .
Why don't you try and let me know?
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
On Thursday, May 03, 2007 9:54 AM
fniles wrote:
Re: Error Row cannot be located for updating. Some values may have been changed since it was last read
I see what you meant.
Regarding the procedure-as-connection-method I will try it later, now that
the method that I use is working.
Thanks a lot for your help. You are very helpful. Thanks !
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:OnpLylQj...@TK2MSFTNGP06.phx.gbl...
On Wednesday, October 29, 2008 8:07 PM
whit snell wrote:
'row cannot be located for updating some values may have been changed since it was last read'
We have been dealing with this error on Microsoft RMS using SQL 2000 - won't allow us to add product to our retail database (big problem). When we uninstall SP3 we no longer get the error. Even after applying the SP3 fix, we still get the error. Now Microsoft support is telling us to uninstall SP3 on about 30 machines. Wish there could be a solution other than this.
Any help much appreciated.
On Wednesday, October 29, 2008 8:53 PM
Bob Barrows [MVP] wrote:
Re: 'row cannot be located for updating some values may have been changed since it was last read'
whit snell wrote:
I am not sure what RMS is. Is it this?
http://www.microsoft.com/dynamics/rms/Default.mspx
If so, we're not going to be able to help - MS knows more about the code
than we can ever know.
If not, could you show a little bit of the code that causes the error?
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
On Sunday, November 23, 2008 7:11 AM
Fahim Ahmad wrote:
error
Dear All,
b4 rs.open write "Rs.CursorLocation=2" it will work
On Sunday, November 23, 2008 8:47 AM
Bob Barrows wrote:
Re: error
Fahim Ahmad wrote:
What problem is this advice intended to solve? There are situations where I
would recommend against following this advice.
Do you know what setting the CursorLocation to 2 actually does?
The ADO documentation can be found here:
http://msdn2.microsoft.com/en-us/library/ms675532.aspx
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
On Thursday, December 04, 2008 11:30 PM
Daniel Arnaldo wrote:
Problem solved: "Row cannot be located for updating. Some values may have been changed since it was last read"
This problem is solved with the change in cursorlocation=clUseServer.
Than you to the post.
Best Regards
Daniel Arnaldo
Delphi programmer
On Saturday, April 25, 2009 2:37 AM
Shilpa Sanghvi wrote:
same error in VB 6 coding
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
On Saturday, July 11, 2009 6:12 AM
George wrote:
have the same problem
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
Submitted via EggHeadCafe - Software Developer Portal of Choice
Spyware Redux: Protect Your Ass!
http://www.eggheadcafe.com/tutorials/aspnet/ace58db7-7bb6-48c4-8021-b1b9d608ee00/spyware-redux-protect-yo.aspx