[thelist] Using @@Identity

Ken Schaefer Ken at adOpenStatic.com
Tue Aug 23 21:14:32 CDT 2005


a) use Scope_Identity not @@IDENTITY. Books Online tells you why, but
basically if you have a trigger on a table that does an insert into another
table, then @@IDENTITY will return the identity in this second table rather
than the table you just inserted into. Scope_Identity insulates you from this

b) I would recommend against what Joshua has suggested. It might make sense
in the CF world, but in ASP/ASP.NET you should return an output parameter
(which is very lightweight), or use ExecuteScalar if you really want to
return a recordset. ExecuteScalar is useful if you only want to return a
recordset with a single row and a single column. I would recommend using an
Output Parameter

As for how to do this, I'm 99% certain there is a code sample in ASP.NET
Unleashed. There's also google. I suggest you look there first - it will save
you a lot of frustration.

Additionally, check out the lists here: www.aspadvice.com lots of good people
to answer ADO.NET and ASP.NET questions.

-- Warning - not tested
CREATE PROC usp_mySproc

	@NewRecordID	int	OUTPUT


		'New Value

	@NewRecord = Scope_Identity()


And then in your ASP.NET code:

'Create a SqlParameter object to hold the output parameter value
Dim RecordIDParam as New SqlParameter("@NewRecordID", SqlDbType.Int)

'Set Direction as Output
RecordIDParam.Direction = ParameterDirection.Output

'Add the parameter to the Command's Parameters collection

'Call the sproc...

'Now you can grab the output parameter's value...
Dim intNewRecordID as Integer = Convert.ToInt32(retValParam.Value)


: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Casey
: Sent: Wednesday, 24 August 2005 10:50 AM
: To: thelist at lists.evolt.org
: Subject: Re: [thelist] Using @@Identity
: Joshua said:
: > Add a trigger to the table on insert:
: >
: > CREATE TRIGGER mytable_insert ON dbo.mytable
: > AS SELECT @@identity AS id
: >
: > Then, whenever you insert a record you'll automatically get back a
: recordset
: > containing the inserted identity.
: >
: > In ASP:
: > Set oRS = oConn.Execute("INSERT INTO mytable...")
: > Response.Write "Inserted ID: " & oRS("id")
: Casey replied:
: Yes, I was thinking along the same lines.  Of course, I'm using ASP.NET
: rather then classic ASP.  After inserting the new record, I tried this:
: Dim doc_id as integer = cmdInsert("Identity")
: But got this error:
:  BC30367: Class 'System.Data.SqlClient.SqlCommand' cannot be indexed
: because
: it has no default property.
: - Becoming rather frustrated.

More information about the thelist mailing list