Sequence problem after delete

Question

0.00/5 (No votes)

See more:

Hey Guys ,

my Question is the following suppose i have 4 records in the database and they have id 1,2 ,3 ,4 and i deleted the second one so now i have 1 ,3 ,4 so how can i fix this automatically and reorder them to be 1 2 3 .

i am asking this because when i create a new record in my vb.net application i am counting the number of record and incrementing it so now i have a record id of 4 and i can't create this new record because there is a record with that id .

thank you in advance for your help .

regards

Posted 19-Oct-11 8:26am

EEKahwaji

Add a Solution

2 solutions

Solution 2

If you make id the primary key, you don't need to worry about inserting anything into it. The database will take care of this. If you delete a record, there will still be a missing primary key however.

Posted 19-Oct-11 8:32am

Abhinav S

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

fjdiewornncalwe · Accepted Answer · 2011-10-19T08:30:00

Solution 1

You don't want to do that. What you want to do is instead of using the count + 1 of records to come up with a new id, use a MAX(id) + 1 instead. That is still not a great solution. The proper solution would be to alter the id column of the table into an identity column with an auto increment property and let the db deal with that on its own.

Posted 19-Oct-11 8:30am

fjdiewornncalwe

Comments

FastEvo8 19-Oct-11 15:00pm

Well said Marcus.
I will also add that trying to reorder the table could become a concurrency nightmare and a performance issue.