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

Question

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'

Nitin Sontakke · 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 Sontakke · Answer

If possible please accept an appropriate answer.

Guruprasad Shreegiri · Answer

Okay. Thank you sir. Using 2008 version of SQL.

Guruprasad Shreegiri · Answer

how to write code to generate next id. mentioned in above question.

Abhishek Chadha · Answer

Please Specify your problem what you want ??