Solved

Need unique numbers for multiple users

Posted on 2012-04-09
7
261 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
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 38

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 37826373
Thanks ;)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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 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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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