?
Solved

Need unique numbers for multiple users

Posted on 2012-04-09
7
Medium Priority
?
265 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 800 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 1200 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

770 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