Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I generate running bill number?

Posted on 2010-08-16
10
Medium Priority
?
1,118 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

609 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