How to get Specific return value from stored procedure in asp.net

See more:

C#

ASP.NET

stored-procedure

Hello all, can anyone tell me how can i get the return value from a stored procedure.
I am using a databasehelper in my dataaccesslayer. My storedProcedure is as follows,

ALTER proc [dbo].[spi_UserExamHistoryInsert]
(
	@GoForExamID int,
	@ExamTakenDate date,
	@NoOfAttempt int,
	@NoOfLeft int,
	@NoOfCorrect int,
	@OptionsScore int,
	@FreeTextScore int,
	@IpAddress varchar(30),
	@ExamScheduleID int,
	@IsExamSubmitted bit,
	@ReturnID int output,
	@ErrorCode int output
	)
	as
	begin 
	set nocount on
	insert into UserExamHistory values(@GoForExamID,@ExamTakenDate,@NoOfAttempt,@NoOfLeft,@NoOfCorrect,
										@OptionsScore,@FreeTextScore,@IpAddress,@ExamScheduleID,@IsExamSubmitted)
	set @ReturnID=SCOPE_IDENTITY() 
	return @ReturnID
	set @ErrorCode=@@ERROR
	end

i just want to retrieve the current inserted ID in my asp.net web application. This is what i have done so far...

public static bool Insert(UserExamRecordBO objUserExamRecordBO, out int ReturnVal)
        {
            bool bIsInserted = false;
            string strExamTakenDate = null;
            int nReturnID = 0;

            DatabaseHelper dbHelper = new DatabaseHelper();
            try
            {
                dbHelper.AddParameter("@GoForExamID", objUserExamRecordBO.GoForExamID);

                strExamTakenDate = objUserExamRecordBO.ExamTakenDate.ToString("yyyy-MM-dd");

                if (objUserExamRecordBO.ExamTakenDate != null)
                    dbHelper.AddParameter("@ExamTakenDate", strExamTakenDate);
                else
                    dbHelper.AddParameter("@ExamTakenDate", DBNull.Value);

                dbHelper.AddParameter("@NoOfAttempt",objUserExamRecordBO.NoOfAttempt);
........................
dbHelper.AddParameter("@ReturnID",System.Data.ParameterDirection.Output);
               **** nReturnID = (int)dbHelper.Command.Parameters["@ReturnID"].Value;
                
                dbHelper.AddParameter("@ErrorCode", -1, System.Data.ParameterDirection.Output);

                dbHelper.ExecuteNonQuery("spi_UserExamHistoryInsert", ref bIsInserted);
               **** //nReturnID = (int)dbHelper.ExecuteScalar("spi_UserExamHistoryInsert", ref bIsInserted);

                ****//nReturnID =(int)dbHelper.Command.Parameters["@ReturnID"].Value;

The First line of stars returns an integer which is clearly not the ID that was just inserted....the Second line of stars returns a 0...and the third line of stars after the executenonquery...throws an exception....the parameter collection does not contain the parameter @ReturnID...this is prbly because in the executenonquery routine, the command clears all it's parameters...for that reason i put the line above the executenonquery(the First line of starts). Plz, if you have any ideas how to get the Inserted ID?(for this case)....or maybe iam doing something wrong...

DatabaseHelper

 public int AddParameter(string name, object value, ParameterDirection direction)
        {
            int result = 0;
            DbParameter p = _newFactory.CreateParameter();

            p.ParameterName = name;
            p.Value = value;
            p.Direction = direction;

            result = _newCommand.Parameters.Add(p);

            return result;
        }

public int ExecuteNonQuery(string sqlStatement, CommandType commandType, ConnectionState connectionState, ref bool executionSucceeded)
        {
            _newCommand.CommandText = sqlStatement;
            _newCommand.CommandType = commandType;
            int i = 0;
            try
            {
                if (_newConnection.State == System.Data.ConnectionState.Closed)
                {
                    _newConnection.Open();
                }
                i = _newCommand.ExecuteNonQuery();
                executionSucceeded = true;
            }
            catch (Exception ex)
            {
                executionSucceeded = false;
                ProcessException(ex);
            }
            finally
            {
                _newCommand.Parameters.Clear();
                if (connectionState == ConnectionState.CloseOnExit && _newCommand.Transaction == null)
                {
                    _newConnection.Close();
                }
            }

            return i;
        }