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

B

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

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

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)
 
G

Guest

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()
 
B

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] (...)

    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?

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
 
G

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top