Return an unique identifier to an ASP.NET page to send it as a parameter into another stor

Hi !

I have a problem with the unique identifier and don't know how to solve it.

I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.

I tried withSCOPE_IDENTITYbut i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.

Dim

cmdAsNew SqlCommand

cmd.CommandText ="Insertar_Contacto"

cmd.CommandType = CommandType.StoredProcedure

cmd.Connection = connect

Thanks!!

Create a parameter of type OUTPUT in your stored proc, assign the value of SCOPE_IDENTITY() to it after your insert, create the same parameter on your application layer, set its direction to OUTPU and retrieve the value. Search my posts here for some sample code as I posted some code for someone, in the last few days.|||

Have a look at

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.usp_Test_Insert
(
@.IsAdmin bit,
@.JobTitle nvarchar (50),
@.Name nvarchar (50),
@.DateCreated datetime,
@.RETURN INT OUTPUT,
@.IDENTITY INT OUTPUT
) AS
-- Purpose:
-- Insert record into Test table
-- Parameters:
-- IsAdmin -
-- JobTitle -
-- Name -
-- DateCreated -
-- RETURN - Zero or Error Code
-- IDENTITY - Identity of inserted row
-- History:
-- 11Jan2006 ACERXP\Administrator Original coding
SET NOCOUNT ON
INSERT INTO Test( IsAdmin, JobTitle, Name, DateCreated)
VALUES (
@.IsAdmin,
@.JobTitle,
@.Name,
@.DateCreated)
SELECT @.RETURN = @.@.error, @.IDENTITY = SCOPE_IDENTITY()
RETURN
----- this is the end ------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Retrieving Scope_Entity or Identity from an SQL Insert

The following code inserts a record into a table. I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other inserts. Can someone offer assistance in handling this... I tried several alternatives that I found on the internet but none seem to work...

Thanks!

Dim objConn3As SqlConnection
Dim mySettings3AsNew NameValueCollection
mySettings3 = AppSettings
Dim strConn3AsString
strConn3 = mySettings3("connString")
objConn3 =New SqlConnection(strConn3)
Dim strInsertPatientAsString
Dim cmdInsertAs SqlCommand
Dim strddlSexAsString
Dim strddlPatientStateAsString
Dim rowsAffectedAsInteger

strddlSex = ddlSex.SelectedItem.Text
strddlPatientState = ddlPatientState.SelectedItem.Text

strInsertPatient ="Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _
"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _
"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _
"Today_Date_Year) Values (@.UserID, @.Accession, @.FirstName, @.MI, @.LastName, @.MedRecord, " & _
"'" & strddlSex &"', @.DOB, @.Address1, @.Address2, @.City, @.Suite , '" & strddlPatientState &"', " & _
"@.ZIP, @.HomeTelephone, @.OutsideNYC, @.ClinicalImpression, @.Today_Date_Month, @.Today_Date_Day, " & _
"@.Today_Date_Year)SELECT @.@.IDENTITY AS NewID SET NOCOUNT OFF"

cmdInsert =New SqlCommand(strInsertPatient, objConn3)

cmdInsert.Parameters.Add("@.UserID","Joe For Now")
cmdInsert.Parameters.Add("@.Accession", Accession.Text)
cmdInsert.Parameters.Add("@.LastName", LastName.Text)
cmdInsert.Parameters.Add("@.MI", MI.Text)
cmdInsert.Parameters.Add("@.FirstName", FirstName.Text)
cmdInsert.Parameters.Add("@.MedRecord", MedRecord.Text)
cmdInsert.Parameters.Add("@.ddlSex", strddlSex)
cmdInsert.Parameters.Add("@.DOB", DOB.Text)
cmdInsert.Parameters.Add("@.Address1", Address1.Text)
cmdInsert.Parameters.Add("@.Address2", Address2.Text)
cmdInsert.Parameters.Add("@.City", City.Text)
cmdInsert.Parameters.Add("@.Suite", Suite.Text)
cmdInsert.Parameters.Add("@.strddlPatientState", strddlPatientState)
cmdInsert.Parameters.Add("@.ZIP", zip.Text)
cmdInsert.Parameters.Add("@.HomeTelephone", Phone.Text)
cmdInsert.Parameters.Add("@.OutsideNYC", OutsideNYC.Text)
cmdInsert.Parameters.Add("@.ClinicalImpression", ClinicalImpression.Text)
cmdInsert.Parameters.Add("@.Today_Date_Month", Today_Date_Month.Text)
cmdInsert.Parameters.Add("@.Today_Date_Day", Today_Date_Day.Text)
cmdInsert.Parameters.Add("@.Today_Date_Year", Today_Date_Year.Text)

objConn3.Open()
cmdInsert.ExecuteNonQuery()
objConn3.Close()

Try this - a zillion ways to get Scope_Identity back:http://www.mikesdotnetting.com/Article.aspx?ArticleID=54