Obtaining autonumber value after using recordset AddNew

[Roger Withnell]

I'm inserting a new record into an MS SQL database table
and I want to obtain the new records autonumber
immediately afterwards, as follows:
MadminRS.CursorLocation = adUseServer
MadminRS.CursorType = adOpenKeyset
MadminRS.LockType = adLockOptimistic
MadminRS.Open "NavBar", objConn, , , adCmdTable
MadminRS.AddNew
MadminRS("Url") = Request.Form("Website")
MadminRS("ParentRecNo") = 0
MadminRS("Menu") = "NavBar"
MadminRS("Sequence") = 1000
MadminRS("ButtonName") = "Master Admin"
MadminRS("Link") = "Menu"
MadminRS("Cos") = "Admin"
MadminRS.Update
vRecNo = MadminRS("RecNo") 'the autonumber field
The record is inserted in the table and the autonumber
field is created. vRecNo is blank, however. What is
wrong with this code? I have to obtain the record number
before closing the recordset because I may not be able to
locate the record subsequently.

 

[Aaron Bertrand - MVP]

Don't use AddNew, use an INSERT statement.
http://www.aspfaq.com/2174
http://www.aspfaq.com/2191

 

[Ken Schaefer]

Why are you using these expensive cursors? Keyset is 2x -> 3x more expensive
than other alternatives (eg a stored procedure, or even an inline SQL
statement).

Cheers
Ken


: I'm inserting a new record into an MS SQL database table
: and I want to obtain the new records autonumber
: immediately afterwards, as follows:
: MadminRS.CursorLocation = adUseServer
: MadminRS.CursorType = adOpenKeyset
: MadminRS.LockType = adLockOptimistic
: MadminRS.Open "NavBar", objConn, , , adCmdTable
: MadminRS.AddNew
: MadminRS("Url") = Request.Form("Website")
: MadminRS("ParentRecNo") = 0
: MadminRS("Menu") = "NavBar"
: MadminRS("Sequence") = 1000
: MadminRS("ButtonName") = "Master Admin"
: MadminRS("Link") = "Menu"
: MadminRS("Cos") = "Admin"
: MadminRS.Update
: vRecNo = MadminRS("RecNo") 'the autonumber field
: The record is inserted in the table and the autonumber
: field is created. vRecNo is blank, however. What is
: wrong with this code? I have to obtain the record number
: before closing the recordset because I may not be able to
: locate the record subsequently.
:

 

[Bhaskardeep Khaund]

Hi

You can obtain the autonumber value by the SQL global variable @@IDENTITY. Just use recordet when inserting data and before closing the rescordset use something like

Set id = "select myid = @@IDENTITY from table_name", conn

you will get it in your code. Tell me if you need the full code.

Regards,
Bhaskardeep Khaund

 

[Aaron Bertrand - MVP]

Set id = "select myid = @@IDENTITY from table_name", conn

This is not valid syntax, and you don't select @@IDENTITY from a table, it
is a global variable. With SQL Server 2000, SCOPE_IDENTITY() is far safer
anyway, but it has to be issued on the *same* instance as the initial
query...

 

[Aaron Bertrand - MVP]

And the SCOPE _IDENTITY returns the last column entered in the field
irrespective of the session,

Not true, I suggest you read up in Books Online, about the differences
between @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT(). I think you have a
couple of these mixed up.

 

[Bhaskardeep Khaund]

Hi,

The code is:-


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

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnection
Set objRS = objConn.Execute(strSQL)
varNewID = objRS("NewID")
objConn.Close()
Set objConn = Nothing
Set objRS = 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,
Bhaskardeep Khaund

 

[Roger Withnell]

After inserting the record, I'm using "ident_current
('table')" to establish the autonumber of the record.
Does it guarantee that the result will be the number of
the record I have just inserted or could it be the number
of a record inserted meanwhile? If the latter, does
using "scope_identity()" guarantee that it is the number
of my record? If so, how do I issue it on the "same"
instance as the initial query?

 

[Bob Barrows]

After inserting the record, I'm using "ident_current
('table')" to establish the autonumber of the record.
Does it guarantee that the result will be the number of
the record I have just inserted

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.

or could it be the number
of a record inserted meanwhile? If the latter, does
using "scope_identity()" guarantee that it is the number
of my record?

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

If so, how do I issue it on the "same"
instance as the initial query?

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

 

[Aaron Bertrand - MVP]

After inserting the record, I'm using "ident_current

('table')" to establish the autonumber of the record.
Does it guarantee that the result will be the number of
the record I have just inserted or could it be the number
of a record inserted meanwhile?

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.

of my record? If so, how do I issue it on the "same"
instance as the initial query?

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