[thelist] Using @@Identity

[Ken Schaefer]

ENOUGH!


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
problem

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

AS

	INSERT INTO
		myTable
	(
		Field1
	)
	VALUES
	(
		'New Value
	)

SELECT 
	@NewRecord = Scope_Identity()

GO

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
myCommand.Parameters.Add(RecordIDParam)

'Call the sproc...
myCommand.ExecuteNonQuery()

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


Cheers
Ken


: -----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
: > FOR INSERT
: > 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.