Need a Virgil to lead me through Access autonumber hell...

  • Thread starter Thread starter Timo
  • Start date Start date
T

Timo

I have followed carefully the advice given in this forum on how to
handle autonumber primary keys in Access, setting AutoIncrement
properties on the column and negative seed number and negative
increment, as well as backfilling the pkcol in the onRowUpdated event.
But I keep getting an error that the value already exists; the weird
thing is, the value that already exists happens to be the PK value of
the PENULTIMATE row in the table, not counting the one I am trying to
add:

I'm invoking the Addnew method via the BindingManager.AddNew method.
and the BindingManager is instantiated like this:

MyBindingManager = myEditControl.BindingContext(MyDataView)


Looking at the table in Access datasheet mode, I see

ID
1000
..
.. many other rows here
..
3231 <-- penultimate row
3232

***************
Error message:
Column 'ID' is constrained to be unique. Value '3231' already exists.
*******************************************************************


I don't understand what is going on. I'm using the DatAdapter
(Table).Update method. Here is how the adapter is instantiated:

AnAdapter = New System.Data.OleDb.OleDbDataAdapter(Table, Connection)
AnAdapter.FillSchema(MyDataset, SchemaType.Source, Tablename)
AnAdapter.Fill(MyDataset, Tablename)


Is the SchemaType an issue?

en una selva oscura,
Timo
 
Thank you, but these guidelines laid out in the article are the very
ones I have been following. I have set AutoIncrementSeed and
AutoIncrementStep both to NEGATIVE 1, and am backfilling the actual
autonumber assigned by the database (Access) in the onRowUpdated event
where I'm calling AcceptChanges --- but my IDs are still colliding; in
the debugger the autoIncrementCurrent value is a large POSITIVE number
-- AS THOUGH THE NEGATIVE INCREMENT WERE NOT WORKING in ADO.NET.

The last ID in my table in the Access database is 3232.
The autoIncrementCurrent is 3230.
The error I get when adding a new row is that value '3231' already
exists.
It looks as though ADO.NET is starting at -1 and adding POSITIVE
numbers.

Timo
 
Timo,
Make sure you do the following in order when loading data
1) FillSchema from the database to the appropriate table
2) Set the AutoIncrement values for the column (seed, step, ...)
3) Fill the DataTable
otherwise if you fill the data table before setting the autoincrement values
the current autoincrement will be set from the database vs your values.

Ron Allen
Timo said:
Thank you, but these guidelines laid out in the article are the very
ones I have been following. I have set AutoIncrementSeed and
AutoIncrementStep both to NEGATIVE 1, and am backfilling the actual
autonumber assigned by the database (Access) in the onRowUpdated event
where I'm calling AcceptChanges --- but my IDs are still colliding; in
the debugger the autoIncrementCurrent value is a large POSITIVE number
-- AS THOUGH THE NEGATIVE INCREMENT WERE NOT WORKING in ADO.NET.

The last ID in my table in the Access database is 3232.
The autoIncrementCurrent is 3230.
The error I get when adding a new row is that value '3231' already
exists.
It looks as though ADO.NET is starting at -1 and adding POSITIVE
numbers.

Timo
Click to expand...
http://msdn.microsoft.com/library/en-us/dnadonet/html/manidcrisis.asp?frame=true
 
I let my business layer assign a temporary negative incrementing value to
the pk, and then follow the same update procedure you are following. This
way I am controlling the auto increment and know for sure it will always
work. You can see how I accomplish this by running my code generator using
the latest template on the open source site in my signature.

(BLL)
public class Employee
{
private static int _created;
private int pkField;
private Employee()
{
pkField = 0 - _created - 1;
// ... init other fields ...
_created++;
}
// ... etc ...
}

These PK values will never duplicate in a single application session.
 
Back
Top