title: Get Return Value from Stored procedure in asp.net
body: <p>i have a stored procedure </p>
  
  <pre><code>ALTER PROC TESTLOGIN
      @UserName varchar(50),
      @password varchar(50)
  As
  Begin
      declare @return int;
  
      set @return  = (SELECT COUNT(*) 
                      FROM CPUser 
                      WHERE UserName = @UserName 
                      AND Password = @password);
  
      return @return;
  End
  </code></pre>
  
  <p>and in c#</p>
  
  <pre><code>SqlConnection con = db.con;
  SqlCommand cmd = new SqlCommand("TESTLOGIN", con);
  cmd.CommandType = CommandType.StoredProcedure;
  
  SqlParameter parm = new SqlParameter("@return", SqlDbType.Int);
  parm.Direction = ParameterDirection.ReturnValue;
  
  cmd.Parameters.Add(parm);
  cmd.Parameters.Add(new SqlParameter("@UserName", txtUserName.Text.ToString().Trim()));
  cmd.Parameters.Add(new SqlParameter("@password", txtPassword.Text.ToString().Trim()));
  
  cmd.ExecuteNonQuery();
  con.Close();
  
  int id = Convert.ToInt32(parm.Value);
  </code></pre>
  
  <p>but it always return 0. Please help me to solve this problem</p>