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();
    }
}

I _think_ I get what you're asking, kinda. Use `SELECT IDENT_CURRENT(StudentDetails) + 1` rather than count, if you're trying to get the (potential) next increment value. I say potential because it could change before you commit. You'll also need an Identity column, otherwise use MAX(Id) if not IDENTITY (it should be). — Rudi Visser, Dec 07 '16 at 14:38
i just need auto increment value in text box how should i do?? — Dharmin Gheewala, Dec 07 '16 at 16:19

score 3 · Accepted Answer · answered Dec 07 '16 at 14:40

3

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.

answered Dec 07 '16 at 14:40

Carra

17,808
7
62
75

It gives error that invalid object name 'Std_Info.Id' – Dharmin Gheewala Dec 07 '16 at 16:22

autogenerate number from database in c#

1 Answers1