ASP and SQL Server

[mobo10]

Good morning,

I am having the following problem:

I need to add new records on a SQL Server and then get the @@identity to pass to a related table. The problem is that the client is not letting me used stored procedures. How can I pick up the identity in ASP?

Thanx!

[monte96]

The only things I could suggest is to try:

select max(identidycolumnname) from table

If your using an identity column in the table that is auto-populated by SQL Server, you should 99% of the time get the correct one. Only if someone else saved a record in the time it takes to execute the query after the insert, would the data be wrong.

Another thing to try would be to use a select with a where clause that includes the criteria you just wrote to the table to get just the identity column returned. That would work unless there is more than one match, then you could use the higher of the two. (Again, there is a small margin of error here- like if someone else saves similar data around the same time or just after)

[Ianpbaker]

Hi mobo10

I've just given this a try and it seems to work fine

Set rs = Server.CreateObject("adodb.recordset")

objCon.execute "Insert into tblTest(field1) Values('32323')"

RS.Open "SELECT @@IDENTITY",objCon,adOpenStatic

Response.Write rs(0)

[tonyenkiducx]

This is a little slower, but its fool proof, and it DOES work with cookies turned of before anyone says that.

Insert into SQL_Table (data, tempfield) Values('"&&"', '"&session.sessionid&"')
Select ID_Field from SQL_TABLE WHERE tempfield = '"&session.sessionid&"'