Autoincrement / Rowstate Problem

[Guest]

Hi,

I'm getting some strange results using the autoincrement column on my
datatable. I'm populating a datatable with data from my database and
displaying this in a datagrid. The first time I get the data from the
database I create a new table with an autoincrement column bind the datagrid
to this new table. The auto increment column is displayed as an ID col in the
datagrid. The user can add records or modify records at this point, when the
user adds records i create a new row in the datatable and rebind the grid,
this all works fine and the autoincrement displays the next number in the ID
col. However if I make a change to the original rows that come back from the
database and then add a new record the autoincrement starts to skip numbers.
It looks like the datatable creates a copy of the original data before
modifying it but the autoincrement col is generated in between this
happening. I have verifed that it is something to do with the rowstate
because if I call AcceptChanges() and then re-generate the auto id it works
fine but I need to know the rowstate to perform my updates so I cannot use
this as a workaround.

Sorry for the long post but if anyone has any ideas pls let me know.

Thanks
N

 

[Sahil Malik]

Neil,

I guess no one has answered so, let me venture a try with a possible
suggestion.

Put simply and you might already know that the datatable's autonumber has
got nothing to do with the database's identity, they are the same concept,
but totally disconnected.

When you say that when you add a new record, the auto increment starts
skipping numbers, are you saying that the seed changed? ie.

Before update - 1,2,3,4
After update - 1,2,3,4,7,8,9,10
OR
After update - 1,2,3,4,6,8,10,12 ?

If it is indeed case #1 above, then probably the data table's auto numbering
scheme is using a static variable inside that somehow gets skipped during an
update (god knows what data adapter did internally, I couldn't reflect that
code portion).

But looking at the bigger picture, since a datatable is disconnected, your
architecture shouldn't be relying on the autonumbers generated in the data
table anyway. So even if the numbers were mickeymouse, donald duck, and
goofy, instead of 1,2,3, the logic should still work. A way around that is
to set seed values to -1, so it is clear to the user that until he/she saves
the data, those numbers are invalid. Then after an update, you'd just do a
merge of the data you got from the database, to show the actually generated
values - and hence this never gets to be a problem then. ... .

- Sahil Malik
You can reach me thru my blog at
http://www.dotnetjunkies.com/weblog/sahilmalik