Output parameter from SQL Server not printing to web form

Question

I have a store procedure

create proc spAddEmployees  
@Name varchar(50),  
@Gender varchar(10),  
@Salary int,  
@EmployeeId int out  
as  
begin  
insert into tblEmployees values (@Name, @Gender, @Salary)  
select EmployeeId = SCOPE_IDENTITY()  
end  

which has an output parameter that tells the user the current scope_identity The markup looks like

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <table style="border: 1px solid black; font-family:Arial">
    <tr>
        <td>
            Employee Name
        </td>
        <td>
            <asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
        </td>
    </tr>        
    <tr>
        <td>
            Gender
        </td>
        <td>
            <asp:DropDownList ID="ddlGender" runat="server">
                <asp:ListItem>Male</asp:ListItem>
                <asp:ListItem>Female</asp:ListItem>
            </asp:DropDownList>
        </td>
    </tr> 
    <tr>
        <td>
            Salary
        </td>
        <td>
            <asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>
        </td>
    </tr>       
    <tr>
        <td colspan="2">
            <asp:Button ID="btnSubmit" runat="server" Text="Submit" 
                onclick="btnSubmit_Click" />
        </td>
    </tr>          
    <tr>
        <td colspan="2">
            <asp:Label ID="lblMessage" runat="server"></asp:Label>
        </td>
    </tr>  
</table>

And the code behind

 public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            //read from the config file so you don't have to hardcode the connection string
            string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            //when you use the using statement the database connection is automatically closed for you
            using(SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("spAddEmployees", con);

                cmd.CommandType = CommandType.StoredProcedure;


                cmd.Parameters.AddWithValue("@Name", txtEmployeeName.Text);
                cmd.Parameters.AddWithValue("@Gender",ddlGender.SelectedValue);
                cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);


                SqlParameter outputParmeter = new SqlParameter();

                outputParmeter.ParameterName = "@EmployeeId";

                outputParmeter.SqlDbType = SqlDbType.Int;
                outputParmeter.Direction = ParameterDirection.Output;

                cmd.Parameters.Add(outputParmeter);

                con.Open();
                cmd.ExecuteNonQuery();

                string EmpId = outputParmeter.Value.ToString();
                lblMessage.Text = "Employee Id = " + EmpId;    


            }

        }
    }

This program should add a new row to the tblEmployees table and then output to the user the output parameter of the stored procedure. It runs and adds the row to the number when the button is clicked, but the output parameter isn't being printed to the screen. Thoughts?

Answer 1

The syntax in the stored procedure isn't right.
You should use

select @EmployeeId = SCOPE_IDENTITY()  
       ^

and also the usual way to retrieve the output parameter is through the command collection. I am not sure that in the process the parameter is replaced somewhere

string EmpId = cmd.Parameters["@EmplyeeId"].Value.ToString();

Answer 2

Perhaps:

select EmployeeId = SCOPE_IDENTITY() 

In the stored procedure should be:

select @EmployeeId = SCOPE_IDENTITY()