Solved

How do I generate running bill number?

Posted on 2010-08-16
10
1,051 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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you cyberkiwi
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now