returning int value from stored procedure and check it in asp.net code to validate login form [duplicate]

This question already has answers here: Closed 10 years ago.

Possible Duplicate:

returning int value from stored procedure and check it in asp.net code to validate login form

hello all please i need help in this code as it is stored procedure validate username and password , the problem here is that form validate any data even it doesn't stored in database and i tried to fix code many times but really i haven't any more thing to do in it , any one can help me to solve this problem

this is stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[login_procedure] @username Varchar =50, @password varchar=50, @result int OUTPUT
as
Declare @user_name varchar , @pass_word varchar
Set @user_name = @username
Set @pass_word = @password

if EXISTS (select @username , @password from data where username= @user_name and password=@pass_word) 
select @result=1
else 
select @result=0

and this is asp.net code

SqlConnection conn = new SqlConnection ("Data Source=ANAGUIB-LAPNEW\\SQLEXPRESS;Initial    Catalog=account;Integrated Security=True");
   SqlCommand cmd = new SqlCommand("login_procedure", conn);
   cmd.CommandType = CommandType.StoredProcedure;
   SqlParameter paramReturnValue = new SqlParameter();
   paramReturnValue.ParameterName = "@result";
   paramReturnValue.SqlDbType = SqlDbType.Int;
   cmd.Parameters.Add(paramReturnValue);
   cmd.Parameters["@result"].Direction = ParameterDirection.Output;
   conn.Open();

   cmd.Parameters.AddWithValue("@username", TextBox1.Text);
   cmd.Parameters.AddWithValue("@password", TextBox2.Text);
   int resultID = Convert.ToInt32(cmd.ExecuteScalar());
   if (Convert.ToInt32(resultID) == 0)
   {
      Response.Redirect("hello.aspx");
   }
   else 
   {
      Response.Write("开发者_如何转开发error");
   }
   conn.Close();
}

---

Your stored-procedure has OUTPUT parameter and it is good practice to access value of output parameter after closing the connection.

I've changed proc.

ALTER PROCEDURE login_procedure 
@username Varchar(50), 
@password varchar(50), 
@result int OUTPUT

AS
IF EXISTS (select username from data where username= @username and password=@password) 
  set @result=1
else 
  set @result=0

Demo: How to pass parameters (IN and OUT)?

SqlConnection cn = new SqlConnection(cnstr);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "login_procedure";
cmd.CommandType = System.Data.CommandType.StoredProcedure;


SqlParameter param1 = new SqlParameter("@username", System.Data.SqlDbType.VarChar, 50);
SqlParameter param2 = new SqlParameter("@password", System.Data.SqlDbType.VarChar, 50);
SqlParameter resultParam= new SqlParameter("@result", System.Data.SqlDbType.Int);
resultParam.Direction = System.Data.ParameterDirection.Output;

param1.Value = TextBox1.Text;
param2.Value = TextBox2.Text;

cmd.Parameters.Add(param1);
cmd.Parameters.Add(param2);
cmd.Parameters.Add(resultParam);

cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

int retVal;
int.TryParse(resultParam.Value.ToString(),out retVal);
if(retVal==1)
  //
else
  //

---

In this code

if EXISTS (select @username , 
                  @password 
           from data 
           where username= @user_name and password=@pass_word)  
           select @result=1 
else  
           select @result=0 

You are returning 1 if the given input is validated in your C# code you are giving error when the return value is not 0, that is why every input even which does not exist in your table is validated.Try this;

if (resultID == 1)       
{          
           Response.Redirect("hello.aspx");       
}       
else        
{          
           Response.Write("error");       
}    

And you do not have to Convert the return value of stored procedure twice, once is enough.