Return Identity

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

@@IDENTITY (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp)

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

You can do:

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

I think :)

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?

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?

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.

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.