Click here to Skip to main content
11,927,711 members (57,219 online)
Rate this:
Please Sign up or sign in to vote.
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
	set nocount on
	insert into UserExamHistory values(@GoForExamID,@ExamTakenDate,@NoOfAttempt,@NoOfLeft,@NoOfCorrect,
	set @ReturnID=SCOPE_IDENTITY() 
	return @ReturnID
	set @ErrorCode=@@ERROR

i just want to retrieve the current inserted ID in my 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();
                dbHelper.AddParameter("@GoForExamID", objUserExamRecordBO.GoForExamID);
                strExamTakenDate = objUserExamRecordBO.ExamTakenDate.ToString("yyyy-MM-dd");
                if (objUserExamRecordBO.ExamTakenDate != null)
                    dbHelper.AddParameter("@ExamTakenDate", strExamTakenDate);
                    dbHelper.AddParameter("@ExamTakenDate", DBNull.Value);
               **** 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...

 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;
                if (_newConnection.State == System.Data.ConnectionState.Closed)
                i = _newCommand.ExecuteNonQuery();
                executionSucceeded = true;
            catch (Exception ex)
                executionSucceeded = false;
                if (connectionState == ConnectionState.CloseOnExit && _newCommand.Transaction == null)
            return i;
Posted 31-Dec-12 22:39pm
Edited 31-Dec-12 22:58pm
Zoltán Zörgő 1-Jan-13 3:48am
"I am using a databasehelper in my dataaccesslayer." And how exactly should we know, how this helper is implemented?
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

try this:
//Setup SPROC
Command = new SqlCommand();
Command.Connection = Connection;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "abc";
Command.Parameters.Add(new SqlParameter("@pcode", SqlDbType.VarChar,5000));
Command.Parameters.Add(new SqlParameter("@RETURNVALUE", SqlDbType.Int));
Command.Parameters["@pcode"].Value = abcValue.pcode;
Command.Parameters["@RETURNVALUE"].Direction = ParameterDirection.Output;
if (Command.Connection.State != ConnectionState.Open)
return (int)Command.Parameters["@RETURNVALUE"].Value;
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

If you want to get the primary key value, you can always use identity value.
Here[^] is a good example that accomplishes this task.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

Try to use this
cmdHeader.Parameters.Add("@ReturnValue", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;

take reference from following link[^]

Rate this: bad
Please Sign up or sign in to vote.

Solution 4

thanyou everyone for chipping in your solutions....i solved the problem myself...the problem was with the databasehelper itself...the executenonquery() function of the databasehelper cleared all the function that was why my return parameter as not being recognized....this is what i did in the executenonquery() function
                if (connectionState == ConnectionState.CloseOnExit && _newCommand.Transaction == null)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web03 | 2.8.151126.1 | Last Updated 1 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100