TABLE UPDATE PROBLEM- VISUAL FOXPRO 7

[IvySoe]

I am using Visual Foxpro 7 and foxpro database and foxpro exe is placed on server. user create shortcut from pc to server to use the application.

When updating multiple records of different tables from foxpro Form Save button, data of quantity field holding numeric value of quantity cannot update for some records of these tables.

for example: There is sales invoice sold multiple items - take out quantity from related sales order and good receive.

When delete these multiple sale items from sales invoice, the quantity will be restore back to each sales_order table , and good_receive table relating to individual items.

For the sales invoice of total 10 items which is 10 details records, when delete 5 items, only 3 items of related Sales order and good receive quantity can be restore back and other 2 record never update quantity in sales order and good receive table.
There is no error came up. only data is incorrect in Sales order and good receive table.
I add my code block of processing tables like TableUpdate() in begin and end transaction block along with Aerror() to catch the error. Tables are set buffer mode in form dataEnvironment.
This problem only happen of some records under the same document while the deletion done at the same time for all 5 records. only 2 records out of 5 are wrong.

The problem might be server client connection or any other memory problem or something like this. and i cannot trace it.
Please anybody can help me out.

 

[IvySoe]

Hi,

I am searching sivd_fil deleted row field in ssod_fil to add back deleted quantity into ssod_fil.


Thanks for the code. I will try it.

 

[IvySoe]

Hi Olaf,

When i force adding duplicate rows and click save to make tableupdate(),program stopped at tableupdate() line and the error raised in AERROR(). I did it to test AERROR() raised or not.

BTW, can you all share any source about debugging and tracing , how to use visual foxpro debugger.

>>>force adding duplicate index row
>>>That's not happening during Tableupdate, is it?

 

[IvySoe]

sivd_fil is actually a view (named sivd_view) created under dbc.
when user select a row in a grid(datasource=sivd_view) and click delete button, sivd_view current row is user selected row to delete.
before delete, search related field of sivd_view current selected row data in ssod_fil, then add back stock. and then, delete sivd_view row.

 

[IvySoe]

Hi MK,

Can you explain a bit more why your second indexseek() use sivd_fil. the table that want to move the record when matching record from sivd_fil is ssod_fil. shouldn't it be ssod_fil in second indexseek()?

 

[mjcmkrsr]

Hi,

when user select a row in a grid(datasource=sivd_view) and click delete button, sivd_view current row is user selected row to delete. before delete, search related field of sivd_view current selected row data in ssod_fil, then add back stock. and then, delete sivd_view row.

OK - then it could be something like this

lcStringToSearch = sivd_view.sivd_refcd + sivd_view.sivd_refno + str(sivd_view.sivd_refln,3)
select ssod_fil
If IndexSeek (lcStringToSearch, .T., "SSOD_FIL", YourIndexTag)
[indent]replace ssod_ivqty with ssod_ivqty - [b][COLOR=#EF2929]sivd_view.sivd_qty[/color][/b][/indent]
else
...
endif 

&& Now you may DELETE the record from SIVD_VIEW - you can't obviously do it before since you still need SIVD_VIEW.SIVD.QTY (see above)

Select SIVD_VIEW
If IndexSeek(lcStringToSearch, .T., "SIVD_VIEW", YousIndexTag) && Be sure you point to the right record
[indent]Delete next 1[/indent]
ELSE
...
Endif

hth

MK

 

[Olaf Doschke]

Using the debugger? It would help to know where your detail problem is, debugging is a vast topic.

Firt of all: In options set your reference for the debugger embedded into the VFP _screen or as separate window. I put it seperate, but that's a matter of taste and your display/monitor setup.

To get there set brakpoints, they already have vast options to do them conditionally only or let the suspended single step debug mode occur anywhere you are, when a certain condition is met. You can do that with code, too, with ASSERTs. When the asserted condition is not met when the ASSERT is exceuted, you get a dialog with an option to go into the debugger, so that's equal to a breakpoint with a condition. You can also use SUSPEND or least recommendable SET STEP ON in code. The bad thing about SET STEP ON in comparison with a normal breakpoint is, you can't remove it during debugging.

In the dedugger the trace window to single step through code and have many options, like setting and unsetting breakpoints, setting the cursor somewhere and "run to cursor" or set next statement to repeat or skip some code.
You have the Locals window to watch variables in scope.
You have the watch window to add other expressions than variable to watch, eg USED("somealias") or GETFLDSTATE or whatever expression you can think of.
You have the debug output window to see output you do via DEBUGOUT.
You have the stack window, where it's sometimes useful to see where you came from, clicking on other than the topmost line the trace window will show the code, which called the current code. Unfortunatley just that, no chance to set the program pionter there or inspect variables in scope there.

If you'd have detail questions it'll help more to guide you through your debugging problems to solve the transaction problem.

Bye, Olaf.

 

[Olaf Doschke]

>force adding duplicate rows and click save to make tableupdate(),program stopped at tableupdate() line and the error raised in AERROR().
OK, then you have a normal error that triggers the error handler, yes.
Your error handling is very minimal, but you show the error via messagebox and then RETURN. If a user kills the task a rollback doesn't occur, but the runtime destroy could end open transactions.

You better put the tableupdates into TRY..CATCH to be able to rollback in case of errors.
>I re-enter manually the problem document sales invoice exactly(delete it first and key in again) to generate error again but nothing wrong. so, it is clearly no logic error in coding.
Well, if you had the error handler raised through index violation there is some flaw in generating keys, or do they not get generated? Do users enter keys? How do you check and force their uniquness?

Bye, Olaf.

 

[IvySoe]

Unfortunately, try catch does not support in visual foxpro version 7...

 

[IvySoe]

in save button click, before begin transaction block of TABLEUPDATE() and REVERT things, following is done to control unique value.
any suggestion for improvement

select pgrh_fil
if !flock()
	=messagebox("File is locked by another user, Please try again later",0,"File Locked")
	return 0
endif
set delete off
select delete() as delfg from pgrh_fil into cursor pgrh_dup ;
where pgrh_trncd = thisform.text1.value and pgrh_docno = thisform.text2.value
set delete on
select pgrh_dup
go top
if !eof()
   if delfg
           rmsg = "Please run Re-index program to key in same document number again"
           =messagebox(rmsg,0,"Document Deleted")
   else
       	   =messagebox("Record already exists, Please check Key Fields",0,"Duplicate Record")
   endif
   select pgrh_fil
   return 0
endif

 

[Olaf Doschke]

Try Catch in VFP can be done by ON ERROR llError = .T., set llError = .F. and do some code. Afterwards check llError, if .T. do the catch block code (handling the error), finally reset the previous normal error handling.

It's always better to generate keys, so code enhancments are quite useless, you could use INDEXSEEK to find already existing index keys (also in deleted records), you already were pointed to that function by MK, and I know it exists in VFP7, too.

In the end nothing will check duplicates in the buffered changes and so there always is the chance of duplicate coming in from two new documents entered with same keys in the same buffer. If users can only enter a single document, that's not the main problem, still nothing can prevent double keys as easy as generating keys instead of letting users enter them. In samples of VFP you'll find a newid() stored procedure in the tastrade.dbc sample database you could use, you can also modify it to count up the docno for each trncd group of documents instead of being a global counter per table.

Bye, Olaf.

 

[mjcmkrsr]

Hi,

In addition to what Olaf suggests, you may want to have a PRIMARY/CANDIDATE index on pgrh_trncd + pgrh_docno in pgrh_fil. An error would be thrown (Error N° 1884 - Uniqueness of index "name" is violated)if you attempt to add a duplicate value - which you might catch with ON ERROR ...

hth

MK