Makishima Makishima - 1 month ago 10
ASP.NET (C#) Question

SQL: How to Auto-increment a value in the database

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

Answer

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:

  1. Computed Field based on the auto increment ID
  2. Trigger that fires on insert and set the CustomerID
  3. Custom Code in your back-end, add the customer object, retrieve the primary key and set the 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.

Comments