Click here to Skip to main content
Rate this: bad
good
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
	)
	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;
        }
Posted 31-Dec-12 22:39pm
Edited 31-Dec-12 22:58pm
v2
Comments
Zoltán Zörgő at 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
good
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)
    Command.Connection.Open();
 
Command.ExecuteNonQuery();
 
return (int)Command.Parameters["@RETURNVALUE"].Value;
  Permalink  
Rate this: bad
good
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.
  Permalink  
Rate this: bad
good
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
 
http://stackoverflow.com/questions/2342806/getting-a-return-value-in-c-sharp-asp-net-from-a-stored-procedure-syntax-issue[^]
 
Thanks
  Permalink  
Rate this: bad
good
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
finally
            {
                //_newCommand.Parameters.Clear();
                if (connectionState == ConnectionState.CloseOnExit && _newCommand.Transaction == null)
                {
                    _newConnection.Close();
                }
            }
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 335
1 OriginalGriff 250
2 Kornfeld Eliyahu Peter 130
3 DamithSL 120
4 Peter Leow 95
0 OriginalGriff 7,355
1 DamithSL 5,199
2 Sergey Alexandrovich Kryukov 4,942
3 Maciej Los 4,906
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web04 | 2.8.141223.1 | Last Updated 1 Jan 2013
Copyright © CodeProject, 1999-2014
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