Retrieving Return value from stored procedure

Question

I have a stored procedure such as the following:

CREATE PROCEDURE procTest 
    -- Add the parameters for the stored procedure here
    @gameId int = NULL, 
    @memberID int = NULL,
    @mediatorID int = NULL,
    @id int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO tblSiteChallengeMember (gameId, creatorId, mediatorId, completed, dateAdded) VALUES (@gameId, @memberId, @mediatorID, 0, GETDATE())
END
GO

I need to return the ID of tblSiteChallengeMember, but I have no clue how to do it.

I have seen the example 'round about here, but I am afraid that:

SELECT TOP 1 @ProductName=PRODUCTNAME, @Quantity =quantity 
FROM Products P, [Order Details] OD, Orders O, Customers C 
WHERE C.CustomerID = @CustomerID 
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

Wont work, due to the fact that a single user could enter multiple times, and those times may not be unique at all.

How can I get the ID of the last row inserted to my ASP.net page?

---

Updated my Stored Procedure to:

ALTER PROCEDURE [dbo].[procCreateChallengeMember]
    -- Add the parameters for the stored procedure here
    @gameId int = NULL, 
    @memberID int = NULL,
    @mediatorID int = NULL,
    @id int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO tblSiteChallengeMember (gameId, creatorId, mediatorId, completed, dateAdded) VALUES (@gameId, @memberId, @mediatorID, 0, GETDATE())

    SELECT @id = SCOPE_IDENTITY()
END

SQL table is:

GO

/****** Object:  Table [dbo].[tblSiteChallengeMember]    Script Date: 10/17/2012 08:05:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblSiteChallengeMember](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [gameId] [int] NULL,
    [creatorId] [int] NOT NULL,
    [mediatorId] [int] NULL,
    [completed] [tinyint] NULL,
    [dateAdded] [datetime] NULL,
 CONSTRAINT [PK__tblSiteD__3213E83F628FA481] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

aspx.cs is:

SqlConnection db = DataConn.SqlConnection();

db.Open();
SqlTransaction transaction = db.BeginTransaction();

try
{
    int id = 0;

    SqlCommand sqlComm =
            new SqlCommand(
                "procCreateChallengeMember @gameId, @creatorId, @mediatorId, @id", db, transaction) { CommandType = CommandType.Text };

    sqlComm.Parameters.Add(new SqlParameter("@gameId", SqlDbType.Int)).Value = 1;
    sqlComm.Parameters.Add(new SqlParameter("@creatorId", SqlDbType.Int)).Value = 1;
    sqlComm.Parameters.Add(new SqlParameter("@mediatorId", SqlDbType.Int)).Value = 1;
    SqlParameter param = new SqlParameter("@id", SqlDbType.Int)
        {
            Direction = ParameterDirection.Output
        };

    sqlComm.Parameters.Add(param);
    sqlComm.ExecuteNonQuery();

    //if(param.Value != DBNull.Value)
    //{
        id = Convert.ToInt32(param.Value);
        Response.Write("One: " + id + "<br/>");
    //}
    transaction.Commit();
    //Response.Write("Two: " + sqlComm.Parameters["expr"].Value + "<br/>");

}
catch (Exception ess)
{
    transaction.Rollback();
    Response.Write(ess.Message);
}

Now getting the error Object cannot be cast from DBNull to other types. (When I remove that IF statement)

Answer 1

Finally found a solution on my own (that works).

SQL:

-- =============================================
-- Author:      Darren Whitfield
-- Create date: 7 October 2012
-- Description: This will create a new challenge holder, and return its ID
-- =============================================
ALTER PROCEDURE [dbo].[procCreateChallengeMember]
    -- Add the parameters for the stored procedure here
    @gameId int = NULL, 
    @memberID int = NULL,
    @mediatorID int = NULL,
    @id int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    -- SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO tblSiteChallengeMember (gameId, creatorId, mediatorId, completed, dateAdded) VALUES (@gameId, @memberId, @mediatorID, 0, GETDATE())

    SET @id = @@IDENTITY
    SELECT @id AS iden
END

aspx.cs:

string identity = "";
        SqlConnection db = DataConn.SqlConnection();

        db.Open();
        SqlTransaction transaction = db.BeginTransaction();

        try
        {

            SqlCommand sqlComm =
                    new SqlCommand(
                        "procCreateChallengeMember @gameId, @creatorId, @mediatorId, @id", db, transaction) { CommandType = CommandType.Text };

            sqlComm.Parameters.Add(new SqlParameter("@gameId", SqlDbType.Int)).Value = 2;
            sqlComm.Parameters.Add(new SqlParameter("@creatorId", SqlDbType.Int)).Value = 1;
            sqlComm.Parameters.Add(new SqlParameter("@mediatorId", SqlDbType.Int)).Value = 1;
            sqlComm.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { Direction = ParameterDirection.Output });

            using (SqlDataReader rdr = sqlComm.ExecuteReader())
            {
                while (rdr.Read())
                {
                    identity = rdr["iden"].ToString();
                }
            }

            Response.Write("One: " + identity + "<br/>");

            transaction.Commit();

        }
        catch (Exception ess)
        {
            transaction.Rollback();
            Response.Write(ess.Message);
        }

Returns ID
Thanks to all those who took their time to put in their thoughts and opinions. Without it, I would of never made it.

Answer 2

"How can I get the ID of the last row inserted to my ASP.net page?"

SELECT @id = SCOPE_IDENTITY()

Get your stored procedure that inserts the row to return it.

EDIT

In response to follow up problem.

You need to make your SqlCommand aware that a parameter is on it's way out.

   // Just below where you added your other params
   SqlParameter outputParam = new SqlParameter("@id", SqlDbType.Int)
   { 
      Direction = ParameterDirection.Output 
   };

   sqlComm.Parameters.Add(outputParam);

   sqlComm.ExecuteNonQuery();

Answer 3

Getting it from C# after using SCOPE_IDENTITY in your PROC

SqlParameter param = new SqlParameter("@Id", SqlType.Int);
param.Direction = ParameterDirection.Output;

command.Parameters.Add(param);
command.ExecuteNonQuery();

int id = 0;
if(param.Value != null)
   id = Convert.ToInt32(param.Value)

Answer 4

IF (@@error <> 1)
    BEGIN
    SELECT @id=scope_identity()
    END
ELSE
    BEGIN
    set @id=0
    RETURN 0 
    END