Please help with Datagrids and Access primarykeys problem!

[Lars Netzel]

Hello!

I have 4 tables where the first is parent of the second and second parent of
the the third and so on...

These I need to have as Datagrids loaded from access (one grid for each
table, not all tables in the same grid). Then I edit the values and then
save back to the Access Database, keeping the relations.

My problem is this:
If I load the dataset, all the tables will remember the primary keys from
what's set in Access and continue to increment from those values.. that
means if I in Table1 create a post (via the datagrid) it can get the value
10 in the PirmaryKey column.. and then if I remove it.. and create another
one it will become number 11.. which is all fine.. BUT then when I save this
to the database the database has no information of that there was a "created
and deleted value" and will create that post that has number 11 in the
dataset and give it number 10 in access... this means that all the
childrecord in Table2 will get the wrong parent ID...

How on earth do I do this???

Best regards
/Lars Netzel

 

[Cor Ligthert]

Lars,

Do you use autokeys?.

Cor

 

[Lars Netzel]

I use AutoIncrement in the Dataset it gets it from access automatically it
seems when I fill from the OledbAdapter!

If that is the same thing as autokeys I'm not sure...

 

[Cor Ligthert]

Lars,

Can you change that with a method of guid's or is it an existing database?

Cor

 

[Andy O'Neill]

Hello!

I have 4 tables where the first is parent of the second and second parent
of the the third and so on...

These I need to have as Datagrids loaded from access (one grid for each
table, not all tables in the same grid). Then I edit the values and then
save back to the Access Database, keeping the relations.

My problem is this:
If I load the dataset, all the tables will remember the primary keys from
what's set in Access and continue to increment from those values.. that
means if I in Table1 create a post (via the datagrid) it can get the value
10 in the PirmaryKey column.. and then if I remove it.. and create another
one it will become number 11.. which is all fine.. BUT then when I save
this to the database the database has no information of that there was a
"created and deleted value" and will create that post that has number 11
in the dataset and give it number 10 in access... this means that all the
childrecord in Table2 will get the wrong parent ID...

How on earth do I do this???

Best regards
/Lars Netzel

One simple way would be to just put the grids in separate screens.
Oh, obviously, make sure you just show one screen at a time.
Make the user add the main entry in one screen first, then the children
then the relationships.

The GUID thing would be neater but you've got to change the database
structure and maybe that's not a simple option if you have other code
already.

 

[Greg Burns]

The trick to this is to have your AutoIncrementSeed set to negative 1 (-1).

All your disconected additions will have negative values (no collissions)
with the records in Access. When you Update to the database, the Autonumber
feature of Access will create positive values. There is an even a way for
the child recrods to be updated locally with the new Access positive
autonumbers.

I've never tried it myself, but I've read about it many times. (GUIDs are
definately simpler, at least conceptually).

http://msdn.microsoft.com/msdnmag/issues/04/05/DataPoints/default.aspx

Greg