Generate next ID's with prefix 'AE' to label C# to sql

Guruprasad Shreegiri

If I delete in between one record in master table, next auto generate id gives duplicate value

how to solve this

string empid = "AE000";

con.Open();

SqlCommand cmd = new SqlCommand("select Count(REGISTRATION_ID) from CANDIDATE_MASTER", con);

int i = Convert.ToInt32(cmd.ExecuteScalar());

con.Close();

i++;

Label1.Text = empid + i.ToString();

REGISTRATION_ID

AE0002

AE0004

AE0005

AE0006

If i execute that code ,label will get the value like 'AE0005'

i want to generate next id like 'AE0007'

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
Accepted answer
What is the version of SQL Server you are using? If you are using 2012 and above, you can make use of "Sequence" object. That would be truly helpful for you. Google "Sequence in SQL Server" for details.
If you are using 2008 and below, you can make use of Identity column feature. Create a separate table for your purpose with identity column. Insert a record in it and just use it's newly generated identity value.
The third option is to create one separate table for this purpose with integer column and pick value from that column, increment it by one, save it in that table and return the same value. Write a stored procedure for this purpose.
If you are having too many tables to this (10 or more), in above suggested table keep row for each table.
If you are confused by reading all this, please do let me know.
Nitin Sontakke10y ago
If possible, please accept an appropriate answer.
Guruprasad Shreegiri10y ago
Okay. Thank you sir. Using 2008 version of SQL.
Guruprasad Shreegiri10y ago
how to write code to generate next id. mentioned in above question.
Abhishek Chadha10y ago
Please Specify your problem what you want ??