Scope_Identity()

  • Thread starter Thread starter Luc
  • Start date Start date
L

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!!
 
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
 
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 said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
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!
|
*************************************************
 
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 said:
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)
____________________________________________________________________________________________
Click to expand...
 
Back
Top