Solved

Need unique numbers for multiple users

Posted on 2012-04-09
7
259 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Round up to 100% in .NET 10 80
Connect to a database from Excel using JDBC instead of ODBC 3 47
Change Format on Data when exporting to Excel 4 22
Sql query 107 22
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 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

12 Experts available now in Live!

Get 1:1 Help Now