Basic question: returning @@IDENTITY from stored procedure...

[Blue Streak]

Hi, Folks!

This is a basic question. I'm just having a brain freeze and can't
figure out how to it.

I have a stored procedure like (SQL2005):
CREATE PROCEDURE [dbo].[sp_INSERT] (...)

AS

INSERT INTO Table (...)
VALUES (...)

SELECT @@IDENTITY AS ID


and in my behind code I have some thing like:
Dim sqltmp As String
....

sqltmp = "sp_INSERT ..."
SqlDataSource.InsertCommand = sqltmp
SqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text

'Commit to database ... or bust!
Try
MessageLabel.Text = SqlDataSource.Insert() & " record inserted."
Catch ex As Exception
MessageLabel.Text = sqltmp & " " & ex.Message
End Try
....


How do I capture the @@IDENTITY from the returned ID field after an
INSERT command?

TIA...

 

[Jesse Houwing]

Hello Blue,

Hi, Folks!

This is a basic question. I'm just having a brain freeze and can't
figure out how to it.

I have a stored procedure like (SQL2005):
CREATE PROCEDURE [dbo].[sp_INSERT] (...)
AS

INSERT INTO Table (...)
VALUES (...)
SELECT @@IDENTITY AS ID

and in my behind code I have some thing like:
Dim sqltmp As String
...
sqltmp = "sp_INSERT ..."
SqlDataSource.InsertCommand = sqltmp
SqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text
'Commit to database ... or bust!
Try
MessageLabel.Text = SqlDataSource.Insert() & " record inserted."
Catch ex As Exception
MessageLabel.Text = sqltmp & " " & ex.Message
End Try
...

How do I capture the @@IDENTITY from the returned ID field after an
INSERT command?

The MSDN Documentation actually has a pretty good example for this.

 

[Jesse Houwing]

Hello Blue,

Hi, Folks!

This is a basic question. I'm just having a brain freeze and can't
figure out how to it.

I have a stored procedure like (SQL2005):
CREATE PROCEDURE [dbo].[sp_INSERT] (...)
AS

INSERT INTO Table (...)
VALUES (...)
SELECT @@IDENTITY AS ID

and in my behind code I have some thing like:
Dim sqltmp As String
...
sqltmp = "sp_INSERT ..."
SqlDataSource.InsertCommand = sqltmp
SqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text
'Commit to database ... or bust!
Try
MessageLabel.Text = SqlDataSource.Insert() & " record inserted."
Catch ex As Exception
MessageLabel.Text = sqltmp & " " & ex.Message
End Try
...

How do I capture the @@IDENTITY from the returned ID field after an
INSERT command?

The MSDN Documentation actually has a pretty good example for this.

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insertcommand.aspx

Scroll all the way to the bottom to see it. (it uses an out parameter)

 

[Alexey Smirnov]

Hi, Folks!

This is a basic question.  I'm just having a brain freeze and can't
figure out how to it.

I have a stored procedure like (SQL2005):
CREATE PROCEDURE [dbo].[sp_INSERT] (...)

AS

    INSERT INTO Table (...)
    VALUES (...)

    SELECT @@IDENTITY AS ID

and in my behind code I have some thing like:
Dim sqltmp As String
...

sqltmp = "sp_INSERT ..."
SqlDataSource.InsertCommand = sqltmp
SqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text

'Commit to database ... or bust!
Try
    MessageLabel.Text = SqlDataSource.Insert() & " record inserted."
Catch ex As Exception
    MessageLabel.Text = sqltmp & " " & ex.Message
End Try
...

How do I capture the @@IDENTITY from the returned ID field after an
INSERT command?

TIA...

You can also do it without using SqlDataSource
http://msdn.microsoft.com/en-us/library/37hwc7kt.aspx

Dim sqlConnection1 As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand
Dim returnValue As Object

cmd.CommandText = "StoredProcedureName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()

returnValue = cmd.ExecuteScalar()

sqlConnection1.Close()

 

[Blue Streak]

Hi, Folks!

This is a basic question.  I'm just having a brain freeze and can't
figure out how to it.

I have a stored procedure like (SQL2005):
CREATE PROCEDURE [dbo].[sp_INSERT] (...)

AS

    INSERT INTO Table (...)
    VALUES (...)

    SELECT @@IDENTITY AS ID

and in my behind code I have some thing like:
Dim sqltmp As String
...

sqltmp = "sp_INSERT ..."
SqlDataSource.InsertCommand = sqltmp
SqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text

'Commit to database ... or bust!
Try
    MessageLabel.Text = SqlDataSource.Insert() & " record inserted."
Catch ex As Exception
    MessageLabel.Text = sqltmp & " " & ex.Message
End Try
...

How do I capture the @@IDENTITY from the returned ID field after an
INSERT command?

TIA...

You can also do it without using SqlDataSourcehttp://msdn.microsoft.com/en-us/library/37hwc7kt.aspx

Dim sqlConnection1 As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand
Dim returnValue As Object

cmd.CommandText = "StoredProcedureName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()

returnValue = cmd.ExecuteScalar()

sqlConnection1.Close()- Hide quoted text -

- Show quoted text -

Thanks for the feedback... I thought it was something simple.

=P

 

[Gregory A. Beamer]

(e-mail address removed):

How do I capture the @@IDENTITY from the returned ID field after an
INSERT command?

You can select it out:

SELECT @@IDENTITY

and then pull from the result set. If this is all you are returning
ExecuteScalar works fine.

You can also assign the value to the return variable:

RETURN @@IDENTITY


NOTE: Scope_Identity() is better than @@IDENTITY in most cases

The Return value is an ouput variable that is always there with SQL
Server. To capture, create a parameter called RETURN_VALUE and set as an
output variable (direction out).


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************