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.