Getting the id generated by SQL Server for a new record

[Nathan Sokalski]

I am using ASP.NET 2.0 to add records to a database table in an SQL Server
database. The id field is automatically created when a record is added, and
I would like to be able to know what value was assigned to the id field for
use in my ASP.NET application. Is there a way to find out what value was
assigned to the record at the same time I create the record (in otherwords,
I do not want to use a SELECT statement)? Thanks.

 

[Stuart Ainsworth]

I am using ASP.NET 2.0 to add records to a database table in an SQL Server
database. The id field is automatically created when a record is added, and
I would like to be able to know what value was assigned to the id field for
use in my ASP.NET application. Is there a way to find out what value was
assigned to the record at the same time I create the record (in otherwords,
I do not want to use a SELECT statement)? Thanks.

Look in the Books Online for the SCOPE_IDENTITY function. Lots of
good examples there.

 

[Dan Gartner]

I am using ASP.NET 2.0 to add records to a database table in an SQL Server
database. The id field is automatically created when a record is added, and
I would like to be able to know what value was assigned to the id field for
use in my ASP.NET application. Is there a way to find out what value was
assigned to the record at the same time I create the record (in otherwords,
I do not want to use a SELECT statement)? Thanks.

You would want to return the SCOPE_IDENTITY() value as an output
parameter from your DbCommand after you perform the insert.

 

[Manfred Sorg]

You would want to return the SCOPE_IDENTITY() value as an output

You could use the OUTPUT feature of sql server 2005. But in this case
SCOPE_IDENTITY() and @@IDENTITY give back odd results while
IDENT_CURRENT() and inserted.id seem to give correct values.
inserted.id is the way the output feature is meant.

Example:
begin tran
create table tmp (id int identity, xyz varchar)
insert into tmp (xyz) output inserted.id values ('w')
insert into tmp (xyz) output @@identity values ('x')
insert into tmp (xyz) output ident_current('tmp') values ('y')
insert into tmp (xyz) output scope_identity() values ('z')
select * from tmp
drop table tmp
rollback

Results:
1
1
3
3

id xyz
----------- ----
1 w
2 x
3 y
4 z

(4 Zeile(n) betroffen)

 

[Ibrahim Shameeque]

Hi
You can use the output parameter of the stored procedure and return the
value that is being inserted. If you are using identity column then you can
use @@IDENTITY to return the last inserted indentity value.
--

 

[TheSQLGuru]

This method obviously requires using stored procs (which is almost always a
good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
bit of a bind I think. IIRC you can't issue an insert statement and get a
select back out in a single Execute... type command.

 

[Kerry Moorman]

Kevin,

Sure you can, like this:

cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
(@StudentName, @Test1, @Test2); Select Scope_Identity()"

Then:

ID = cmd.ExecuteScalar

Kerry Moorman

 

[TheSQLGuru]

I seem to recall a client trying to do that recently (using ADO classic) and
it not working. Perhaps they missed the semicolon. I will recheck their
attempts and see if that does it.

One additional question since I am not an ADO guru. Does the Select
Scope_identity() not return a single-column single-row result set, which the
executescalar isn't expecting?

 

[Kerry Moorman]

Kevin,

ExecuteScalar returns the first column of the first row in the result set
returned by the query.

Kerry Moorman

 

[Miha Markic]

Right.
BTW one should always use Scope_Identity() and not @@Identity as posted by
Ibrahim.

 

[TheSQLGuru]

I just checked back with the developer that had the issue. He swears that
using VB6 and ADO classic your example fails. Were you using ADOc or
ADO.NET?

 

[Kerry Moorman]

Kevin,

My example was using ADO.Net.

Kerry Moorman

 

[William Vaughn]

The approach in VB6 is virtually identical.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Cor Ligthert[MVP]]

Manfred,

insert into tmp (xyz) output scope_identity() values ('z')
select * from tmp

The OP wrote that he does not want to use the "Select" statement

In my idea at the moment impossible however this seems ignored in any reply
in this messagethread

Cor

 

[TheSQLGuru]

Recalling that I am NOT an ADO guru - despite having a copy of Vaughn's
book - I tried to get this to work. First I found that in ADO 2.8 there is
no ExecuteScalar (at least not that I found). So I tried it using a
recordset, with the following code:

Private Sub Command1_Click()

'here is the table def I used
'use northwind
'go
'create table c (c int identity, b char(1))

' connection, command, and recordset variables
Dim Cnxn As Connection
Dim cmdChange As Command
Dim rs As Recordset

' Open connection
Set Cnxn = New Connection
Cnxn.Open "Provider='sqloledb';Data Source='(local)';Initial
Catalog='Northwind';Integrated Security='SSPI';"

' Create command object
Set cmdChange = New Command
Set cmdChange.ActiveConnection = Cnxn
cmdChange.CommandText = "insert c (b) values ('A'); select
scope_identity() as a"

Set rs = cmdChange.Execute()

rs.MoveFirst

Do While Not rs.EOF
id = rs.Fields(0)
Loop

Cnxn.Close
Set rstTitles = Nothing
Set Cnxn = Nothing
Exit Sub

End Sub


I get the following message when I step on rs.MoveFirst - Operation is not
allowed when the object is closed.

Perhaps it can be done using a parameter with the command execute?

 

[Tibor Karaszi]

Classic ADO treats the "rows affected" message as a recordset. Try adding SET NOCOUNT ON before your
INSERT statement to suppress this.

 

[TheSQLGuru]

THANKS Tibor - that was the trick I was looking for!