1

I have a login page wherein it logs in the user with their Email Address & Password.Below is the stored procedure which validates the user and then gives out 'UserId' and 'Role' from tblAllUser.

     Alter proc spValidateUser
     @EmailAdd nvarchar(30),
     @Password nvarchar(20)
     as
     begin
     Set Nocount on;
     Declare @UserId nvarchar(10),@LastLogin datetime,@RoleId int

     Select @UserId = UserId, @LastLogin=LastLogin, @RoleId=RoleId
     from tblAllUsers where EmailAdd=@EmailAdd and Password=@Password

     If @UserId is not null
     Begin
     Update tblAllUsers
     SET LastLogin= GETDATE()
     WHERE UserId=@UserId
     Select @UserId [UserId],
     (Select Role from tblRoles where RoleId=@RoleId) [Roles]
     End
     Else
     Begin
     Select -1
     End
     End

I have tested the above procedure and it works fine and gives out the UserId and Role as required.The problem is when i click on the Login button i throws and exception IndexOutOfRange.Here is the code :

  protected void LoginControl_Authenticate(object sender, EventArgs e)
{
    string userId = string.Empty;
    string roles = string.Empty;
    string CS = ConfigurationManager.ConnectionStrings["SportsActiveConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        using (SqlCommand cmd = new SqlCommand("spValidateUser"))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EmailAdd", Login1.UserName);
            cmd.Parameters.AddWithValue("@Password", Login1.Password);
            cmd.Connection = con;
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            userId = reader["UserId"].ToString();
            roles = reader["Roles"].ToString();
            con.Close();
        }
        switch (userId)
        {
            case "-1":
                Login1.FailureText = "Username and/or password is incorrect.";
                break;
            default:
                FormsAuthenticationTicket ticket = new FormsAuthenticationTicket(1, Login1.UserName, DateTime.Now, DateTime.Now.AddMinutes(2880), Login1.RememberMeSet, roles, FormsAuthentication.FormsCookiePath);
                string hash = FormsAuthentication.Encrypt(ticket);
                HttpCookie cookie = new HttpCookie(FormsAuthentication.FormsCookieName, hash);

                if (ticket.IsPersistent)
                {
                    cookie.Expires = ticket.Expiration;
                }
                Response.Cookies.Add(cookie);
                Response.Redirect(FormsAuthentication.GetRedirectUrl(Login1.UserName, Login1.RememberMeSet));
                break;
        }
    }
}

The exception is thrown at

   userId = reader["UserId"].ToString();

Any help will be appreciated.

RelatedRhymes
  • 428
  • 6
  • 26
  • 1
    Is the exception thrown regardless of whether you specify a valid username/password combination, or only when the login is invalid? Because in the latter case, your sproc is producing a result set that doesn't include a column called `UserId` (or one called `Roles` for that matter). It might be better to produce no result set in the case of a failed login, and then test for that by examining the return value of `reader.Read()`. – Joe Farrell Jun 22 '15 at 16:06
  • Also, although you haven't shared all of the relevant code, I have concerns about whether or not you're storing passwords securely. If they're in the database as plaintext, please review some of the answers to [this question](http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database). – Joe Farrell Jun 22 '15 at 16:10
  • Yes, I had to give columns even when the user is invalid.The problem was that the stored procedure just gave out -1 and wasnt associated with column UserId.This solved the problem.@JoeFarrell – RelatedRhymes Jun 22 '15 at 16:17

0 Answers0