return an id while doing an insert\update to a table

Hi people,

i Have a small issue. I need to be able to retrive an id number of a new row to a table using the the insert into command. I was able to do this in sql 2000 but the same sql does not work now in 2005. here is the code

"Set NoCount On; select user_id from users insert into users (username) values('" & CurrentUser & "')"

This used to work in sql2000,

I am woundering if anyone could help me or point me in the right direction for doing this with SQL 2005

Best regards

RBowden

Did you try putting a semincolumn between the statements (before the insert) ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

I tried putting the ; before the insert function it is still returns 0

any other ideas?

|||

Ah, ok now I know what you mean. You are refering to the OUTPUT clause in SQL Server 2005.

"Set NoCount On; DECLARE @.Somevar VARCHAR(10);insert into users (username) OUTPUT user_id INTO @.SomeVar values('" & CurrentUser & "')"; SELECT @.SomeVar"

Look in the BOL, there should me some examples around that. If you are using an IDENTITY Column for the userid cou can also query the SCOPE_IDENTITY() function for the new identity value.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Cheers,

thank you very much for your help, that worked a treat.

keep up the good advice

all the best