Sequence is not consecutive

Question

From my previous question I have solved how to display information from MS Access DB file. The problem is that the sequence number is messing up after I have deleted some entries which I used as sample tries, and now the ID of items are out of order. Here is what it looks now, and following by code I have.

=== Magazine 1 ===
People
Times Inc.
4.95
19.95

=== Magazine 2 ===
Car and Driver
Hachetter Inc.
3.95
19.99

=== Magazine 7 ===
a
b
1
2

Button event (I doubt it's the codes fault):

    private void btnShowMags_Click(object sender, EventArgs e)
    {
        // Creating new instance of the DisplayMags form.
        DisplayMags displayMags = new DisplayMags();

        // find the path where the executable resides
        string dbPath = Application.StartupPath;

        // Providing a path to the MS Access file.
        string connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="
            + dbPath + @"\..\..\..\..\Magazines.mdb; User Id=admin; Password=";

        // Creating a new connection and assigning it to a variable.
        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = connString;

        // Creating a new instance for a command which we will use later.
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;

        // declare and instantiate the command
        OleDbCommand cmdMagazines = new OleDbCommand();
        cmdMagazines.CommandText = "select * from magazine";
        cmdMagazines.Connection = conn;

        OleDbDataReader drMagazines;

        try
        {
            // open the connection
            conn.Open();

            // retrieve data from the data source to the data reader
            drMagazines = cmdMagazines.ExecuteReader();

            if (drMagazines.HasRows)
            {
                while (drMagazines.Read())
                {
                    displayMags.txtDisplayMags.Text += "=== Magazine " + 
                        drMagazines.GetValue(0) + " ===" + Environment.NewLine + 
                        drMagazines.GetValue(1) + Environment.NewLine +
                        drMagazines.GetValue(2) + Environment.NewLine +
                        drMagazines.GetValue(3) + Environment.NewLine +
                        drMagazines.GetValue(4) + Environment.NewLine + 
                        Environment.NewLine;
                }
            }
        }
        catch (Exception ex)
        {
            // Displaying any errors that might have occured.
            MessageBox.Show("Error opening the connection: " + ex.Message);
        }
        finally
        {
            // Closing connection after task was completed.
            conn.Close();
        }

        // Displaying DisplayMags form, assuring that earlier form
        // will not be accessible. Show() let us access all forms.
        displayMags.ShowDialog();
    }

How can I make the sequence number to appear in order?

EDIT I will count all entries this way:

        try
        {
            // open the connection
            conn.Open();

            // retrieve data from the data source to the data reader
            drMagazines = cmdMagazines.ExecuteReader();

            int i = 0;

            if (drMagazines.HasRows)
            {
                while (drMagazines.Read())
                {
                    i++;

                    displayMags.txtDisplayMags.Text += "=== Magazine " + 
                        i + " ===" + Environment.NewLine + 
                        drMagazines.GetValue(1) + Environment.NewLine +
                        drMagazines.GetValue(2) + Environment.NewLine +
                        drMagazines.GetValue(3) + " / issue" + Environment.NewLine +
                        drMagazines.GetValue(4) + " / year" + Environment.NewLine + 
                        Environment.NewLine;
                }
            }
        }

Do you mean by "out of order" that the IDs aren't ordered (ie. 1, 3, 2) or that there are IDs missing in between (ie. 1, 4, 7)? — chrischu, Nov 19 '11 at 10:02

score 2 · Accepted Answer · answered Nov 19 '11 at 10:00

2

They are not "sequence numbers" but "primary keys".

Keys should never change so Yes, deleting rows will produce gaps. There is no built-in mechanism to renumber.

answered Nov 19 '11 at 10:00

H H

263,252
30
330
514

So, nothing I can do about it? BTW! You think this code is sufficient? – HelpNeeder Nov 19 '11 at 10:01
You can add an extra column and use either C# or SQL to renumber. But don't mess with the PK. It's customary not to show the PK. – H H Nov 19 '11 at 10:04
Your `displayMags.txtDisplayMags.Text += ... ` will become (very) inefficient for large numbers of records. Look into StringBuilder. – H H Nov 19 '11 at 10:06
Well, yes I do know. But this is enough to get an A of the assignment :) PS. (my edit) this count of books will do much better work of counting my entries, right? – HelpNeeder Nov 19 '11 at 10:11
1

@HelpNeeder There is something you can do about it: Compact and repair your database every time you delete a record. This resets the autonumbering (or at least used to in Access 2003 and earlier). This won't fill gaps that already exist, but it will prevent new gaps at the end of the sequence. For example, with items 1, 2, and 7, create item 8, and delete it. Compact and repair before creating a new item, the new item will also be 8; otherwise it will be 9. Beware, however, that this can cause confusion or other problems if the first item 8 was around long enough to leave traces anywhere. – phoog Nov 22 '11 at 00:16

Chris Gessler · Answer 2 · 2011-11-19T10:04:26.713

1

Change your SQL from:

cmdMagazines.CommandText = "select * from magazine";

To:

cmdMagazines.CommandText = "select * from magazine order by ID";

edited Nov 19 '11 at 10:04

answered Nov 19 '11 at 09:56

Chris Gessler

22,727
7
57
83

Well, I do not retrieve data using any SQL query. I just use DataReader. – HelpNeeder Nov 19 '11 at 10:00
@HelpNeeder: You can only get a DataReader by executing a query. – H H Nov 19 '11 at 10:01
Oh, OK, well I think the problem does lay that the numbers are skipped because I have deleted some entries. I guess I can't do nothing about this. – HelpNeeder Nov 19 '11 at 10:07
A primary key is often used as a row identifier. If you changed the row's identifier with a select statement, you would not be able to find the row later in an update or delete statement. If you simply want to show a sequence of numbers based upon the number of rows selected, use SQL's ROW_NUMBER() function and maintain the primary key ID as a hidden reference back to the original table row. – Chris Gessler Nov 19 '11 at 10:14
Oops... scratch that. You're using Access DB. ROW_NUMBER() won't be available. – Chris Gessler Nov 19 '11 at 10:27

Sequence is not consecutive

2 Answers2