Update Conflict when DELETE a new append record

[ArthurLiu88]

I don't understand where is the update conflict. The code is as follows:

SCATTER NAME oFieldValue MEMO FIELDS EXCEPT timestamp_column, idnbr
APPEND BLANK
GATHER NAME oFieldValue MEMO
REPLACE curEmployee.name WITH ALLTRIM(curEmployee.name) + "-COPY" IN curEmployee

lcAlias = "curEmployee"
TABLEUPDATE(1, .T., lcAlias)
GO (RECNO(lcAlias))

DELETE IN (lcAlias)

SKIP IN (lcAlias) && where the error happens
IF EOF(lcAlias)
......
ENDIF

"curEmployee" is an updatable cursor using SPT in VFP9 and SQL2000.
Who can help? Many thanks
Arthur

 

[bborissov]

What is the result of TableUpdate()?

CATTER NAME oFieldValue MEMO FIELDS EXCEPT timestamp_column, idnbr
APPEND BLANK
GATHER NAME oFieldValue MEMO
REPLACE curEmployee.name WITH ALLTRIM(curEmployee.name) + "-COPY" IN curEmployee

lcAlias = "curEmployee"
IF NOT TABLEUPDATE(1, .T., lcAlias)
   AERROR(laError)
   MESSAGEBOX([Update is not successful ]+laError[1,2])
ELSE
    GO (RECNO(lcAlias))
        
    DELETE IN (lcAlias)

    SKIP IN (lcAlias)   && where the error happens
    IF EOF(lcAlias)
        ......
    ENDIF
ENDIF

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ArthurLiu88]

Borislav Borissov
Thanks for your prompt reply. TABLEUPDATE is fine.

Arthur

 

[Mike Lewis]

Arthur,

If I've understood your code right, I think you are appending a record and then immediately deleting it. I assume that's not what you intended.

Whether or not that's correct, the point is that VFP will be sending a DELETE command with a WHERE clause; the WHERE clause will contain field values that reflect the state of the cursor before the update. Given that the record in question has since been altered, the server is reporting that no records match the WHERE condition, so VFP interprets it as an update conflict.

If that analysis is right, the solution might be simply to requery the cursor after the TABLEUPDATE() and before the DELETE.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[ArthurLiu88]

Hi Mike
I just assume the end user will add a new record and delete it immediately. I put these two function (Add and Delete) in one question in order to easy to read.

Thanks for your advise, but I use the SPT not remote view. Do you mean I have to SQLEXEC("SELECT ...") again, because REQUERY() do not support SPT.

Is there any other easy and simply way.

Many thanks
Arthur

 

[Mike Lewis]

Arthur,

Ah, yes, I see now that you mentioned an updatable cursor rather than remote view. But I still don't understand what you are trying to achieve. If you try to delete a record that doesn't yet exist on the server, you're sure to get an update conflict.

Is there any reason why you can't simply send a SQL DELETE to the server, using a primary key to identify the record? Something like:

DELETE FROM <table> WHERE <primary key> = <some value>

That way, you'll only get an update conflict if the primary key doesn't exist on the server, in which case you can ignore it.

If I've still misunderstood the requirement, my aplogies.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[ArthurLiu88]

Mike
Thanks for your great help.
I found the reason. The primary key "idnbr" is set to INT IDENTITY in the server and will be auto increased. When adding a new record, after TABLEUPDATE, the field "idnbr" is affected on the server (auto increased), but the value of the new added "idnbr" in local cursor is 0. That's why the updated conflict happened when delete it immediately.

I'm wondering if it is any wrong for setup the updatable cursor. I using the following codes:

CURSORSETPROP("Tables", lcTableName, lcCursorName)
CURSORSETPROP("KeyFieldList", lcKeyFieldName, lcCursorName)
CURSORSETPROP("UpdatableFieldList", lcUpdatableFieldList, lcCursorName)
CURSORSETPROP("UpdateNameList", lcUpdateNameList, lcCursorName)
CURSORSETPROP("SendUpdates" , .T., lcCursorName)

I'm sorry if I can not point it out clearly.

Arthur Liu (Shanghai, China)

 

[Mike Lewis]

Arthur,

The correct way to deal with this depends in part on what back end you are using. I can only speak about SQL Server. I'm not familiar with other databases.


With SQL Server, you would send the following command immediately after inserting the new record:

SELECT @@IDENTITY AS 'Identity'

That will give you the value of the new primary key in your SQL Results cursor. The cursor will have one row, and one field, named Identity.

You can then replace that value into your local cursor:

REPLACE MyCursor.IDNbr WITH SQLResults.Identity

I think that should work.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[ArthurLiu88]

Mike
It works.
Thank you for your great help.

Arthur Liu (Shanghai, China)