Click here to Skip to main content
14,640,603 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi Guys.

Something has been bothering me for a while, i have been rigorously testing my database with updating, editing, and deleting.

one thing i have noticed while deleting is that the auto increment frunction on the primary keys do not revert when deleting from the datagrid.

for example:

if i have 5 entries in a table (id key 1, 2, 3, 4, 5) and delete the last entry (5), when i add back into that table it is still counting so the ids are (id key 1, 2, 3, 4, 6).

is there an easy way to ensure that the id keys count correcty instead of ++'ing regardless of deletion of data?

has it something to do with the fields under Identity Specification on the table definitions?

sorry if im not to clear on what im asking as its the only way i can think of explaining it....

Regards
Matt.
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

You can reset the count by using this in SSMS
DBCC CHECKIDENT (<tablename>, RESEED, <newvalue>)


Replace the tablename with the name of the table that you want to reset and replace newvalue with the number you want to start with - 1. For example if you want to start numbering back at 5 then the seed value will be 4.
   
v2
Comments
BBCokeley 16-Apr-12 17:15pm
   
sorry i forgot to mention the table is not updated in the SSMS (detached from SSMS and copied to local), it is kep localy inside the application and reads/writes to this data base.

is there a way to do it from here? can i re atach the DB in its updated form?

thanks for helping.
Wes Aday 16-Apr-12 17:18pm
   
Yes you can re-attach the db within SSMS. Right click on Databases and Attach.
BBCokeley 16-Apr-12 17:28pm
   
ok trying it out now, will let you know
thanks
BBCokeley 16-Apr-12 17:40pm
   
Doesnt seem to be working, and the path im re attaching from is not the path that appears in the databases when i reatach it...

something is going wrong somewhere.
BBCokeley 16-Apr-12 17:45pm
   
On the DBCC CHECKIDENT (<tablename>, RESEED, <newvalue>) it runs the query and executes with no problems, but it doesnt seem to be ressetting it, as i ve checked the top 200 rows and still the huge leaps in incrementation remain :S.

any idea why?
BBCokeley 16-Apr-12 17:49pm
   
so i cant physically change the current numbers in the table?

only reset the increment for new data entries?
Wes Aday 16-Apr-12 18:31pm
   
I do not believe you can. I could be wrong. All I have been able to find is resetting the count not resetting everything else. Makes sense though. If you already had records matching up with those keys then other tables would have to be reset too.
Rate this:
Please Sign up or sign in to vote.

Solution 2

Right, i get what your saying, ive reattached the DB to SSMS, ive droped the primary key from the table manually reset the ID's then antered your helpful SQL statement and set it to continue on the value.

reset the primary key, and indexing back on.

detached and re imported to test and it works

Thanks for your help guys much appreciated :)

Best Regards
Matt.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100