Retrieving Return value from stored procedure

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)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

“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();

Method 2

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

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

Method 4

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0