Return Value From Stored Procedure To C#

I got same problem in one more stored procedure. I want to add leave request to a SQL Server 2008 database. I can run a stored procedure successfully but from page I can't return

Solution 1:

Please Edit Your Code as:

Remove this line:

 cmd.Parameters.Add("@Status", SqlDbType.Int).Value = status;

Add this Code:

SqlParameterabc= cmd.Parameters.Add("@Status", SqlDbType.Int);
 abc.Direction = ParameterDirection.Output;

Then you can get your Status result in abc.

Hope this will help you.

Solution 2:

In Stored Procedure

RETURN@status

in C#

string retunvalue = (string)sqlcomm.Parameters["@status"].Value;

Solution 3:

publicint? AddNewDesigDataSql(string desig_name, string Details, int AddedBy)
    {
        int? status = 0;
        DataTabledt=newDataTable();
        try
        {
            SqlConnectionsqlConnection=newSqlConnection();
            stringconString= Connection.GetConnection;

            using (SqlConnectioncon=newSqlConnection(conString))
            {
                con.Open();

                using (SqlCommandcmd=newSqlCommand("SP_Dedignation_Add", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter param1=cmd.Parameters.Add("@Desig_Name", SqlDbType.varchar,500).Value = desig_name;
 param1.Direction = ParameterDirection.Input;

                    SqlParameter param2=cmd.Parameters.Add("@Desig_Desc", SqlDbType.varchar,500).Value = Details;
param2.Direction = ParameterDirection.Input;

                    SqlParameter param3=cmd.Parameters.Add("@Desig_AddedBy", SqlDbType.int,8).Value = AddedBy;
param3.Direction = ParameterDirection.Input;

                    SqlParameter param4=cmd.Parameters.Add("@Status", SqlDbType.Int).Value = status;

param4.Direction = ParameterDirection.Output;


                    cmd.ExecuteNonQuery();
                    con.Close();

                    status = (int)param4.Value;

                    using (SqlDataAdapterda=newSqlDataAdapter(cmd))
                    {
                        DataSetds=newDataSet();
                        da.Fill(ds);
                        dt = ds.Tables[0];
                    }
                }
            }
        }
        catch (SqlException ex)
        {

        }
        return status;
    }