Update Conflict in Cursor

[MikeMV]

Hello,

I am using VFP 9.0 manipulating data on a single table MS SQL 2005 database.

What I am trying to do is dump the content of the table nightly and upload new records, there are about 13k records and about 90% of them become obsolete daily, that I why I rather wipe out the data and upload fresh data.

I am getting the update conflict in cursor error when I try to delete the content of the table, I use this code in other places without this problem. The one thing that I am not sure if it is causing the conflict it that the name of the local table that I use as the seed is the same as the sql table that I am trying to populate, would this matter?

I will greatly appreciate any feedback you can provide.

* Begin Updating of remote table
*
OPEN DATABASE vinventory
SET DATABASE TO vinventory
DELETE VIEW vupload
*
CREATE SQL VIEW vupload;
REMOTE CONNECTION Reporting;
AS select saleshistory.StockCode, saleshistory.OnHand, saleshistory.Available, saleshistory.OnOrder, saleshistory.YtdSold, saleshistory.PrevYearSold, saleshistory.Supplier, saleshistory.Warehouse, saleshistory.KitsBfYtd, saleshistory.KitsBfPrev, saleshistory.YtdTotal, saleshistory.PrevTotal, saleshistory.FutureGross, saleshistory.FutureNet FROM saleshistory;
ORDER BY saleshistory.StockCode
*
DBSETPROP ("vupload", "View", "SendUpdates", .T.)
DBSETPROP("vupload" , "VIEW","WhereType",3)
DBSETPROP("vupload.stockcode", "Field", "Updatable", .T.)
DBSETPROP("vupload.onhand", "Field", "Updatable", .T.)
DBSETPROP("vupload.available", "Field", "Updatable", .T.)
DBSETPROP("vupload.onorder", "Field", "Updatable", .T.)
DBSETPROP("vupload.ytdsold", "Field", "Updatable", .T.)
DBSETPROP("vupload.prevyearsold", "Field", "Updatable", .T.)
DBSETPROP("vupload.supplier", "Field", "Updatable", .T.)
DBSETPROP("vupload.warehouse", "Field", "Updatable", .T.)
DBSETPROP("vupload.kitsbfytd", "Field", "Updatable", .T.)
DBSETPROP("vupload.kitsbfprev", "Field", "Updatable", .T.)
DBSETPROP("vupload.ytdtotal", "Field", "Updatable", .T.)
DBSETPROP("vupload.prevtotal", "Field", "Updatable", .T.)
DBSETPROP("vupload.futuregross", "Field", "Updatable", .T.)
DBSETPROP("vupload.futurenet", "Field", "Updatable", .T.)

*
DBSETPROP("vupload.stockcode", "Field", "UpdateName", [saleshistory.StockCode])
DBSETPROP("vupload.onhand", "Field", "UpdateName", [saleshistory.OnHand])
DBSETPROP("vupload.available", "Field", "UpdateName", [saleshistory.Available])
DBSETPROP("vupload.onorder", "Field", "UpdateName", [saleshistory.OnOrder])
DBSETPROP("vupload.ytdsold", "Field", "UpdateName", [saleshistory.YtdSold])
DBSETPROP("vupload.prevyearsold", "Field", "UpdateName", [saleshistory.PrevYearSold])
DBSETPROP("vupload.supplier", "Field", "UpdateName", [saleshistory.Supplier])
DBSETPROP("vupload.warehouse", "Field", "UpdateName", [saleshistory.Warehouse])
DBSETPROP("vupload.kitsbfytd", "Field", "UpdateName", [saleshistory.KitsBfYtd])
DBSETPROP("vupload.kitsbfprev", "Field", "UpdateName", [saleshistory.KitsBfPrev])
DBSETPROP("vupload.ytdtotal", "Field", "UpdateName", [saleshistory.YtdTotal])
DBSETPROP("vupload.prevtotal", "Field", "UpdateName", [saleshistory.PrevTotal])
DBSETPROP("vupload.futuregross", "Field", "UpdateName", [saleshistory.FutureGross])
DBSETPROP("vupload.futurenet", "Field", "UpdateName", [saleshistory.FutureNet])
*
DBSETPROP("vupload.stockcode", "Field", "KeyField", .t.)
*
USE vupload
GO BOTTOM
DELETE ALL NOOPTIMIZE
*
APPEND FROM saleshistory
*
CLEAR
CLOSE DATABASES ALL
CLOSE ALL
*
* End of Updating of remote table

 

[Dan Freeman]

Is this pseudo-code? It won't come close to working "as is".

You don't have a tableupdate() anywhere so none of your changes are sent to the back end.

Why are you re-creating the view each time? Why not create it once and re-use the definition?

I'm willing to bet your error is coming from the APPEND FROM. Since your delete isn't removing records, you're getting a duplicate primary key. But that's just a guess.

 

[MikeMV]

Thanks Dan,

I think you are onto something, the problem only shows up when there are "duplicate" records in the back end.

The back end does not have a primary key and records with duplicated stock codes are legit. For example this is what part of the data looks like:

stockcode warehouse

1000 A
1000 B
2000 A
2000 B

If I filter the selection and select warehouse A only the process works. When the back end is empty the update works well, even when I allow for multiple warehouse records. The problem comes when I try to delete all records from the back end when multiple warehouses exist.

The code works as I use for different purposes, but you are right it is not elegant and I guess I should work towards learning to streamline.

You are correct that I don't have a tableupdate(), but the append from saleshistory accomplishes the posting of the records.

Any ideas?

Thanks again.