SQL : Get deleted ID and replace with new data

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 : Get deleted ID and replace with new data

Author

Topic

JmaC
Starting Member

18 Posts

Posted - 2010-08-25 : 02:07:13

hi all...

i have registration form with button DELETE,EDIT,INSERT..
All working fine...

example table:

i have table with 5rows...
1 | Peter
2 | James
3 | Helen
4 | Nickles
5 | John

if i delete ID 3 (3|Helen) from table...

1 | Peter
2 | James
4 | Nickles
5 | John

now am going to register new person...
what i need is when i click INSERT button in form ID:3 have to appear automatically in textbox/dropdownlist to register new person.(use deleted ID for register new person).

i write coding for get ID:6 and it's working good...

SELECT min(PANG)
FROM (SELECT AccNo + 1 AS PANG FROM debtor EXCEPT SELECT AccNo FROM Debtor)
AS boon WHERE PANG >300000000

can anyone help me...

Thanks & Regards
JmaC

Devart
Posting Yak Master

102 Posts

Posted - 2010-08-25 : 02:40:48

Hello,

You can try this:

SELECT TOP 1
t1.AccNo,
(SELECT AccNo FROM debtor WHERE id=t1.id+1) as new_id,
t1.id+1
FROM (SELECT 0 as AccNo UNION ALL SELECT AccNo FROM debtor) t1
WHERE
(SELECT count(*) FROM debtor WHERE AccNo=t1.AccNo+1)=0
ORDER BY
t1.AccNo

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-25 : 03:07:20

Would this work ok?

drop table stest
go
create table stest( cid int,cname varchar(10))
go
insert into stest values(1 , 'Peter')
go
insert into stest values(2 , 'James')
go
insert into stest values(4 , 'Nickles')
go
insert into stest values(5 , 'John')
go
select min(a.cid)+1 from stest a  where not exists(select 1 from stest b where a.cid+1=b.cid)

JmaC
Starting Member

18 Posts

Posted - 2010-08-25 : 03:26:15

quote:
Originally posted by Devart

Hello,

You can try this:

SELECT TOP 1
t1.AccNo,
(SELECT AccNo FROM debtor WHERE id=t1.id+1) as new_id,
t1.id+1
FROM (SELECT 0 as AccNo UNION ALL SELECT AccNo FROM debtor) t1
WHERE
(SELECT count(*) FROM debtor WHERE AccNo=t1.AccNo+1)=0
ORDER BY
t1.AccNo

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

thanks sir...when i apply this codes to my project it show error "Conversion failed when converting the nvarchar value 'I00002756' to data type int."

And the t1 is refer for what?

Thanks & Regards
JmaC

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-25 : 04:19:19

Did you try converting your sql like I said?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 05:12:27

This is so wrong on so many levels!

1) What if there are two users clicking INSERT? Both will be suggested ID:3
2) In your sample code, you use ID, but your code handles AccNo

How can we now what AccNo looks like when you don't supply enough information?

N 56°04'39.26"
E 12°55'05.63"

Devart
Posting Yak Master

102 Posts

Posted - 2010-08-25 : 06:25:01

Sorry,

SELECT TOP 1
t1.AccNo+1 as new_AccNo
FROM
(SELECT 0 as AccNo UNION ALL SELECT AccNo FROM deptor) AS t1
WHERE
(SELECT count(*) FROM deptor WHERE AccNo=t1.AccNo+1)=0
ORDER BY
t1.AccNo

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

JmaC
Starting Member

18 Posts

Posted - 2010-08-25 : 22:00:11

quote:
Originally posted by Peso

This is so wrong on so many levels!

1) What if there are two users clicking INSERT? Both will be suggested ID:3

N 56°04'39.26"
E 12°55'05.63"

hi...

What should i do to avoid this happen?
when there are two users clicking INSERT?

is TRIGGER in SQL manage this problem?
how to manage this problem with trigger?

Thanks & Regards
JmaC

JmaC
Starting Member

18 Posts

Posted - 2010-08-26 : 23:04:20

I create trigger to solve this problem...

CREATE TRIGGER TGRDebtor
ON dbo.Debtor
FOR UPDATE
AS
IF ()

END
go

can anyone help me out....

Thanks & Regards
JmaC

Devart
Posting Yak Master

102 Posts

Posted - 2010-08-27 : 02:17:10

Hello,

For example:

CREATE TRIGGER TGRDebtor ON dbo.deptor
FOR INSERT
AS
set nocount on
update d
set d.accno=(SELECT TOP 1
t1.AccNo+1 as new_AccNo
FROM
(SELECT 0 as AccNo UNION ALL SELECT AccNo FROM deptor) AS t1
WHERE
(SELECT count(*) FROM deptor WHERE AccNo=t1.AccNo+1)=0
ORDER BY
t1.AccNo)
from dbo.deptor d
inner join inserted d1
on d.accno=d1.accno

Column AccNo must be DEFAULT 0;

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

JmaC
Starting Member

18 Posts

Posted - 2010-09-01 : 22:20:45

"Column AccNo must be DEFAULT 0;"

If i have AccNo start with 300000000 and have data more than 2000 already.how i can make it?where i have to make changes..

Thanks & Regards
JmaC

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-02 : 00:59:29

You shouldn't even bother. If a userid gets deleted, so what, let it get deleted.

As Peter said above, this is so wrong on so many levels.

Instead of spending time filling in the holes, figure out why anyone cares that they exist.

Basically you're trying to fix something that isn't broken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources