New voucher number issue

[TariqMehmod]

Dear Experts,

I am using these code to save data from VFP 9 to sqlserver 2008.
The codes work fine on single pc.
When I try run my codes on multiuser system then it does display correct next voucher number.
Please review What I am doing wrong.

[pre]

*--------------------------------------------------------------------------------------------------
*--- PRODUCTS TABLE
*-------------------------------------------------------------------------------------------
LiCountx=0
Select cash_s1
Count For !Deleted() To LiCountx
If LiCountx>0
Repl All cr_amount With Round(cr_amount,0),;
vou_no With Thisform.text1.Value,;
Type With 'P',;
Date With date1,;
vou_type With vou_type1,;
acc_name With partyname,;
code2 With Alltrim(Thisform.text10.Value),;
dr_amount With 0

Insert Into cash_s1 (Date,vou_no,vou_type,acc_code,acc_name,qty,rate,dr_amount,cr_amount,code2,Type) ;
SELECT Date,vou_no,vou_type,'2310001' ,acc_name,qty,rate,cr_amount,dr_amount,acc_code,'C' From cash_s1
*-------------------------------------------------------------------------------------------
*--- DEDUCTION TABLE
*-------------------------------------------------------------------------------------------
*---prepare credit entry
If Thisform.text14.Value>0
Thisform.creat_cash_s2()
Select CASH_S2
Append Blank
Repl All vou_no With Thisform.text1.Value,Type With [D],Date With Thisform.text3.Value,;
acc_code With [3501001], dr_amount With Round(Thisform.text14.Value,0),cr_amount With 0,;
vou_type With [ST], acc_name With [CASH SALE],code2 With Thisform.text10.Value,;
dr_amount With Round(Thisform.text14.Value,0)

Insert Into cash_s2 (Date,vou_no,vou_type,acc_code,acc_name,qty,rate,dr_amount,cr_amount,code2,Type) ;
SELECT Date,vou_no,vou_type,'3501001' ,acc_name,qty,rate,dr_amount,cr_amount,acc_code,'' From cash_s2
*-------------------------------------------------------------------------------------------
* collect data from above 2 cursors into one cursor (cash_s1+cash_s2)
Thisform.all_data() && temporary cursor to hold data
Select all_data
Append From Dbf("cash_s2")For !Deleted()
Append From Dbf("cash_s1")For !Deleted()
*-------------------------------------------------------------------------------------------
*--- begin transaction starts
*-------------------------------------------------------------------------------------------
Begin Transaction
SQLSetprop(m.con, "Transactions",2)

lok=.T. && variable to dectect error

If Thisform.edit_mode
SQLExec(cmd,"delete from cashsalpt where date=?thisform.text3.value and vou_no=?thisform.text1.value")
SQLExec(cmd,"delete from cash_sumt where date=?thisform.text3.value and vou_no=?thisform.text1.value")

Else
*--- get NEW voucher number
cmd="select MAX(vou_no)as max_sno from cash_sumt where date=?thisform.text3.value"
If SQLExec(con,cmd,'table1')<0
Aerror(laerror)
Messagebox(laerror[1,2])
Return.F.
Thisform.text1.Value=1
Else
Select table1
Thisform.text1.Value=Nvl(max_sno,0)+1
Endif
Endif

Select all_data
Replace All vou_no With Thisform.text1.Value
Replace All ed_mode With 1
Replace All ed_event With Datetime()

Select all_data

Go Top
Scan For !Deleted() And vou_no>0
TEXT TO m.cmd NOSHOW
INSERT INTO cashsalpt(vou_no,date,vou_type,acc_code,tittle,qty,dr_amount,cr_amount,
rate,code2,type,acc_name,pur_rate,sal_rate)
values(?all_data.vou_no,?all_data.date,?all_data.vou_type,?all_data.acc_code,
?all_data.tittle,?all_data.qty,?all_data.dr_amount,?all_data.cr_amount,
?all_data.rate,?all_data.code2,?all_data.type,?all_data.acc_name,?all_data.pur_rate,?all_data.sal_rate)
ENDTEXT

If SQLExec(m.con,cmd)<0
Aerror(laerror)
Messagebox(laerror[1,2])
lok=.F.
Endif
Endscan
*-------------------------------------------------------------------------------------------
*--- summary table
*-------------------------------------------------------------------------------------------
TEXT TO m.cmd NOSHOW

INSERT INTO cash_sumt(vou_no,date,ed_cnt,vou_type ,acc_code,acc_name,sales_r,
dr_amount,gate_pass ,code2)
values(?thisform.text1.value,?Thisform.text3.value,
?my_edits,'ST',?Thisform.text10.Value,'CASH SALE',?Thisform.text13.Value,
?thisform.text17.value,?thisform.text15.value,?Thisform.text10.Value)
ENDTEXT

If SQLExec(m.con,cmd)<0
Aerror(laerror)
Messagebox(laerror[1,2])
lok=.F.
Endif
*-------------------------------------------------------------------------------------------
*--- Final decesion to save transaction or reject
*-------------------------------------------------------------------------------------------
If lok=.F.
Sqlrollback(m.con)
Rollback
Messagebox("Invoice Not Saved",0+64,"System",3000)
Else
If Sqlcommit(m.con)<0
Aerror(laerror)
Messagebox(laerror[1,2])
Rollback
Else
End Transaction
Messagebox("Invoice Saved",0+64,"System",3000)
Endif
=SQLSetprop( m.con,"Transactions",1)
Endif
Endif


[/pre]

 

[Mike Lewis]

Hi Tariq,

I'm sorry nobody has yet been able to help you with your problem. I have only had a quick look at your code, but I can't see anything that would cause it to behave differently in a multi-user environment compared to a single-user one.

It might help if you could amplify the problem. What is actually happening in the multi-user system when the problem occurs? Is another user running the same code at the same time? Are any of the tables locked by another user? And what locks are you applying in this code (are the tables buffered, and if so in which buffer mode)?

One other point, not directly related to the problem: There are a couple of cases where you are issuing an error message before you roll back or commit your transaction. One example is near the very end, where you have:

[tt]Messagebox(laerror[1,2])
Rollback[/tt]

In general, it's very bad practice to require any user interaction while a transaction is in progress, especially in a multi-user environment. While the transaction is in force, all the participating tables are completely locked. If your user spends several seconds - or minutes - studying your error message - or even goes to lunch while the error is being displayed - then you could be preventing all other users from accessing the system. The simple solution is to finish the transaction first and then display the message.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

select MAX(vou_no)as max_sno from cash_sumt where date=?thisform.text3.value

It seems you want a number sequence of voucher numbers per date.

Well, it's never a good idea to do that with MAX(), transactions are preventing parallel writes, but not parallel reads, so between this moment and the momnt you insert max_sno+1 multiple users can generate the same voucher number again.

An integer identity field is not capable to reset per day or any other group and indeed I also did such queries to get a sequence per project, but in my case there only was one user per project adding detail data to it, so I could do without a failsafe sequence mechanism.

You could do as Foxpro does in his sample implementation of a newid() stored procedure, which is in a sample database and was used by the VFP ffc framework (wizards) before we had autoincrementing integers. You'll have an extra table with just one record per date for a counter you update the record for a date read out the updated value so everyone doing the same gets a new next number, even if it takes some time before that new number is saved.

The main idea is to get the next number first and already commit that to the counter table even before using it in the main table it's made for. Then any time needed to make use of that voucher number is accaptable, even if a user in a concurrent session also updates the counter once more, he'll already get the next next numnber.

Bye, Olaf.