[thelist] @@Identity (was [OT] SQL and portability.)

[Warden, Matt]

> the danger of concurrent hits has been covered previously in a couple of
> threads regarding the @@identity feature of sql/server, which also works
in
> access2k apparently --
> http://www.aspwatch.com/c/200043/d1AED4F05B84711D4AF0200A0C9E95208.asp
> (thanks, cory)


I did a little testing just now on MSSQL7 and ASP2 (not 100% positive of ASP
version) and the code on that page doesn't work. You have to call
oRS.nextRecordset before attempting to access the value returned by
@@Identity if you have two SQL commands in one ASP execute command. So,
rather than:

...
StrSQL = " Insert Into Customer(CustomerName) Values('Raymond') ;
Select @@Identity"

Set adoRs = adoCnn.Execute(strSQL)

Response.Write(adoRs.Fields(0).value)
...


You'd do:

...
StrSQL = " Insert Into Customer(CustomerName) Values('Raymond') ;
Select @@Identity"

Set adoRs = adoCnn.Execute(strSQL)

adoRs.nextRecordset

Response.Write(adoRs.Fields(0).value)
...

Though, I'm connecting directly through OLEDB, and that might make the
difference (though I doubt it because the recordset is an ADO object). I'd
be interested if anyone could try this with a direct connection to ADO.



--
mattwarden
mattwarden.com