Improve company productivity with a Business Account.Sign Up

x
?
Solved

How do I generate running bill number?

Posted on 2010-08-16
10
Medium Priority
?
1,126 Views
Last Modified: 2012-05-10
Hi All,

Below is my scenario.

I am working for Point Of Sale application. Front end is VB6, back end is SQL Server 2005. My application has an interface to generate cash sale bills.  Each sale should be saved with a running cash bill number. For example, 2010/POS/1, 2010/POS/2, 2010/POS/3, 2010/POS/4, and so on. Here, the prefix 2010/POS is constant. It is taken from a table named Prefix. I have to take this prefix and append a running number like 1, 2, 3, 4, and so on.

My problem is how to compute this running number since my server is simultaneously accessed by around 100 sale counters. My idea is to save the Last Bill Number in a table, and to fetch it the next time a bill is raised. Since more client machines fetches this number almost same time, duplicate bill numbers will be computed. How could i lock other machines when one machine processes this value.  I also fear that this lock should not introduce more delay to save the bills to server.

Please guide me how to generate unique bill number.

thanks in advance.
Regin
0
Comment
Question by:Fi-es
  • 5
  • 4
10 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33451777
Is it ok to have gaps in the bill number?  If it is, there is a simple solution.
0
 
LVL 22

Expert Comment

by:Om Prakash
ID: 33451778
If you are not displaying the running number then saving the data should not be a problem. If the running number is displayed then there might be an issue.

If you want to display the running number then before displaying the data you get the sequence number from table and use this to update when actual data is saved.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 33451785
A very easy solution like you posted in the question is to store it in a table, say

LastBillNumber - LastUsed (int)

to get the next number, use this

declare @next int
update LastBillNumber set @next = LastUsed = LastUsed + 1
select @next

The reason for the double-assignment is to guarantee atomic access.  It first updates the value of LastUsed, and at the same time returns what it was UPDATED TO.  This type of query is guaranteed not to duplicate, even without using transactions.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33451818
Bear in mind that this type of update is actually transaction aware and if your transactions are large, the lock on the sequence could be longer than you desire.

The alternate out-of-transaction way to get a sequence number without involving any locks is to create a table with an identity column and insert dummies into the table with cleanup.  IDENTITY sequences are special in that they cannot be rolled back and are therefore not subject to any transaction lockup/bottleneck chain.
--- create a sequence table, start it off with your current number, here it is 9876
create table billnumberseq (i int identity primary key clustered, bdummy bit)

-- create a proc to populate the table
create proc usp_getnextbillnumber
as
insert billnumberseq (bdummy) values (null);
select scope_identity();
delete billnumberseq with (readpast);
GO

Open in new window

0
 

Author Comment

by:Fi-es
ID: 33451840
Thank you cyberkiwi and om_prakash_p.

Below are the answers to your questions:

My running number should not have gaps. It should be a serial number.

Running number is not displayed when creating new bill, it is generated only after the save button is hit. Running bill number is displayed when the bill is edited.

cyberkiwi's solution sounds good for me. double-assignment technique is really great. Consider a scenario. LastUsed is 10 now. Counter1 increments it to 11, but not yet committed. Counter2 increments it again to 12, and commits. Now counter1 rolls back. I hope that BillNumber 11 will be missed since the respective trans was rolled back. If i am right, how to solve this issue?

Thanks in advance.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33451857
FWIW, you won't be able to get to 12 without first committing the 11, because the lock is still there (read comment about being transaction aware).  So in a sense, it will either roll back to 10 or be committed - no gaps should be possible.
0
 

Author Comment

by:Fi-es
ID: 33452010
Hi cyberkiwi,

Thank you for your help.

Can you find time to explain below lines of your proc?

select scope_identity();
delete billnumberseq with (readpast);
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33452045
That approach won't work for you because it can leave gaps.

scope_identity() returns the last IDENTITY value generated by the current connection.

delete [from] tbl -- this is just a normal delete
with (readpast) is a hint to say do what you can and what is locked, ignore and move on
0
 

Author Comment

by:Fi-es
ID: 33452129
Thank you cyberkiwi.

I understood.

So do you advise me to use the attached solution given by you?

declare @next int
update LastBillNumber set @next = LastUsed = LastUsed + 1
select @next

Open in new window

0
 

Author Closing Comment

by:Fi-es
ID: 33452208
Thank you cyberkiwi
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question