Getting a returned value from stored procedure is always giving 0

See more:

C#

SQL-Server

Hi ,

I am trying to insert into employees table in northwind and return the scope_identity but it is always giving me 0 and this is my code :

in stored procedure:

ALTER PROCEDURE [dbo].[InsertmployeeAndGetScope] 
	-- Add the parameters for the stored procedure here
	@LastName nvarchar(20)=null,
	@FirstName nvarchar(10),
	@EmplyeeId int= null 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 dbo.Employees(LastName,FirstName) values(@LastName,@FirstName);
	set @EmplyeeId=SCOPE_IDENTITY();
	return @EmplyeeId;
END

and the c# code:

public int insertemployeeandgetid()
        {
            string connStr = ConfigurationManager.ConnectionStrings["north"].ToString();
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand dCmd = new SqlCommand("InsertmployeeAndGetScope", conn);
            dCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dCmd.Parameters.AddWithValue("@LastName", "zarif");
                dCmd.Parameters.AddWithValue("@FirstName", "mhamad");
                dCmd.Parameters.AddWithValue("@EmplyeeId", null);
                return Convert.ToInt32(dCmd.ExecuteScalar());
            }
            catch
            {
                throw;
            }
            finally
            {
                dCmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

So what is the problem ?