[c#] Get Return Value from Stored procedure in asp.net

i have a stored procedure

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

and in c#

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);

but it always return 0. Please help me to solve this problem

If you want to to know how to return a value from stored procedure to Visual Basic.NET. Please read this tutorial: How to return a value from stored procedure

I used the following stored procedure to return the value.

CREATE PROCEDURE usp_get_count

AS
BEGIN
 DECLARE @VALUE int;

 SET @VALUE=(SELECT COUNT(*) FROM tblCar);

 RETURN @VALUE;

END
GO

Do it this way (make necessary changes in code)..

            SqlConnection con = new SqlConnection(GetConnectionString());
            con.Open();
            SqlCommand cmd = new SqlCommand("CheckUser", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("username", username.Text);
            SqlParameter p2 = new SqlParameter("password", password.Text);
            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            SqlDataReader rd = cmd.ExecuteReader();
            if(rd.HasRows)
            {
                //do the things
            }
            else
            {
                lblinfo.Text = "abc";
            }

you can try this.Add the parameter as output direction and after executing the query get the output parameter value.

  SqlParameter parmOUT = new SqlParameter("@return", SqlDbType.Int);
  parmOUT.Direction = ParameterDirection.Output;
  cmd.Parameters.Add(parmOUT);
  cmd.ExecuteNonQuery();
  int returnVALUE = (int)cmd.Parameters["@return"].Value;

Procedure never returns a value.You have to use a output parameter in store procedure.

ALTER PROC TESTLOGIN
@UserName   varchar(50),
@password   varchar(50)
@retvalue int output
 as
 Begin
    declare @return     int 
    set @return  = (Select COUNT(*) 
    FROM    CPUser  
    WHERE   UserName = @UserName AND Password = @password)

   set @retvalue=@return
  End

Then you have to add a sqlparameter from c# whose parameter direction is out. Hope this make sense.

2 things.

• The query has to complete on sql server before the return value is sent.

• The results have to be captured and then finish executing before the return value gets to the object.

In English, finish the work and then retrieve the value.

this will not work:

                cmm.ExecuteReader();
                int i = (int) cmm.Parameters["@RETURN_VALUE"].Value;

This will work:

                        SqlDataReader reader = cmm.ExecuteReader();
                        reader.Close();

                        foreach (SqlParameter prm in cmd.Parameters)
                        {
                           Debug.WriteLine("");
                           Debug.WriteLine("Name " + prm.ParameterName);
                           Debug.WriteLine("Type " + prm.SqlDbType.ToString());
                           Debug.WriteLine("Size " + prm.Size.ToString());
                           Debug.WriteLine("Direction " + prm.Direction.ToString());
                           Debug.WriteLine("Value " + prm.Value);

                        }

if you are not sure check the value of the parameter before during and after the results have been processed by the reader.