Auto Generate Customer ID

Question

Hi I m creating one small application in asp.net for my practice. Let take one example customer registering information in the website like firstname lastnamedobetc. after metion the personal details customer need to generate his/her ID by clicking on button. Customer id format 2aplha & 4numeric  system should automatically want to generate. example: P raveen B ahubali is my name for this customer id should come PB0886 (0886 is my dob mm/yy) Can any one help this logic. Thanks Praveen Bahubali

Zoran Horvat · Answer

Praveen You can create MSSQL database with two tables and a stored procedure like the one created with this script: CREATE DATABASE UsersTest; GO USE UsersTest; GO CREATE TABLE UserSequence ( LastStrongUserID INT NOT NULL ); CREATE TABLE UserDetails ( UserID INT IDENTITY(1 1) PRIMARY KEY StrongUserID INT NOT NULL FirstName VARCHAR(100) NOT NULL LastName VARCHAR(100) NOT NULL DOB DATE NOT NULL ); GO CREATE PROCEDURE InsertUser( @firstName VARCHAR(100) @lastName VARCHAR(100) @dob DATE ) AS BEGIN DECLARE @strongUserID INT; SELECT @strongUserID=MAX(LastStrongUserID)+1 FROM UserSequence; IF (@strongUserID IS NULL) BEGIN SET @strongUserID=1 INSERT INTO UserSequence(LastStrongUserID) VALUES (1) END ELSE UPDATE UserSequence SET LastStrongUserID=@strongUserID INSERT INTO UserDetails(StrongUserID FirstName LastName DOB) VALUES (@strongUserID @firstName @lastName @dob); RETURN @strongUserID END; GO Here you have one table named UserSequence which contains only one record remembering last issued UserID. Another table named UserDetails contains users data which includes column named StrongUserID. Whenever a record is inserted into UserDetails table value in the UserSequence table is increased by one. That operation guarantees that every user will have strictly unique strong identity. Note that simple autoincrement value cannot be directly used because it is affected by replication database restoration etc. Instead UserID column is only used as local identity and strong identity is maintained manually. Note also that you can't assign strong identity as by one larger than current maximum in the UserDetails table because users records might be deleted in the future which would lead to issuing the same strong ID twice. To test this procedure I have changed UserID autoincrement to start from 9 and step by 14 just to make impression that database was used for longer time. Then I have made total of 8 calls like this: EXEC InsertUser 'Praveen' 'Bahubali' {d '1986-08-14'}; Resulting contents of the UserDetails table is this: UserID StrongUserID FirstName LastName DOB 9 1 Praveen Bahubali 1986-08-14 23 2 Praveen Bahubali 1986-08-14 37 3 Praveen Bahubali 1986-08-14 51 4 Praveen Bahubali 1986-08-14 65 5 Praveen Bahubali 1986-08-14 79 6 Praveen Bahubali 1986-08-14 93 7 Praveen Bahubali 1986-08-14 107 8 Praveen Bahubali 1986-08-14 As you can see every user has got unique StrongUserID value. Even if DELETE is issued later StrongUserID will continue to count forward: DECLARE @newUserID INT DELETE FROM UserDetails EXEC @newUserID = InsertUser 'Praveen' 'Bahubali' {d '1986-08-14'}; PRINT @newUserID This prints value 9 and contents of UserDetails table is now: UserID StrongUserID FirstName LastName DOB 121 9 Praveen Bahubali 1986-08-14 You can apply similar solutions in other databases. Zoran

praveen bahubali · Answer

Hi Zoran Can you give one example for auto increment how it works for this logic and how will return back id as a format

Jiteendra Sampathirao · Answer

Hi I have a name like Prashant Bishra and moth and year is 08-1986 then It will generate PB0886 as Praveen Bahubali is my name for this customer id should come PB0886 (0886 is my dob mm/yy) so be careful when working on this type of practices...

Abhimanyu K Vatsa · Answer

this will not be successful if you have huge list of data because only by first letters of each word + dob can't be unique for always. keep this point in mind. however if you wish to do so then implement a logic in such a way that finds the first letter of each word and concatenate this with dob.

Zoran Horvat · Answer

If you're using database then attach autoincrement ID to a customer and give that ID back. If you're not using database then you can use ASP.NET session ID like this: Session.SessionID. It is a string and it is currently unique on your Web site at any given time. Zoran