Scope_Identity()

[Luc]

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!!

 

[HelloWorld]

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]

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

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

 

[William Vaughn]

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)
____________________________________________________________________________________________

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

 

[Alec MacLean]

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

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

 

[Cowboy \(Gregory A. Beamer\)]

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!
|
*************************************************

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

 

[Cowboy \(Gregory A. Beamer\)]

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!
|
*************************************************

 

[Cowboy \(Gregory A. Beamer\)]

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!
|
*************************************************

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)
____________________________________________________________________________________________

 

[Paul O'Flaherty]

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

 

[Amit Andharia]

<a href="http://myjourneythroughweb.blogspot.com/2009/01/difference-between-scopeidentity.html" alt="scope_identity()">here</a> is the complete detail of how the Scope_Identity works. additioanlly it explains other 2 methods @@Identity & current_ident that would be helpful in your scenario.



EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng