Reset AutoIncrement value

[Srikanth]

Hi,
I have a DataTable with some rows and bind to a DataGrid. The first
column in the DataTable is an AutoIncrement value. I am clearing the rows in
the DataTable by calling DataTable.Clear(). After this I want new rows in
the DataGrid to start with AutoIncrement value of '1' instead of continuing
the number from the earlier rows before clearing.

How to do this?

Thanks 'n' Regards,
Srikanth.

 

[Kevin Yu]

Hi Srikanth,

We cannot reset an auto increment value back to 1. This behavior is by
design. Because if you set it back to 1, there will be some conflicts of
the primary key and foreign key constraint.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Tim]

Srikanth,

If you don't show the identity column to the user then it won't look odd
having an odd sequence.
Further, if you have multiple users concurrently, you won't get consequetive
ID's anyway.

Use the identity column for the purpose it is intended: as a proxy ID.

If you really do want / need to show an ordinal row number (which never will
be the same as an Identity), then add an extra column into a dataset object
and loop through adding in the row number, then present it to the user.

- Tim

 

[William Ryan]

Kevin:

I think you can reset the seed. I have an app where I use the identity
client side although it isn't an identity field on the server. Anyway, I
reset the seed depending on the user role b/c they have blocks of records
availalble to the . It's definitely a weird implementation, but I inherited
it and had to make it work in .NET. However, if it was an Identity field in
the DB, is it not supposed to work? This is my snippet...

With Ds1.Tables("Employees").Columns(9)

.AutoIncrement = True

.AutoIncrementSeed = MaxPlusOne()

.AutoIncrementStep = 1

 

[Severin]

Well, in MS Access if you open up the actual database (not through code) I
know that if you clear ALL records from the table, then 'Tools | Compact and
Repair' then any new records will begin with 1.

Not sure how to do this through code, but I did once find a code snippet
that will compact a database through code. You must first copy the database
to a temp file, compact the temp file, then rename the temp file to the
original database name (overwrites the original with compacted copy) this is
done by coding for the Jet Engine itself, not the database.

MAYBE it is possible through code...

First delete every record in the table - in code
Then Compact database - in code.

Never tried it in code, but I do reset my autonumbers every three months by
clearing and compacting.

Severin

 

[Kevin Yu]

Hi William,

Thanks for your reminding. Yes, we can set the auto increment seed back to
1, if the column is not a primary key or only a client side AutoIncrement.
However, if the column is a primary key, you'll receive an exception during
updating the database from the dataset.

For more information, please check the following:

1. For Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;287756

2. For SQL Server
Check DBCC CHECKIDENT in Books Online.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Srikanth]

Hi,
Actually Iam clearing everything in the DataSet (DataTables) and
trying to add new rows. The DataTable has a foreignKey column from the
parent table and a unique key column which takes AutoIncrement values from
UI. So, if I create new rows for this table, I want AutoIncrement values to
start from '1'. But, somehow in the datagrid, where Iam binding this
Datable, it generates values continuing
the number from the earlier rows before clearing.

How do I reset the AutoIncrement values to start from '1'?.

Thanks 'n' Regards,
Srikanth.