.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> ASP.Net >> Post New QuestionBookmark and Share Subscribe to Forum

Auto Increment id not getting decremented with Delete Record

Posted By: Jai     Posted Date: September 19, 2008    Points:2   Category :ASP.Net
Hi All,

I currently working asp.net with vb.net and inserting data from ASP.NET webpage into sqlserver 2005 express data table.

I have an auto increment Primary Key in a table. Let's say 10 are inserted. Then you delete those 10. You add another and the auto increment sets the id at 11. How can I get that table to start back to 1?

Plz help me and give code for increment id programmatically using VB.NET..

Thanks,
Jai

Responses
Author: Pankaj Mishra             
Posted Date: September 19, 2008     Points: 5   

Hi Jai,
You have to reset the "seed" property of the table to whatever the no you want.

Cheers
Pankaj

Author: Akhil Raj       [Moderator]      
Posted Date: September 22, 2008     Points: 5   

Yes Pankaj is correct. You want to reset the seed. Please try the following command to do that.

DBCC CHECKIDENT('TableName', RESEED, 0);

It will reset the Identity column of the TableName you specify

Author: Jai             
Posted Date: September 23, 2008     Points: 5   

Thank you Pankaj & Akhil. But can this be done from the ASP.Net side, like whenever I call the delete record method, I want the seed to be set to max value in the Primary key column. Will I be able to use this query in the TableAdapter wizard along with the delete query.

Thanks,
Jai

Author: Akhil Raj       [Moderator]      
Posted Date: September 23, 2008     Points: 5   

hi friend,

You just check whether any record available after delete and use the seed resetting. You can write this stored procedure

Author: Santosh             
Posted Date: October 14, 2009     Points: 5   

hi Jai
Just Run the Below Code inside ur database

DBCC checkident('TEST',reseed,0) -- TEST is the tableName

Regards
Santosh



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend