Reputation: 125
i am creating a custom CustomerID by getting the first double characters of first name and last name taken from textboxes as following:
string CustomID = (FirstNameTxtbox.Value.Substring(0, 2) + LastNametxtbox.Value.Substring(0, 2));
in order to avoid a Duplicated CustomerID i am trying to:
get the 5th value which is the int since the first 4 values are characters. and then increase it by 1.
my attempt:
string s = cmd.CommandText= " Select MAX(CustomerID) from Customers";
string CustomID = (FirstNameTxtbox.Value.Substring(0, 2) + LastNametxtbox.Value.Substring(0, 2) + (Convert.ToInt32(s)+1)) ;
example:
First name= Mak
last name = shima
CustomerID = MASH1
next value will be e.g KAAR2
Upvotes: 0
Views: 1524
Reputation: 4456
Make the primary key an auto increment integer and the CustomerID
is a string that is computed based on the ID
, this way it will never duplicate.
You can set the CustomerID
by:
ID
CustomerID
CustomerID
Your CustomerID
can be something like 0000001
.
ex: First customer will have a primary key of 1, the CustomerID
will be 000001
, next customer will be 000002
and so on.
Upvotes: 1
Reputation: 1349
Yes Primary key (id) is necessary but Customer ID can be Custom String like 'anything'.
I you want to generate Customer ID like this
*First name= Mak
last name = shima
CustomerID = MASH1
next value will be e.g KAAR2*
Use front end to generate KA(first)AR(last)+Sequence in Sql Server /Oracle.
CREATE SEQUENCE [dbo].[CustomerId_Generation]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE -9223372036854775808
MAXVALUE 9223372036854775807
CACHE
GO
Get value of sequence and Add to database afterward.
There was a case that Same CutomerID for returning customer than i mean repetitive same CutomerID can also be handled by your code if you follow this approach.
Upvotes: 0
Reputation: 1061
Your practice is not recommended, you should design a primary key with Auto Increment small value like integer, remember that the table is physically sorted on the clustered index (your primary key), and your non-clustered indexes are keyed based on your primary key.
You can add your combined value in another field if you still need it, and if your queries are based on it, create a non clustered index for it.
Upvotes: 2