if i delete id no 3 from my asp.net3.5 page. then id no 4,5,6 should be updated like 3,4,5.

Question

0.00/5 (No votes)

See more:

Hi i have id in my sql table such as 1,2,3,4,5,6.... now i want if i delete id no 3 from my asp.net3.5 page.
then id no 4,5,6 should be updated like 3,4,5. it should be 1,2,3,4,5 not 1,2,4,5,6. what i can do.

Posted 9-Feb-11 23:41pm

c27bharti

Add a Solution

Comments

thatraja 10-Feb-11 9:47am

Seriously it's a very bad practice doing like this(as our members said in answers), so be careful with your data.

4 solutions

Solution 4

you can use the "count" code but if you want use that ,you must check your sql-server onder the 2008 :-O

Posted 10-Feb-11 0:59am

java_Edward

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)

#realJSOP · Accepted Answer · 2011-02-09T23:45:00

Solution 1

The ID is most likely automatically incremented. If you start monkeying around with the value (which I don't think SQL will allow you to do anyway), you will screw up the internal counter and risk a collision. Just leave them alone.

Posted 9-Feb-11 23:45pm

#realJSOP

Comments

OriginalGriff 10-Feb-11 6:17am

This is one of the reasons I think we should teach 'em about GUIDs before integers... :laugh:

DaveAuld · Accepted Answer · 2011-02-09T23:47:00

Solution 2

You could use a trigger on the table, so that on a delete event you then run an SP to iterate through the table and edit all row ids appropriately.

Now, why you would want to do this i don't know, but take into consideration if you have any other tables that would reference this id, you would screw up the relationships.

It would be better if this ID was normal integer field, and not an ID used as a primary key etc, i.e. have 2 ID's. your custom one could then be played with all you like, but the unique record id would never change.

Posted 9-Feb-11 23:47pm

DaveAuld

Comments

c27bharti 10-Feb-11 6:03am

if i will delete id 2 then all the dtls will be deleted from the table
exmp:-
ID Name Country
1 Jhon USA
2 Amir Pak
3 Raj IND
now when i'll delete id no 2 then all the info will be delete and id no 3 will be update like
1 Jhon USA
2 Raj IND

here id is PK.
Thanks

DaveAuld 10-Feb-11 6:09am

As i said in my answer, it would be fine if this is not a primary key, but just some form of row counter, don't mess around with PK's

OriginalGriff · Accepted Answer · 2011-02-09T23:52:00

Solution 3

Trust me, it is a bad idea. Any design which needs to change the id is fundamentally flawed.

The id is a way to uniquely identify a record: if you change the id, then two things can happen:
1) You could end up with two different records having the same id: bad! It's like having two cars with the same number plate. Fine until one of them gets stopped for speeding...
2) You could end up accessing the wrong record: What if there are two people accessing the database as the same time? If one of them re-numbers the ids while the other starts retrieving the data, which record does he get?

There is also the fun of: what if you decide to re-order the records by first name, instead of last name for a particular view. Are you going to renumber them then, as well? What happens to all the other users then?

Leave the id alone!

Posted 9-Feb-11 23:52pm

OriginalGriff

Comments

c27bharti 10-Feb-11 6:03am

if i will delete id 2 then all the dtls will be deleted from the table
exmp:-
ID Name Country
1 Jhon USA
2 Amir Pak
3 Raj IND
now when i'll delete id no 2 then all the info will be delete and id no 3 will be update like
1 Jhon USA
2 Raj IND

here id is PK.
Thanks

OriginalGriff 10-Feb-11 6:05am

"here id is PK."
Precisely. What you are proposing will bugger up a real, live system.
It is similar to saying: "I have torn up this banknote. Now we must re-number all the banknotes with higher numbers."
Not a good idea at all.

c27bharti 10-Feb-11 6:12am

Id Is PK but not Auto increment.

OriginalGriff 10-Feb-11 6:15am

It doesn't matter: If it was AutoIncrement, then the database would throw an exception when you tried to change it - simply because it knows it is a bad idea.
Never change Id's: if you need the ordinal number of a record, use that, but don't try to alter unique ids!

c27bharti 10-Feb-11 6:19am

OK And Thanks for your valueable suggestion. Thanks Agian.....