SQL command to read last deleted ID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL command to read last deleted ID

Author

Topic

JmaC
Starting Member

18 Posts

Posted - 2010-08-19 : 05:36:46

hi all...!
i have a database and i create a form with button edit,delete, New Registration..using Formview in asp.net..
everything was working good...

And i write SQL statement to get New ID number into dropdownlist/TextBox when i click button "New Registration".

i can get new(next) ID number after read database
it's also working good

Now i want change it to get ID which was deleted from table and replace the ID with new record...what i need is when i click "New Registration" i want get deleted "ID" first and then go to new record ID...

my code is here:-it working for get next ID after read database.
[code]
SELECT min(PANG)
FROM (SELECT ID + 1 AS PANG
FROM Table1
EXCEPT SELECT ID FROM Table1)
AS boon WHERE PANG >300000
[code]

Thanks & Regards
JmaC

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-08-19 : 06:44:25

What do you mean, after delete?

If you are using SQL 2005 or greater, use the output when you do a delete:

DELETE <table>
OUTPUT <fields> INTO <Table or Variable>
FROM <Table>
WHERE <Records To delete>

This way you can save all deleted records for archiving or you can put the id into a variable to use later in the same procedure..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 14:05:11

didnt understand why you need to generate id before hand in interface? so what will happen if insertion of data (transaction) resulted in error? Isnt it better to generate this ID only after succesful save of details?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JmaC
Starting Member

18 Posts

Posted - 2010-08-19 : 20:57:20

i have a table and 5 column
if click button new in my form to register new member i will automatically get id =6 in my drop down list..
if i deleted one row(id=3) from my table

now how i can get id=3 first and only go to id=6 for register..

Thanks & Regards
JmaC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 11:13:50

quote:
Originally posted by JmaC

i have a table and 5 column
if click button new in my form to register new member i will automatically get id =6 in my drop down list..
if i deleted one row(id=3) from my table

now how i can get id=3 first and only go to id=6 for register..

Thanks & Regards
JmaC

do you mean your next insertion will be at deleted position? (id=3)
How will you maintain insertion sequence then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources