Scope_Identity()

Dear,

how can I get the scope_identity after inserting records into the database


<System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select, True)> _
Public Function Insertdata(@Name, @firstname) as integer

return adapter.insert(@name,@firstname)

end function


The return statement only returns 1 incase the insert was
successful........this while I'm interested to get the last Id from this
insert.

your help is most appreciated!!



--
Best regards
Nicole

HelloWorld
Mon Mar 03 04:29:58 CST 2008

Is your question to retrieve the identity value? in that case one
alternative is to query the @@Identity after your insert...

select @@identity from TableName


"Luc" <Luc@discussions.microsoft.com> wrote in message
news:79B36FC9-BF47-405C-A446-B2DD0CA1D226@microsoft.com...
> Dear,
>
> how can I get the scope_identity after inserting records into the database
>
>
> <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select,
> True)> _
> Public Function Insertdata(@Name, @firstname) as integer
>
> return adapter.insert(@name,@firstname)
>
> end function
>
>
> The return statement only returns 1 incase the insert was
> successful........this while I'm interested to get the last Id from this
> insert.
>
> your help is most appreciated!!
>
>
>
> --
> Best regards
> Nicole

Luc
Mon Mar 03 04:41:00 CST 2008

Dear ,

Thank you for your quick response,

Yes, I want to retrieve the identity, In my SQL I have insert into names
(name, firstname) values(@name,@firstnam); select is from names where
id=scope_identity()

How can I retrieve this identity in code ?? Hope you can help lme out here

--
Best regards
Nicole

"HelloWorld" wrote:

> Is your question to retrieve the identity value? in that case one
> alternative is to query the @@Identity after your insert...
>
> select @@identity from TableName
>
>
> "Luc" <Luc@discussions.microsoft.com> wrote in message
> news:79B36FC9-BF47-405C-A446-B2DD0CA1D226@microsoft.com...
> > Dear,
> >
> > how can I get the scope_identity after inserting records into the database
> >
> >
> > <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select,
> > True)> _
> > Public Function Insertdata(@Name, @firstname) as integer
> >
> > return adapter.insert(@name,@firstname)
> >
> > end function
> >
> >
> > The return statement only returns 1 incase the insert was
> > successful........this while I'm interested to get the last Id from this
> > insert.
> >
> > your help is most appreciated!!
> >
> >
> >
> > --
> > Best regards
> > Nicole
>
>
>

William
Mon Mar 03 12:05:41 CST 2008

Ah, no. Unless this is Access/JET you should NOT use @@Identity to return
the latest identity value.
The correct way to handle identity value retrieval is SCOPE_IDENTITY as she
said.
Generally, one executes a batch that includes an extra SELECT to return the
identity value after the INSERT. I would implement this in a stored
procedure that also used RETURN to return a success/failure flag (0,1) along
with the SCOPE_IDENTITY value as a second rowset (expensive) or an OUTPUT
parameter.

See my book for more details. It seems that now that Fawcett has
disappeared, the magazine articles they were hosting are gone too--otherwise
I would send you to an article on identity I wrote some time ago. Until I
get that resurrected, this might do.
http://msdn2.microsoft.com/en-us/library/aa224821(SQL.80).aspx

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"HelloWorld" <no_mails_AT_to_me_.com> wrote in message
news:upWnQlRfIHA.5280@TK2MSFTNGP02.phx.gbl...
> Is your question to retrieve the identity value? in that case one
> alternative is to query the @@Identity after your insert...
>
> select @@identity from TableName
>
>
> "Luc" <Luc@discussions.microsoft.com> wrote in message
> news:79B36FC9-BF47-405C-A446-B2DD0CA1D226@microsoft.com...
>> Dear,
>>
>> how can I get the scope_identity after inserting records into the
>> database
>>
>>
>> <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select,
>> True)> _
>> Public Function Insertdata(@Name, @firstname) as integer
>>
>> return adapter.insert(@name,@firstname)
>>
>> end function
>>
>>
>> The return statement only returns 1 incase the insert was
>> successful........this while I'm interested to get the last Id from this
>> insert.
>>
>> your help is most appreciated!!
>>
>>
>>
>> --
>> Best regards
>> Nicole
>
>

Alec
Mon Mar 03 12:30:53 CST 2008

In your SQL SPROC, add an Output parameter

create proc ...
@name ... ,
@firstname ... ,
@NewID INT OUTPUT

AS

INSERT INTO ... (Name, Firstname)
VALUES
(@Name, @Firstname)

SELECT @NewID = SCOPE_IDENTITY()

And in your app code, add the output parameter to the parameter set:
E.g. using DAAB

dbCmd = db.GetStoredProcedure("usp_MySproc")
db.AddInParameter(dbCmd, "Name", dbtype.string, myStringValue)
...
db.AddOutParameter(dbCmd, "NewID", dbtype.Int32, 4)
'Retrieve the new ID value created by the sproc
dim myNewID as integer = Cint(db.GetParameterValue(dbCmd, "NewID"))


HTH

Al


"Luc" <Luc@discussions.microsoft.com> wrote in message
news:064D9F16-87C9-4578-9D4B-3E4BFF805406@microsoft.com...
> Dear ,
>
> Thank you for your quick response,
>
> Yes, I want to retrieve the identity, In my SQL I have insert into names
> (name, firstname) values(@name,@firstnam); select is from names where
> id=scope_identity()
>
> How can I retrieve this identity in code ?? Hope you can help lme out here
>
> --
> Best regards
> Nicole
>
> "HelloWorld" wrote:
>
>> Is your question to retrieve the identity value? in that case one
>> alternative is to query the @@Identity after your insert...
>>
>> select @@identity from TableName
>>
>>
>> "Luc" <Luc@discussions.microsoft.com> wrote in message
>> news:79B36FC9-BF47-405C-A446-B2DD0CA1D226@microsoft.com...
>> > Dear,
>> >
>> > how can I get the scope_identity after inserting records into the
>> > database
>> >
>> >
>> > <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select,
>> > True)> _
>> > Public Function Insertdata(@Name, @firstname) as integer
>> >
>> > return adapter.insert(@name,@firstname)
>> >
>> > end function
>> >
>> >
>> > The return statement only returns 1 incase the insert was
>> > successful........this while I'm interested to get the last Id from
>> > this
>> > insert.
>> >
>> > your help is most appreciated!!
>> >
>> >
>> >
>> > --
>> > Best regards
>> > Nicole
>>
>>
>>

Cowboy
Mon Mar 03 17:58:56 CST 2008

He wants scope_identity, not just last value. The way he has things coded,
your example would bomb anyway.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
"HelloWorld" <no_mails_AT_to_me_.com> wrote in message
news:upWnQlRfIHA.5280@TK2MSFTNGP02.phx.gbl...
> Is your question to retrieve the identity value? in that case one
> alternative is to query the @@Identity after your insert...
>
> select @@identity from TableName
>
>
> "Luc" <Luc@discussions.microsoft.com> wrote in message
> news:79B36FC9-BF47-405C-A446-B2DD0CA1D226@microsoft.com...
>> Dear,
>>
>> how can I get the scope_identity after inserting records into the
>> database
>>
>>
>> <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select,
>> True)> _
>> Public Function Insertdata(@Name, @firstname) as integer
>>
>> return adapter.insert(@name,@firstname)
>>
>> end function
>>
>>
>> The return statement only returns 1 incase the insert was
>> successful........this while I'm interested to get the last Id from this
>> insert.
>>
>> your help is most appreciated!!
>>
>>
>>
>> --
>> Best regards
>> Nicole
>
>

Cowboy
Mon Mar 03 17:59:56 CST 2008

You can either send a batch of statments in or code your batch in a SQL
stored procedure. Thsi will require a bit of refactoring of your data
access, but it is worth it in this case.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
"Luc" <Luc@discussions.microsoft.com> wrote in message
news:79B36FC9-BF47-405C-A446-B2DD0CA1D226@microsoft.com...
> Dear,
>
> how can I get the scope_identity after inserting records into the database
>
>
> <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select,
> True)> _
> Public Function Insertdata(@Name, @firstname) as integer
>
> return adapter.insert(@name,@firstname)
>
> end function
>
>
> The return statement only returns 1 incase the insert was
> successful........this while I'm interested to get the last Id from this
> insert.
>
> your help is most appreciated!!
>
>
>
> --
> Best regards
> Nicole

Cowboy
Mon Mar 03 18:00:49 CST 2008

But Bill, it is so much fun to get back the identity from the next record
inserted. What is coding without moving values? ;->

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
"William Vaughn" <billvaNoSPAM@betav.com> wrote in message
news:4EDE7BC8-AC2D-40ED-8DFE-2E7D4C05ED30@microsoft.com...
> Ah, no. Unless this is Access/JET you should NOT use @@Identity to return
> the latest identity value.
> The correct way to handle identity value retrieval is SCOPE_IDENTITY as
> she said.
> Generally, one executes a batch that includes an extra SELECT to return
> the identity value after the INSERT. I would implement this in a stored
> procedure that also used RETURN to return a success/failure flag (0,1)
> along with the SCOPE_IDENTITY value as a second rowset (expensive) or an
> OUTPUT parameter.
>
> See my book for more details. It seems that now that Fawcett has
> disappeared, the magazine articles they were hosting are gone
> too--otherwise I would send you to an article on identity I wrote some
> time ago. Until I get that resurrected, this might do.
> http://msdn2.microsoft.com/en-us/library/aa224821(SQL.80).aspx
>
> --
> __________________________________________________________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205 (Pacific time)
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> ____________________________________________________________________________________________
> "HelloWorld" <no_mails_AT_to_me_.com> wrote in message
> news:upWnQlRfIHA.5280@TK2MSFTNGP02.phx.gbl...
>> Is your question to retrieve the identity value? in that case one
>> alternative is to query the @@Identity after your insert...
>>
>> select @@identity from TableName
>>
>>
>> "Luc" <Luc@discussions.microsoft.com> wrote in message
>> news:79B36FC9-BF47-405C-A446-B2DD0CA1D226@microsoft.com...
>>> Dear,
>>>
>>> how can I get the scope_identity after inserting records into the
>>> database
>>>
>>>
>>> <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select,
>>> True)> _
>>> Public Function Insertdata(@Name, @firstname) as integer
>>>
>>> return adapter.insert(@name,@firstname)
>>>
>>> end function
>>>
>>>
>>> The return statement only returns 1 incase the insert was
>>> successful........this while I'm interested to get the last Id from this
>>> insert.
>>>
>>> your help is most appreciated!!
>>>
>>>
>>>
>>> --
>>> Best regards
>>> Nicole
>>
>>
>

Paul
Thu Mar 06 07:49:28 CST 2008

This should help
http://www.mikesdotnetting.com/Article.aspx?ArticleID=54
Paul


"Luc" <Luc@discussions.microsoft.com> wrote in message
news:79B36FC9-BF47-405C-A446-B2DD0CA1D226@microsoft.com...
> Dear,
>
> how can I get the scope_identity after inserting records into the database
>
>
> <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select,
> True)> _
> Public Function Insertdata(@Name, @firstname) as integer
>
> return adapter.insert(@name,@firstname)
>
> end function
>
>
> The return statement only returns 1 incase the insert was
> successful........this while I'm interested to get the last Id from this
> insert.
>
> your help is most appreciated!!
>
>
>
> --
> Best regards
> Nicole