Need unique numbers for multiple users

Hello Experts,

I have written a VB .net app (Windows)  using an SQL server back end that service technicians use to log service calls from customers.  Everything is working except one point I hope you can help me with.

When a Tech answers a call he wants to open a screen that has a unique number we will call "Case number".  The tech will tell the customer "Your case number is..." The tech may spend a few seconds, or an hour on the call.

When he is done he will click a button to save all the data filled out on the screen.

Since many techs will use this at the same time, it is very probable that another other techs will be doing the same thing at the same time, and the case numbers must be accurate.

My first inclination is to creat an identity column in the table and use that number as the case number, however the only way I know to "hold" that number is to insert a blank record in the table.  That's OK, but if a customer hangs up, or the tech opens the window by mistake my table will be littered with blank records.

Before I embark on that route is there some slick built in function or trick that you can recommend to accomplish my task ?

Thanks.
Steve5140Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gerwin Jansen, EE MVETopic Advisor Commented:
How about selecting the case number on closure of the screen? As in: when the tech completes the call and presses 'save', the application gets the case number, shows it to the tech and saves the call with the number. When the tech does not press save, no number is generated and no record is saved.
Steve5140Author Commented:
Not a bad suggestion, I had tought of that myself, but the tech wants to be able to give the customer a case number at the beginning of the call.

I did some reading and discovered the @@Identity function in SQL Server

So I wrote this, which seems to work.

This code checks to see if there are any blank records from the user, and if so uses it.  If there are no blank numbers a new record is inserted.

This might work well.

IF EXISTS (SELECT 1 FROM ServiceCalls WHERE EnteredBy = 'UserName' AND TDate IS NULL) 
Begin
  SELECT MAX(ID) FROM ServiceCalls WHERE EnteredBy = 'UserName' AND TDate IS NULL
  end
ELSE
Begin
  Insert INTO ServiceCalls (EnteredBy) VALUES('UserName')
  SELECT @@Identity
End

Open in new window

wdosanjosCommented:
Your latest code could give two different Operators the same case number under certain scenarios.  You should consider keeping track of the latest Case Number on a different table.  Something like this:

Table definition
create table CaseNumber (LastNumber int not null)
insert into CaseNumber values(1)

Open in new window


Get Case Number
Begin Transaction
Update CaseNumber
   Set LastNumber = LastNumber + 1
Select LastNumber From CaseNumber
Commit Transaction

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Steve5140Author Commented:
wdosanjos,

Please explain, my understanding of the @@Identity function was that it returned the last ID number of the specific connection.

Therefore if two people were entering numbers, @@Identity would keep track of their ID numbers separately.

Am I wrong about this ?

Additionally, my query selects based on the username and ID number.

It's not that I don't believe you, it's just that I don't understand.
wdosanjosCommented:
Your understanding of the @@Identity is correct and your code should work.  I didn't notice that your code selects MAX(ID) by operator, thus preventing two different operators from pulling the same number.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve5140Author Commented:
Ahh, thank you.

I'm going to split the point between both of you to thank you both for your help.
Gerwin Jansen, EE MVETopic Advisor Commented:
Thanks ;)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.