Rearrange the autoincrement column value after deleting some rows

Question

The case that a column such as the ID column on a table will auto increment. This simply means that the next insert into the table will have an ID that is one more then the previous one and therefore all ID's will be unique.

However, if you delete a row from the table, the table will auto increment as if the row had not been deleted at all. The result is a gap in the sequence of numbers. This is normally not that much of an issue, but you may want to reset the auto increment field.

PatientNo is primary key and here is the code what I have tried so far

    private void txtincr()
    {
        int a;
        if (textPatientNo.Text == "")
        {
            if (con.State == 0)
            {
                con.Open();
            }
            string Query= "select PatientNo from PatientRegistration";
            SQLiteDataAdapter DataAdapter = new SQLiteDataAdapter(Query,con);
            DataSet PatientDataSet = new DataSet();
            DataAdapter.Fill(PatientDataSet);
            if (PatientDataSet.Tables[0].Rows.Count != 0)
            {
                a = PatientDataSet.Tables[0].Rows.Count;
                a = a + 1;
                textPatientNo.Text = Convert.ToString(a);
                textPatientName.Focus();
            }
            else
            {
                textPatientNo.Text = "1";
                textPatientName.Focus();
            }
            con.Close();
        }
    }

Am I the only one who couldn't get the question? Please explain a little more - when and where you want to reset — shikjohari, Feb 06 '15 at 08:22

score 0 · Answer 1 · answered Feb 06 '15 at 08:24

0

You can try using the SQL Query

    DBCC CHECKIDENT('TABLENAME',RESEED,initialvalue);

answered Feb 06 '15 at 08:24

Manu Nair

314
2
7

This question is about SQLite. – CL. Feb 06 '15 at 09:24

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

When you have declared the column as INTEGER PRIMARY KEY, you don't need to do anything because the database automatically uses the next value after the largest value in the table.

When you have declared the column as INTEGER PRIMARY KEY AUTOINCREMENT, the current counter is stored in the sqlite_sequence table. You can simply modify it by updating that table.

edited May 23 '17 at 11:50

Community

1
1

answered Feb 06 '15 at 09:28

CL.

173,858
17
217
259

Rearrange the autoincrement column value after deleting some rows

2 Answers2