Getting the id generated by SQL Server for a new record

I am using ASP.NET 2.0 to add records to a database table in an SQL Server
database. The id field is automatically created when a record is added, and
I would like to be able to know what value was assigned to the id field for
use in my ASP.NET application. Is there a way to find out what value was
assigned to the record at the same time I create the record (in otherwords,
I do not want to use a SELECT statement)? Thanks.
Nathan Sokalski
njsokalski@.hotmail.com
http://www.nathansokalski.com/
On Nov 29, 10:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
Look in the Books Online for the SCOPE_IDENTITY function. Lots of
good examples there.
|||On Nov 29, 9:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
You would want to return the SCOPE_IDENTITY() value as an output
parameter from your DbCommand after you perform the insert.
|||On 30 Nov., 07:18, Dan Gartner <dgart...@.gmail.com> wrote:
> On Nov 29, 9:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> You would want to return the SCOPE_IDENTITY() value as an output
You could use the OUTPUT feature of sql server 2005. But in this case
SCOPE_IDENTITY() and @.@.IDENTITY give back odd results while
IDENT_CURRENT() and inserted.id seem to give correct values.
inserted.id is the way the output feature is meant.
Example:
begin tran
create table tmp (id int identity, xyz varchar)
insert into tmp (xyz) output inserted.id values ('w')
insert into tmp (xyz) output @.@.identity values ('x')
insert into tmp (xyz) output ident_current('tmp') values ('y')
insert into tmp (xyz) output scope_identity() values ('z')
select * from tmp
drop table tmp
rollback
Results:
1
1
3
3
id xyz
-- --
1 w
2 x
3 y
4 z
(4 Zeile(n) betroffen)
|||Hi
You can use the output parameter of the stored procedure and return the
value that is being inserted. If you are using identity column then you can
use @.@.IDENTITY to return the last inserted indentity value.
--
Thanks,
Ibrahim
Software Consultant - Web Development, GB
"Nathan Sokalski" wrote:

> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokalski@.hotmail.com
> http://www.nathansokalski.com/
>
>
|||This method obviously requires using stored procs (which is almost always a
good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
bit of a bind I think. IIRC you can't issue an insert statement and get a
select back out in a single Execute... type command.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
message news:88277D10-9F15-42C3-BA0B-D18E4377E160@.microsoft.com...[vbcol=seagreen]
> Hi
> You can use the output parameter of the stored procedure and return the
> value that is being inserted. If you are using identity column then you
> can
> use @.@.IDENTITY to return the last inserted indentity value.
> --
> --
> Thanks,
> Ibrahim
> Software Consultant - Web Development, GB
>
> "Nathan Sokalski" wrote:
|||Kevin,
Sure you can, like this:
cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
(@.StudentName, @.Test1, @.Test2); Select Scope_Identity()"
Then:
ID = cmd.ExecuteScalar
Kerry Moorman
"TheSQLGuru" wrote:

> This method obviously requires using stored procs (which is almost always a
> good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
> bit of a bind I think. IIRC you can't issue an insert statement and get a
> select back out in a single Execute... type command.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
> message news:88277D10-9F15-42C3-BA0B-D18E4377E160@.microsoft.com...
>
>
|||I seem to recall a client trying to do that recently (using ADO classic) and
it not working. Perhaps they missed the semicolon. I will recheck their
attempts and see if that does it.
One additional question since I am not an ADO guru. Does the Select
Scope_identity() not return a single-column single-row result set, which the
executescalar isn't expecting?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:D12AFECB-DC31-4CE3-A2A2-14F3AB3CD95D@.microsoft.com...[vbcol=seagreen]
> Kevin,
> Sure you can, like this:
> cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
> (@.StudentName, @.Test1, @.Test2); Select Scope_Identity()"
> Then:
> ID = cmd.ExecuteScalar
> Kerry Moorman
>
> "TheSQLGuru" wrote:
|||Kevin,
ExecuteScalar returns the first column of the first row in the result set
returned by the query.
Kerry Moorman
"TheSQLGuru" wrote:

> One additional question since I am not an ADO guru. Does the Select
> Scope_identity() not return a single-column single-row result set, which the
> executescalar isn't expecting?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
|||I just checked back with the developer that had the issue. He swears that
using VB6 and ADO classic your example fails. Were you using ADOc or
ADO.NET?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:6AD6FF28-692A-4DFA-9BCB-7D8E9A2302C1@.microsoft.com...
> Kevin,
> ExecuteScalar returns the first column of the first row in the result set
> returned by the query.
> Kerry Moorman
>
> "TheSQLGuru" wrote:
>