Hi,

The code is:-


<%
strSQL =3D "SET NOCOUNT ON INSERT INTO tblName(TextField, NumberField)
VALUES('ABC', 123) SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"

Set objConn =3D Server.CreateObject("ADODB.Connection")
objConn.Open strConnection
Set objRS =3D objConn.Execute(strSQL)
varNewID =3D objRS("NewID")=20
objConn.Close()
Set objConn =3D Nothing
Set objRS =3D Nothing
%>

And the SCOPE _IDENTITY returns the last column entered in the field =
irrespective of the session, if another column is inserted into the =
database between the column you have inserted and sending the autonumber =
column value, the value retrieved would be wrong.


Regards,

[quoted text, click to view]

No. From BOL:
Returns the last identity value generated for a specified table in any
session and any scope.

So you may wind up getting the ID of a row inserted by some other process.

[quoted text, click to view]

Yes. Again from BOL:
[SCOPE_IDENTITY] ... Returns the last IDENTITY value inserted into an
IDENTITY column in the same scope. A scope is a module -- a stored
procedure, trigger, function, or batch. Thus, two statements are in the same
scope if they are in the same stored procedure, function, or batch.

I think this is pretty clear ...


[quoted text, click to view]

As always, I suggest using a stored procedure:

Using Query Analyzer, run this script (adapt it to your table of course):
CREATE PROCEDURE InsRow (
@TextCol varchar(50),
@IntCol int) AS
SET NOCOUNT ON
INSERT INTO tblName(TextCol, IntCol)
VALUES(@TextCol,@IntCol)
SELECT SCOPE_IDENTITY As [NewID]

Then in ASP:

dim rs, cn
Set cn= Server.CreateObject("ADODB.Connection")
cn.Open strConnection
Set rs = Server.CreateObject("ADODB.Recordset")
cn.InsRow "ABC",123,rs

Of course, this is inefficient, since it is using a heavy recordset object
to return a single value to the client. My preference is to use an output
parameter, retrieving its value using a Command object in ASP:

CREATE PROCEDURE InsRow (
@TextCol varchar(50),
@IntCol int,
@NewID int output) AS
SET NOCOUNT ON
INSERT INTO tblName(TextCol, IntCol)
VALUES(@TextCol,@IntCol)
SET @NewID = SCOPE_IDENTITY

dim cn,cmd,newID, params
Set cn= Server.CreateObject("ADODB.Connection")
cn.Open strConnection
Set cmd= Server.CreateObject("ADODB.Command")
With cmd
.CommandText = "InsRow"
.CommandType = adCmdStoredProc
Set .ActiveConnection = cn
Set params = .Parameters
params.Append .CreateParameter("RETURN_VALUE", adInteger, _
adParamReturnValue)
params.Append .CreateParameter("@TextCol", adVarChar, _
adParamInput,50,"ABC")
params.Append .CreateParameter("@IntCol", adInteger, _
adParamInput,,123)
params.Append .CreateParameter("@NewID", adInteger, _
adParamOutput)
.Execute ,,adExecuteNoRecords
End With
newID = params(3).value

Admittedly, it's more code, and it can be tricky to write, but it is more
efficient than using a recordset. The trickiness can be alleviated by using
a code generator, such as the one I wrote which is available here:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear


But, if you have some sort of phobia about using stored procedures, you can
send a string of batched commands to SQL Server so they all get executed in
the same scope. You can use what Bhaskardeep posted:

strSQL = "SET NOCOUNT ON INSERT INTO tblName(TextField, NumberField)
VALUES('ABC', 123) SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"

Just substitute "SCOPE_IDENTITY" for "@@IDENTITY"

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[quoted text, click to view]

IDENT_CURRENT returns the most recent IDENTITY value generated for that
table. That might have been yours, or it might have been someone else's.

SCOPE_IDENTITY() returns the most recent IDENTITY value generated by *you*.
Unlike @@IDENTITY, this does not include the IDENTITY value generated by a
trigger on the table that generated the IDENTITY value you are interested
in.

SCOPE_IDENTITY() is safest, @@IDENTITY should be used if you need to support
SQL Server 7.0, and IDENT_CURRENT() should only be used if you're curious
what the *current* value is (and don't want to use SELECT
MAX(identityColumn), not if you're interested in finding out what you just
did.

[quoted text, click to view]

sql = "SET NOCOUNT ON; " & _
"INSERT table(col) VALUES(vals);" & _
"SELECT SCOPE_IDENTITY()"
set rs = conn.execute(sql)
response.write rs(0)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/