I have a situation, reproducable very occasionally but not consistently,
where a requery after tableupdate fails with uncommited changes. I've looked
at the view cursor in Debug and it looks really strange. This is the
scenario (code that creates the view is below).
The view is of a child table and has a filter in place equal to the id of
the parent table. The view is in item number order. Add 5 records (items 1
to 5) to the view, delete the 3rd one (causes item 4 and 5 to resequence to
item 3 and 4) and then save. Save scans the file (with set deleted off) and
issues a tableupdate on each record. The subsequent requery sometimes fails.
Browsing the cursor shows why it has failed but I can't see a reason for the
cursor being in the state it is in!
Debug says these are 8 records. Browsing displays 5. 2 records belonging to
the previous header even though there is a filter in place so I don't know
why these are showing. The 2nd item of the added items does not display.
After setting the filter off and setting deleted off, 6 records display, the
same 5 as above plus the deleted one. I then set the order off and 8 records
then appeared. The 8th record was full of zeroes and characters that look
like a thick letter I in some of the fields. Where this came from I have no
idea.
I have no idea where to go from this point as it seems to be VFP7 doing
something outwith my control. Has anyone had this kind of problem before?
Thanks for any help.
Bernie Beattie
Here is the code that creates the view:
CREATE SQL VIEW V_NLJDTL AS ;
SELECT NLJDTL.nljdtl_id AS nljdtl_id,;
NLJDTL.nljhead_id AS nljhead_id,;
NLJDTL.item_no AS item_no,;
NLJDTL.acctno AS acctno,;
NLJDTL.db_or_cr AS db_or_cr,;
NLJDTL.tran_amt AS tran_amt,;
IIF(nljdtl.db_or_cr='D',tran_amt,0000000000000.00) AS Debit,;
IIF(nljdtl.db_or_cr='C',tran_amt,0000000000000.00) AS Credit,;
NLJDTL.property AS property,;
NLJDTL.REFERENCE AS REFERENCE,;
NLJDTL.reconc_id AS reconc_id,;
NLJDTL.added_dt AS added_dt,;
NLJDTL.changed_dt AS changed_dt,;
NLJDTL.vatst_pr AS vatst_pr, ;
nljdtl.rcveble_id as rcveble_id,;
nljdtl.plinv_id as plinv_id,;
NLACCD.COMPULSPRP AS COMPULSPRP,;
NLACCD.ACCTYPE_ID AS ACCTYPE_ID ;
FROM NLJDTL,NLACCD ;
WHERE NLJDTL.acctno=NLACCD.acctno AND NLJDTL.acctno<>0 AND
nljdtl.nljhead_id=?pnhead_id
*make the view updatable
DBSETPROP('v_nljdtl','View','Tables','nljdtl')
DBSETPROP('v_nljdtl.nljdtl_id','Field','KeyField',.T.)
DBSETPROP('v_nljdtl.nljdtl_id','Field','UpdateName','nljdtl.nljdtl_id')
DBSETPROP('v_nljdtl.nljhead_id','Field','UpdateName','nljdtl.nljhead_id')
DBSETPROP('v_nljdtl.item_no','Field','UpdateName','nljdtl.item_no')
DBSETPROP('v_nljdtl.acctno','Field','UpdateName','nljdtl.acctno')
DBSETPROP('v_nljdtl.db_or_cr','Field','UpdateName','nljdtl.db_or_cr')
DBSETPROP('v_nljdtl.tran_amt','Field','UpdateName','nljdtl.tran_amt')
DBSETPROP('v_nljdtl.property','Field','UpdateName','nljdtl.property')
DBSETPROP('v_nljdtl.reference','Field','UpdateName','nljdtl.reference')
DBSETPROP('v_nljdtl.reconc_id','Field','UpdateName','nljdtl.reconc_id')
DBSETPROP('v_nljdtl.added_dt','Field','UpdateName','nljdtl.added_dt')
DBSETPROP('v_nljdtl.changed_dt','Field','UpdateName','nljdtl.changed_dt')
DBSETPROP('v_nljdtl.vatst_pr','Field','UpdateName','nljdtl.vatst_pr')
DBSETPROP('v_nljdtl.nljdtl_id','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.nljhead_id','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.item_no','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.acctno','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.db_or_cr','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.tran_amt','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.property','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.reference','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.reconc_id','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.added_dt','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.changed_dt','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.vatst_pr','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.rcveble_id','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.plinv_id','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.compulsprp','Field','Updatable',.T.)
DBSETPROP('v_nljdtl.acctype_id','Field','Updatable',.T.)
DBSETPROP('v_nljdtl','View','SendUpdates',.T.)
*open the view
SELECT SELECT(1)
USE V_NLJDTL EXCL
INDEX ON STR(nljhead_id)+STR(item_no,3) TAG UNIQUE_KEY
INDEX ON item_no TAG item_no
INDEX ON acctno TAG acctno
INDEX ON Debit TAG Debit
INDEX ON Credit TAG Credit
INDEX ON UPPER(property) TAG property
INDEX ON UPPER(REFERENCE) TAG REFERENCE
CURSORSETPROP("buffering",5) &&optimistic table buffering