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

Retrieving from SQLServer to Word

Please,
how can I execute a SQL command into SQLServer from Word/VBA code using ADO? I want to execute a comand and work with resulting recordset.
Best regards.Please see the following link:

dbforums link (http://dbforums.com/t390641.html)

If this does not help, please respond.

Good luck !|||Thanks a lot. It works fine.

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the week
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like Week('01/08/06') to return 2. I cannot find
such a thing.
Thanks.
use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/
|||No, don't use DATEPART for weeknumbers. The function doesn't calculate week numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for weeknumbers. The function doesn't calculate
> week numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
>
|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||you don't need to, that was just to show you 2 values
The ISOWeek ifunction can be found here
http://msdn.microsoft.com/library/de...reate_7r1l.asp
http://sqlservercode.blogspot.com/
|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different week numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the week
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like Week('01/08/06') to return 2. I cannot find
such a thing.
Thanks.
use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/
|||No, don't use DATEPART for weeknumbers. The function doesn't calculate week numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for weeknumbers. The function doesn't calculate
> week numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
>
|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||you don't need to, that was just to show you 2 values
The ISOWeek ifunction can be found here
http://msdn.microsoft.com/library/de...reate_7r1l.asp
http://sqlservercode.blogspot.com/
|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different week numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the w
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like W('01/08/06') to return 2. I cannot find
such a thing.
Thanks.use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/|||No, don't use DATEPART for wnumbers. The function doesn't calculate w
numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for wnumbers. The function doesn't calculate
> w numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
>|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||you don't need to, that was just to show you 2 values
The ISOW ifunction can be found here
http://msdn.microsoft.com/library/d...r />
_7r1l.asp
http://sqlservercode.blogspot.com/|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different w numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the week
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like Week('01/08/06') to return 2. I cannot find
such a thing.
Thanks.use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/|||No, don't use DATEPART for weeknumbers. The function doesn't calculate week
numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for weeknumbers. The function doesn't calculate
> week numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
>|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||you don't need to, that was just to show you 2 values
The ISOWeek ifunction can be found here
http://msdn.microsoft.com/library/d...r />
_7r1l.asp
http://sqlservercode.blogspot.com/|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different week numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.