...

View Full Version : Return Identity



christrinder
07-02-2004, 12:22 PM
Hi,

Previously all web apps I have done have been in asp.net (c#) using SQL server. now I'm doing a small app for myself in asp.net (c#) using a microsoft database. What I want to know is how you return the identity of a newly inserted record when you use a inline sql string. I've tried inserting the record then running a select for the top 1 record ordering my the primary key descending, but sometimes this seems to be running too quickly and hence dosn't return the correct ID. Is there another way to do this?

Thanks in advance for you help.

Chris

allida77
07-02-2004, 01:22 PM
@@IDENTITY (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp)

christrinder
07-02-2004, 01:32 PM
Thanks, but this is sql-server. I'm executing a sql string on the web page. i.e. a simple...

"INSERT INTO table (column) VALUES ("+ value +")";

How would I return the identity of that?

Thanks,
Chris

Spudhead
07-02-2004, 03:04 PM
You can do:

"SET NOCOUNT OFF INSERT INTO table (column) VALUES ("+ value +")" SELECT @@IDENTITY AS newID SET NOCOUNT ON;"

I think :)

christrinder
07-02-2004, 03:41 PM
No, that dosn't work... even if you run it in a query design in the acutal database. It knows that the statement should end after the list of values so throws an error. Any other ideas?

justD
07-02-2004, 07:11 PM
Hi,

Don't know if it's ideal, but here's something that works on my PC with MS SQL Server


sql = "execute('INSERT INTO TryInsert(TheValue) VALUES (''CF'') SELECT NewVal = @@identity')"
set rs = conn.execute(sql)
if NOT rs.EOF Then
Response.Write(rs("NewVal"))
End If

Wouldn't a stored procedure give you more freedom though?

Roy Sinclair
07-02-2004, 07:13 PM
You can do:

"SET NOCOUNT OFF ; INSERT INTO table (column) VALUES ("+ value +")"; SELECT @@IDENTITY AS newID ; SET NOCOUNT ON;"

I think :)

Adding the semi-colons as shown in the quoted section above should fix the problem of that statement not working.

boywonder
07-07-2004, 10:56 PM
Be careful using @@IDENTITY... it can lead to problems in complicated stored procedures

SCOPE_INDENTITY() (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_6n8p.asp?frame=true) is more specific. One should at least be aware of the difference between the two and avoid possible headaches.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum