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

Retrieve Identity Field Value from a stored procedure into a form

OK first time poster, so hello everyone in advance. Right i'm sure this is a simple problem to solve, but I'm just getting the hang of SQL 2000. What I've got is a form where I input the values and then through a procedure these values are inserted into a table. That's fine. However I now need to open a subform which is linked by the ID field created through the first procedure. How do I retrieve that value back into the form??

The procedure code is

CREATE PROCEDURE InsertFamilyDetails

@.CarerIDvarChar(6),
@.FamilyNamevarChar(30),
@.Address1varChar(30),
@.Address2varChar(30),
@.Address3varChar(30),
@.PostCodeMain varChar(4),
@.PostCodeSubvarChar(30),
@.PhoneNovarChar(16),
@.LocalTransport varChar(200),
@.Leisure varChar(200),
@.School varChar(200),
@.RulesvarChar(250),
@.Resultint OUTPUT

AS

DECLARE @.FamilyID int;

BEGIN TRANSACTION

-- Insert New Family

SELECT @.FamilyID=@.@.Identity

INSERT INTO tblWfsFamilyDetails

(intCarerID,txtFamilyName,txtAddress1,txtAddress2, txtAddress3,txtPostCodeMain,txtPostCodeSub,txtPhon eNo,memoLocalTransport, memoLeisure, memoSchool,memoRules)

VALUES

(@.CarerID,@.FamilyName,@.Address1,@.Address2,@.Address 3,@.PostCodeMain,@.PostCodeSub,@.PhoneNo,@.LocalTransp ort,@.Leisure,@.School,@.Rules)

IF @.RESULT<1

BEGIN

ROLLBACK TRANSACTION;

RETURN -1

END

SET @.RESULT=1

COMMIT TRANSACTION
GO

Whereas, the VB Code is

Private Sub cmdInsert_Click()
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "insertFamilyDetails"

' FieldNames Assigned to FormControls

cmd.Parameters.Append cmd.CreateParameter("CarerID", adInteger, adParamInput, 6, txtCarerID)
cmd.Parameters.Append cmd.CreateParameter("FamilyName", adVarChar, adParamInput, 30, txtFamilyName)
cmd.Parameters.Append cmd.CreateParameter("Address1", adVarChar, adParamInput, 30, txtAddress1)
cmd.Parameters.Append cmd.CreateParameter("Address2", adVarChar, adParamInput, 30, txtAddress2)
cmd.Parameters.Append cmd.CreateParameter("Address3", adVarChar, adParamInput, 30, txtAddress3)
cmd.Parameters.Append cmd.CreateParameter("PostCodeMain", adVarChar, adParamInput, 4, txtPostCodeMain)
cmd.Parameters.Append cmd.CreateParameter("PostCodeSub", adVarChar, adParamInput, 4, txtPostCodeSub)
cmd.Parameters.Append cmd.CreateParameter("PhoneNo", adVarChar, adParamInput, 12, txtPhoneNo)
cmd.Parameters.Append cmd.CreateParameter("LocalTransport", adVarChar, adParamInput, 200, memoLocalTransport)
cmd.Parameters.Append cmd.CreateParameter("Leisure", adVarChar, adParamInput, 200, memoLeisure)
cmd.Parameters.Append cmd.CreateParameter("School", adVarChar, adParamInput, 200, memoSchool)
cmd.Parameters.Append cmd.CreateParameter("Rules", adVarChar, adParamInput, 200, memoRules)
cmd.Parameters.Append cmd.CreateParameter("Result", adInteger, adParamOutput) ' OutPut Returns Result Parameter a Value

cmd.Execute

Res = cmd("Result")

If (Res = 1) Then
MsgBox "Family Inserted Successfully", , "Insert Family"
End If

Set cmd.ActiveConnection = Nothing

End Sub

Apologise for wasting your time if this something obvious, but like I say I'm learning.pretty simple - in your stored procedure immediately after the line values(....) do this: set @.retval = @.@.IDENTITY

that will automatically put the new ID into the variable @.retval. Then you can do
Set @.RESULT = @.retval|||

Quote:

Originally Posted by scripto

pretty simple - in your stored procedure immediately after the line values(....) do this: set @.retval = @.@.IDENTITY

that will automatically put the new ID into the variable @.retval. Then you can do
Set @.RESULT = @.retval

scope_identity() function is a better choice than @.@.IDENTITY as there might be some triggers defined on tblWfsFamilyDetails table.