public void Authenticate(string Username, string Password)
{
string Encryptpassword = FormsAuthentication.HashPasswordForStoringInConfigFile(txtpassword.Text, "SHA1");
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using(SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spAuthenticateUsers",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username",txtuname.Text);
cmd.Parameters.AddWithValue("@Password", Encryptpassword);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int retryattempts = Convert.ToInt32(dr["retryattempts"]); //
if(Convert.ToBoolean(dr["Accountlocked"]))
{
Label1.Text = "Account locked Please contact Administrator";
}
else if (retryattempts > 0)
{
int Attemptsleft = (4 - retryattempts);
Label1.Text = "Invalid Username or Password" + Attemptsleft.ToString() + "Attemptsleft";
}
else if (Convert.ToBoolean(dr["Authenticated"]))
{
FormsAuthentication.RedirectFromLoginPage(txtuname.Text, CheckBox1.Checked);
}
else
{
Label1.Text = "invalid Username/Password";
}
}
}
// this line iam getting the exception what does it mean?
int retryattempts = Convert.ToInt32(dr["retryattempts"]);
Help me out thanks in advance
and the stroed procedure is
Create Procedure sptblUserAuthentication
@Username nvarchar(50),
@Password nvarchar(50)
As
Begin
Declare @Accountlocked int
Declare @Count int
Declare @Retrycount bit
End
-- Declaration is finished--
Select @Accountlocked = Islocked from tblUsers Where Username=@Username
if(@Accountlocked = 1)
Begin
Select 1 as Accountlocked,0 as RetryAttempts,0 as Authenticated
End
Else
Begin
--Check if the username and password is match--
Select COUNT(Username) from tblUsers Where Username=@Username and Password=@Password
--if match found--
if(@Count = 1)
Begin
--Reset values --
Update tblUsers set RetryAttempts=0 Where Username=@Username
Select 0 as RetryAttempts,1 as Authenticated,0 as Accountlocked
End
Else
Begin
--if match not found--
Select @Retrycount = IsNull(RetryAttempts,0) from
tblUsers Where Username=@Username
Set @Retrycount = @Retrycount + 1
if(@Retrycount <= 3)
Begin
--if retry attempts are not completed--
Update tblUsers Set RetryAttempts = @Retrycount Where Username=@Username
Select 0 as Accountlocked,0 as Authenticated,@Retrycount as RetryAttempts
End
Else
Begin
--if retry attempts are not completed--
Update tblUsers set RetryAttempts=@Retrycount ,Islocked=1,LockDatetime=GETDATE() Where Username=@Username
Select 1 as Accountlocked,0 as Authenticated,0 as RetryAttempts
End
End
End