multi Bill No In 1 table 1

[bros]

My program is running in multi-user enviroment, when user open the Billing Entry at the same time, they get the same Bill No and saved it.
How do I to prevent this error occur, cause the Bill No is unique. I try to use LockEdit command but it not work properly.
Can I Lock the record when someone is updating the record?

Help.....!
bros

 

[Digimon02]

One solution is to create a table for Bill No. where all the generated bill no is stored. then when the user request for a Bill No just get the maximum bill No +1.

Another solution is just give the Bill No. when the user save the transaction. Get the maximum Bill No + 1.

 

[PraveenMenon]

Well this is quite a bit of a problem bros.. One solution that i have successfully implemented is to create a seperate table, with a single field denoting each table u want to lock, and set the bit/boolean value, just before the DML. After the DML has successfully executed, set it back to normal. Also, check the status of the table each time u issue the DML, and make it loop, until the status is free.

An example will be like this..

u have tables A and B, in which you dont want the id to repeat, or u generate the primary key id, in a database of say 10 tables.

create a table t_Status with two fields
A_Status Boolean/Bit
B_Status Boolean/Bit

Default values will be False/0

Now, in the code, before u issue a DML to table A, just check the value of A_Status. If it is true, Dont issue the DML, but Loop through, until this becomes Flase. You should also set a time out, based on the loop count or the time elapsed. When the status is false, set the value to true,issue the DML and set it back to false... All the Best
Praveen Menon
pcmin@rediffmail.com

 

[johnwm]

1. Only open the recordset while you are actually updating
2. Use pessimistic locking
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[bros]

Thank for reply.
Digimond02, I can get the number running, just sometime 2 user get the same Ref #

 

[CCLINT]

Using Digimon02's solution, you would need to immediately update the BillingNumber table with a new value, so that this number is not used anymore (also, if the user decides not to process the order, the number still cannot be used again...unless you look additionally for the missed order numbers).

In any case, you would still need to set the field as a unique key field in order to prevent duplicates.

You would therefore, need to catch the error, if the number is already in use, and loop again for another try.

Do
on error resume next
'Select statment here to get the last Bill number
'Insert statement here, or add new method.
' a simple error catcher - actually, you would check for duplications error number and any other errors. Any other errors need to be handled seperately
If err=0 then exit Do
Loop [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[Digimon02]

if the user decided to cancel the process then delete that bill no.

 

[bros]

thanks for help.

 

[SemperFiDownUnda]

I'm not a fan of implimenting locking mechanisms via code because, sinse they are via code, they can mistakenly be circumvented. Research the locking features of what ever underlying DB you are using try to use them (yes it is possible to circumvent these in a way to) but I guess I also don't believe in reinventing the wheel 8)

 

[CajunCenturion]

Please be aware that PraveenMenon's proposal will not solve the problem, it only decreases the liklihood of the problem happening. Just as the concurrency problem exists on the main table, the concurrency control problem has been relegated to the lock table, and given the following sequence, both users wanting to lock table A

User 1: Check Status of Table A and its False
User 2: Check Status of Table A and its False
Then User 1 sets the status to True, followed by User 2 setting the status to True. But both users have not gotten passed the lock and are in the main table and the potential for the same error condition exists.

This proposal also suffers in that table locking is required, rather than record locking which significantly bogs down production because the result is not preventing two users from updating the same record at the same time, but now preventing two users from updating the same table at the same time. Why should user 1 be prevented from updating record 10 at the same time that user 2 is updating record 20 in the same table?

I agree with johnwm - Use the pessimistic locking feature of the RecordSet. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein