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.

I haven't tested this, but I think it should do what you want. I think your problem might have been in the

But I'm not completely sure. Try that, and see if it works. If it does, you're set (you should comment it though, I was lazy). If it throws an error, post it.
HTH

I have solved the problem before I see your reply, but thanks anyway, karsh44.

---
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)
---

I think the problem is that I executed the first strSQL at the same time when I open the connection. Then I executed the second one with "objConn.Execute(strSQL)", so the server may treat it as 2 separate transaction and cannot get @@identity as NULL.

My revised code is as follows:
---
objConn.Open strDSN

strSQL = "insert ........ "

objConn.Execute(strSQL)

strSQL= "select @@identity as myID"

Set objRS=objConn.Execute(strSQL)

response.write objRS("myID")
---

I have done QUITE a bit of sql programming over the last few years, and found the @@identity variable to work only partly. Granted, it gets the last identity value entered...over the whole database. Now, if you doing all of your programming in ASP...this should be acceptable. But, if you start getting into the realm of triggers and such...the following is MUCH better:

sql = "Select Ident_Current('tablename') As Whatever"
set rs = objconn.execute(sql)

newid = rs("whatever") or just rs(0)
rs.close

This makes sure it gets the last identity...from the table you just inserted into.

Chris

Actually, @@IDENTITY is localized to a database connection, and you have even more granularity with SQL Server 2000 where you can specify which table per connection.