ASP+M$SQL: @@identity problem

Dear all,

I m writing a very typical ASP script which insert a record into one table with an identity column and then get the id generated and insert into another table.

Table_1 columns:
id_1
name

Table_2 columns:
id_1
id_2

But in with the ASP script below, I can't get the id_1 back.
Can anyone help me?

-----
objConn.Open strDSN

strSQL = "insert into Table_1 (name) values ('myName')

objRS.Open strSQL, objConn, adOpenStatic,adLockReadOnly

strSQL= "select @@identity"

Set objRS=objConn.Execute(strSQL)

response.write objRS(0)
-----

Nothing is written to the HTML. When I check the DB, the insert statement has been successfully executed.

I m using IIS5+MS SQL Server 8.
Thanks.

Package it up in a stored procedure.

MattR is right -- package it in a SP and have the SP return the resulting new ID number to you. However, there is a small flaw in the code you posted so I'll try to clear that up here as well. You need to execute the Insert and get the new ID in a recordset. Normally, however, ASP will pool db connections, so you can't be 100% certain you're still using the same db connection you used to execute the Insert statement, which is important because SQL Server will only return the Identity information to that connection in particular. That's partly why it's a good idea to put this in a SP with error trapping, etc. Anyway, this is the modified code... see if it works for you:

objConn.Open strDSN
strSQL = "insert into Table_1 (name) values ('myName')
objConn.Execute strSQL
strSQL= "select @@identity as Num"
objRS.Open strSQL
response.write objRS.Fields("Num") .Value
objConn.Close

HTH,
-Dave

If you're trying to extract the Identity value using a stored procedure, I found out through bitter experience that you need to precede the INSERT statement in the st. proc. with SET NOCOUNT ON. After the insert actions you put SET XYZ=@@IDENTITY (and then SET NOCOUNT OFF is nice). Then in your asp program you get that Identity value with intNewValue = objRs ("XYZ"). It's virtually impossible to find any documentation about doing it this way.

---RLF

Why not simply write a trigger?