August 3, 2004 at 4:02 am
Good Morning everyone,
I have a Program made on SQL Server .. and I am facing a problem..
there are many departments that Add any new customer .. and they want to see his number in front of them as they typing his information...
They problem is .. if they cancel the Add then the number will be lost in the Middle ): .. so how can i keep the Primary key of CustomerID in order without any lost?
Many thanks to all who respond.
Alamir Mohamed
Alamir_mohamed@yahoo.com
August 4, 2004 at 6:52 am
Alamir,
How are you getting this customer number before the addition of the record?
Are the different departments entering customers at the same time, or close to the same time? If there is a low probability of the records being created close to one another then I would just do the following statement to return the next customer ID.
Select MAX(CustomerID) + 1 AS 'NextCustomerID' From Customer
The other option will be to use a Transaction block so that the number isn't lost if the addition is cancelled.
Kris
August 4, 2004 at 7:26 am
yes, the different departments are entering customers at the same time ..
and i want them to see Customer Number without no change in the future ..
Alamir Mohamed
Alamir_mohamed@yahoo.com
August 4, 2004 at 7:41 am
Alamir,
How are you currently getting the customer number that is being displayed?
My first thought is to use a transaction block so you could rollback on a cancel but that might not work well depending on how you are getting the customer number.
Kris
August 4, 2004 at 8:00 am
Currently I getting the number in the End of adding Customer (by making transaction and get Max+1 )
are there any other solution ?
Alamir Mohamed
Alamir_mohamed@yahoo.com
August 4, 2004 at 8:31 am
Alamir,
Here is solution you could use.
Your table will need a field that can be used to determine the status of the record. Example: RecordStatus as TinyInt; 0-Unused, 1-Active, 2-Inactive..... default to the column to zero.
When the user is going to create a new record the first thing to do would to be to see if any records exist that have a RecordStatus of zero, and if any exist then return the number of the Min(CustomerNumber). If no unused record is found then create a new customer record retrieving the customer number so that it can be displayed. Then on the save of the customer information just update the existing record. This will give you what you want in not losing any of the customer numbers.
I do not understand why losing a number in the sequence is a big deal, but I am guessing that someone wanted you to do it that way.
I hope this is of some help.
Kris
August 4, 2004 at 8:47 am
Let me preface this by saying that generally it is bad practice to allow the client code to determine the CustomerID. This is precisely why SQL Server (and every other modern RDBMS) provides us w/ the IDENTITY data type. However, occasionally (which sounds like the case here) business needs dictate this sort of need.
Have you considered storing the max Customer ID as an INT value in a separate single column single row table? That way, you could just have the code select the value + 1 and increment the value in the table (all w/in a transaction). You run the risk that some CustomerIDs would never get used (suppose you query the max value, increment it and the Customer INSERT fails for some reason), but that shouldn’t be a big deal. Still place a PK on the CustomerID in the base table, that way if the MaxID value ever gets reset, you don’t run the possibility of dup CustomerIDs.
Also, be cognizant of the difference between CustomerID and CustomerNumber. TableID fields should be internal to the system, and therefore never change or be seen by the user. CustomerNumber, AccountNumber, etc. is the “ID” that the user should be concerned with.
Corie Curcillo
MCT, MCDBA, MCSD
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply