MBR IT/.NET 247 : id of last inserted record on ASPFriends.com 'ngfx-sqlclient' list

id of last inserted record

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Ian Lipsky (VIP)

working on a page where i have to update more then one table, in more then
one database, one of which is going to be an access database most likely.

When i sent a record into the sql2000 database, i need to get the id of
the last inserted record so i can insert it into a lookup table in access.

can anyone point out how i can get the last insert record id?

Ian

Reply to this message...

Douglas Reilly (VIP)

SELECT @@IDENTITY

Perhaps easier would be to use a stored procedure and have the stored =
procedure get the value back. This can be complicated if you have =
triggers that might insert records in tables with identity values. =
Search @@IDENTITY in the SQL Books On line for details of some other =
ways to get the identity value in this case.

----- Original Message -----
From: Ian Lipsky <Click here to reveal e-mail address>
To: Click here to reveal e-mail address
Sent: Sat, 29 Jun 2002 19:25:29 -0700 (PDT)
Subject: id of last inserted record
[Original message clipped]

Reply to this message...

Wolfgang Baeck

assuming you use a stored procedure to insert into a table where the record
id is an identity, you can get the last inserted record id via:

declare @iRecordId int
...
...
insert into ...

select @iRecordId = @@Identity

Also, look up scope_identity() in case of triggers.

Wolfgang

-----Original Message-----
From: Ian Lipsky [mailto:Click here to reveal e-mail address]
Sent: Saturday, June 29, 2002 8:25 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] id of last inserted record

working on a page where i have to update more then one table, in more then
one database, one of which is going to be an access database most likely.

When i sent a record into the sql2000 database, i need to get the id of
the last inserted record so i can insert it into a lookup table in access.

can anyone point out how i can get the last insert record id?

Ian

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

Ian Lipsky (VIP)

ahh, ok. I've used that once before but in an asp page not aspx. Thought
maybe there was something new.

Usinfg a sproc would probably be more complicated then its worth since i
havent written one before.

Ian

On Sat, 29 Jun 2002, Douglas Reilly wrote:

[Original message clipped]

Reply to this message...

Peter Brunone

Ian,

    There may be a cool ADO.NET way to do this, but until someone more helpful
shows up, here ya go.

    Simple SP's are a snap, as I found out in a similar situation. You can
even just build a string and pass it in like you would with a straight
query. Here's a basic insert template that returns the inserted index:

CREATE PROCEDURE [sp_Name_Here]

@var1 int, @Var2 nvarchar(length)
-- (just to show you how the types look)

AS
SET NOCOUNT ON
INSERT INTO tblName(Col1, Col2)
VALUES(@Var1,@Var2)
SET NOCOUNT OFF

SELECT @@IDENTITY
GO

    The NOCOUNT feature keeps any text -- like recordsets, return params, or
rows affected -- from getting into the return stream; it's more of a
precaution for me, although the gurus could tell you if you really need it
in this case. When you're ready to send the identity value back, just turn
NOCOUNT off and do your select.

Cheers,

Peter

|-----Original Message-----
|From: Ian Lipsky [mailto:Click here to reveal e-mail address]
|
|ahh, ok. I've used that once before but in an asp page not aspx. Thought
|maybe there was something new.
|
|Usinfg a sproc would probably be more complicated then its worth since i
|havent written one before.
|
|Ian
|
|On Sat, 29 Jun 2002, Douglas Reilly wrote:
|
|> SELECT @@IDENTITY
|>
|> Perhaps easier would be to use a stored procedure and have the
|stored procedure get the value back. This can be complicated if
|you have triggers that might insert records in tables with
|identity values. Search @@IDENTITY in the SQL Books On line for
|details of some other ways to get the identity value in this case.
|>
|> ----- Original Message -----
|> From: Ian Lipsky <Click here to reveal e-mail address>
|> >
|> >working on a page where i have to update more then one table,
|in more then
|> >one database, one of which is going to be an access database
|most likely.
|> >
|> >When i sent a record into the sql2000 database, i need to get the id of
|> >the last inserted record so i can insert it into a lookup table
|in access.
|> >
|> >can anyone point out how i can get the last insert record id?
|> >
|> >Ian

Reply to this message...

Jeffrey A. Little

There are a couple of different ways. One is to couple the @@IDENTITY
function with your insert statement

INSERT INTO foo (field1, field2, field3) VALUES ('I', 'don't,
'care')
SELECT @@IDENTITY AS "Identity"

If you don't want to couple it with the insert statement, you can simply
do a SELECT MAX(ID) From foo query and trap the returned value.

Jeff Little, MCSD
CounselTechLLC

-----Original Message-----
From: Ian Lipsky [mailto:Click here to reveal e-mail address]
Sent: Saturday, June 29, 2002 10:25 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] id of last inserted record

working on a page where i have to update more then one table, in more
then
one database, one of which is going to be an access database most
likely.

When i sent a record into the sql2000 database, i need to get the id of
the last inserted record so i can insert it into a lookup table in
access.

can anyone point out how i can get the last insert record id?

Ian

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

David L. Penton

Using the MAX() without a transaction is a rathe unsafe operation.
Actually, it is recommended you use SCOPE_IDENTITY() instead of @@IDENTITY
for retrieving the IDENTITY value from the column. That way, if there is
ever a trigger added to the table, the @@IDENTITY value *could* be incorrect
(i.e. the new identity from another table, in the case of a tracking table)

David L. Penton, Microsoft MVP
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Jeffrey A. Little [mailto:Click here to reveal e-mail address]

There are a couple of different ways. One is to couple the @@IDENTITY
function with your insert statement

INSERT INTO foo (field1, field2, field3) VALUES ('I', 'don't,
'care')
SELECT @@IDENTITY AS "Identity"

If you don't want to couple it with the insert statement, you can simply
do a SELECT MAX(ID) From foo query and trap the returned value.

Jeff Little, MCSD
CounselTechLLC

-----Original Message-----
From: Ian Lipsky [mailto:Click here to reveal e-mail address]

working on a page where i have to update more then one table, in more
then
one database, one of which is going to be an access database most
likely.

When i sent a record into the sql2000 database, i need to get the id of
the last inserted record so i can insert it into a lookup table in
access.

can anyone point out how i can get the last insert record id?

Ian

Reply to this message...

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com