C# DataTable: Add new row throws error using AutoInc field

Question

I have a collection

List<Employee> employees;

I use a DataTable, and load all the records (from MDB table) when Form1.Loads, and add these records to the List (Collection) so I work with them in memory.

Now, when I add a new employee to the collection, I should add it also to the mdb table... so I do:

DataRow rowemployee = Program.tblEmployee.NewRow();
rowemployee["name"] = tb_Name.Text;
rowemployee["address"] = tb_Address.Text;
//...all the other fields
Program.tblEmployee.AddRow(rowemployee);

The IDE throws an error regarding "id" column must not have a null value or must not be empty.
As the mdb table has an id column, type number, autoinc, how should I fix the Employee class, and the DataTable/Collections methods, so the id column is "ignored" when adding new rows?

Thanks

Answer 1

How have you set up the table?

This code is from the DataRow.ItemArray Property MSDN page which illustrates how the AutoIncrement property is used.

private DataTable MakeTableWithAutoIncrement()
{
    // Make a table with one AutoIncrement column.
    DataTable table = new DataTable("table");
    DataColumn idColumn = new DataColumn("id", 
        Type.GetType("System.Int32"));
    idColumn.AutoIncrement = true;
    idColumn.AutoIncrementSeed = 10;
    table.Columns.Add(idColumn);

    DataColumn firstNameColumn = new DataColumn("Item", 
        Type.GetType("System.String"));
    table.Columns.Add(firstNameColumn);
    return table;
}

Answer 2

The first problem is that while the id column is auto-incremented in your database, the DataColumn object corresponding to it in your DataTable isn't set up to auto-increment. You need to set its AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties.

Once you do this, you'll run into the second problem, which is that you have two different auto-increment seeds: the one in your database and the one in your DataTable. Even if those two seeds start out at the same value, it's trivial for them to get out of sync: add a row to your DataTable and a different row to your database table, and now you have two different rows with the same ID.

This is a common problem, and its common solution is to have a different numbering sequence for rows that are added to the DataTable. Commonly this is done by setting the seed to 0 and the step to -1, so that all rows that you add to the DataTable have IDs that are less than 0. (This of course assumes that all IDs in your database are greater than 0.) This means that there's no chance of an ID collision between rows added in the database and rows added to the DataTable.

Then, when you actually update the database from the DataTable, after you insert the row into the database and its real ID gets assigned, you change the ID in the DataRow to the correct value. If the row is participating in data relations as a parent, the DataColumn has to have cascading updates set, so that changing the ID in the parent row from its temporary local value to its permanent value also changes the IDs in the related child rows.

One of the many reasons to use typed data sets and table adapters is that all of this work is done for you automatically. But if you're not using table adapters, you'll have to do it yourself. There's a pretty good example of this in the ADO documentation