Retrieve id of newly added row in stored procedure

Hi,

I am trying to return the id of the newly added row after the insertstatement in my stored procedure. I was told to "RETURNSCOPE_IDENTITY()", which i did. The problem is i do not know how to getthis value in asp.net?

I added this code below in my business layer. myDAL refers to an objectof my DAL layer. In my DAL layer, i have a addPara() method which willhelp me dynamically add as many parameters. Someone please advise me onwhat to do. Thanks

myDAL.addPara("@.RETURN_VALUE", , SqlDbType.Int, , ParameterDirection.ReturnValue)

Stored Procedure:
CREATE PROCEDURE [ADDPROMOTION]
(
@.PROMOTIONNAME VARCHAR (100),
@.PROMOSTARTDATE DATETIME,
@.PROMOENDDATE DATETIME,
@.DISCOUNTRATE INT,
@.PROMODESC VARCHAR(100)

)

As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@.PROMOTIONNAME, @.DISCOUNTRATE, @.PROMOSTARTDATE, @.PROMOENDDATE, @.PROMODESC)

-- Now return the InventoryID of the newly inserted record
RETURN SCOPE_IDENTITY()
GOTry @.@.identity

Refer :http://tinyurl.com/5sdht|||

SCOPE_IDENTITY() is definetely a more accurate function than @.@.IDENTITY. Check out Books On Line for more info. To get your code working, create an OUTPUT parameter in the stored proc:

CREATE PROCEDURE [ADDPROMOTION]
(
@.PROMOTIONNAME VARCHAR (100),
@.PROMOSTARTDATE DATETIME,
@.PROMOENDDATE DATETIME,
@.DISCOUNTRATE INT,
@.PROMODESC VARCHAR(100)
@.MSTRPROMID INT OUTPUT
)

As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@.PROMOTIONNAME, @.DISCOUNTRATE, @.PROMOSTARTDATE, @.PROMOENDDATE, @.PROMODESC)
SELECT @.MSTRPROMID = SCOPE_IDENTITY()
-- Now return the InventoryID of the newly inserted record

GO

Now change your front end code to retrieve the ID

check the (2) section ofthis article for sample code.

|||

Look at the @.RETURN_VALUE parameter after you call the stored procedure.

Normally (Without any Dal stuff):

debug.print cmd.parameters("@.RETURN_VALUE).value

Where cmd is your sqlcommand/oledbcommand etc