autogenerate number from database in c#

Question

I used the solution below, but whenever I delete a particular row it will duplicate the number.

Code:

void AutoGenerateId()
{
    SqlConnection cn = new SqlConnection(ConString);
    string Query = "select COUNT(*) from StudentDetails";
    SqlCommand cd = new SqlCommand(Query, cn);
    try
    {
        cn.Open();
        int count = Convert.ToInt16(cd.ExecuteScalar()) + 1;
        txtStudentId.Text = count.ToString();
        txtStudentId.Enabled = false;
    }
    catch (Exception ex)
    {
        lblErrorMsg.Text = ex.ToString();
    }
    finally
    {
        cn.Close();
    }
}

Answer 1

You can use a sequencer to generate ids and get the next one.

Example:

string query = "SELECT NEXT VALUE FOR Student.Sequence;" 

This will return a unique number, even if you remove a row.