problem in login page

[vijayashanthi]

hi all i am creating a login page and my problem was, sp returns value for Num_of_User = 1 but the returnParam will always got a returnParamValue = 0.. what is wrong with this code... this my code... CREATE TABLE NorthWindUsers (UserID INT IDENTITY(1,1) NOT NULL, UserName VARCHAR(50) NOT NULL, Password VARCHAR(50) NOT NULL) INSERT INTO NorthWindUsers (UserName, Password) VALUES ('sachin', 'tendulkar') alter PROCEDURE sp_ValidateUser ( @UserName VARCHAR(50) = Null, @Password VARCHAR(50) = Null, @Num_of_User INT = 0 ) AS SET @Num_of_User = (SELECT COUNT(*) AS Num_of_User FROM NorthWindUsers WHERE UserName = @UserName AND Password = @Password) Return @Num_of_User private void btnSubmit_Click(object sender, System.EventArgs e) { int maxLoginAttempts = (int)Session["MaxLoginAttempts"]; if (Session["LoginCount"].Equals(maxLoginAttempts)) { Response.Redirect("WebForm1.aspx?reason=maxloginattempts"); } if(Page.IsValid) { if(DBConnection(txtusername.Text.Trim(),txtPassword.Text.Trim())) { FormsAuthentication.RedirectFromLoginPage(txtusername.Text, false); } else { lblMessage.Text = "Invalid Login Please Try again...."; } } } private bool DBConnection(string txtUser,string txtPass) { //int a; SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connstr"]); SqlCommand myCommand = new SqlCommand("sp_ValidateUser", myConnection); myCommand.CommandType = CommandType.StoredProcedure; SqlParameter objParam1; SqlParameter objParam2; SqlParameter returnParam; objParam1 = myCommand.Parameters.Add("@UserName", SqlDbType.VarChar); objParam2 = myCommand.Parameters.Add("@Password",SqlDbType.VarChar); returnParam = myCommand.Parameters.Add ("@Num_of_User",SqlDbType.Int); objParam1.Direction = ParameterDirection.Input; objParam2.Direction = ParameterDirection.Input; returnParam.Direction = ParameterDirection.ReturnValue; objParam1.Value = "txtuser"; objParam2.Value = "txtPass"; //a = Convert.ToInt32(myCommand.Parameters["@num_of_User"].Value); try { if(myConnection.State.Equals(ConnectionState.Closed)) { myConnection.Open(); returnParam = myCommand.ExecuteNonQuery(); //Int32 rowCount = (Int32)catDA.InsertCommand.Parameters["@RowCount"].Value; } if((int)returnParam.Value

[Colin Angus Mackay]

There are too many problems with this.

wrote:

@Num_of_User INT = 0

You have a parameter clash. You've defined this as a return value, yet here you are specifying it as an input parameter with a default value of zero.

wrote:

objParam1.Value = "txtuser"; objParam2.Value = "txtPass";

This passes a string literal to the parameter, so the stored procedure will never get the value passed in to the method, so it will never find anything because it does not match what you inserted into the database.

wrote:

if(myConnection.State.Equals(ConnectionState.Closed)) { myConnection.Open();

This is redundant. The connection will always be closed as it is newly created. Here is a simpler solution: For something this simple, I'd use ExecuteScalar and get the value from the SELECT directly SQL:

alter PROCEDURE ValidateUser
(
@UserName VARCHAR(50) = Null,
@Password VARCHAR(50) = Null
)
AS
SELECT COUNT(*)
FROM NorthWindUsers
WHERE UserName = @UserName AND Password = @Password
GO

The sp prefix was removed because in SQL Server this may clash with system stored procedures. If SQL Server sees a stored procedure call starting with sp it goes to the master database first. C#

SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connstr"]);
SqlCommand myCommand = new SqlCommand("ValidateUser", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

SqlParameter objParam1 = myCommand.Parameters.Add("@UserName", SqlDbType.VarChar);
SqlParameter objParam2 = myCommand.Parameters.Add("@Password",SqlDbType.VarChar);

objParam1.Value = txtuser;
objParam2.Value = txtPass;

myConnection.Open();

int returnParam = (int)myCommand.ExecuteScalar();

myConnection.Close();

.... // Do the rest of your stuff.

The direction was dropped, as all parameters are Input by default.

---

Upcoming events: * Glasgow: Geek Dinner (5th March) * Edinburgh: Web Security Conference Day for Windows Developers (12th April) My: Website | Blog |