Solved

Need unique numbers for multiple users

Posted on 2012-04-09
7
257 Views
Last Modified: 2012-04-09
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.
0
Comment
Question by:Steve5140
  • 3
  • 2
  • 2
7 Comments
 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen earned 200 total points
ID: 37824884
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.
0
 

Author Comment

by:Steve5140
ID: 37824961
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

0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37825011
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

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Steve5140
ID: 37825303
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.
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 300 total points
ID: 37825359
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.
0
 

Author Comment

by:Steve5140
ID: 37825374
Ahh, thank you.

I'm going to split the point between both of you to thank you both for your help.
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 37826373
Thanks ;)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

18 Experts available now in Live!

Get 1:1 Help Now