Without Using Login control Validate Credentials

Posted by Nkkppp under ASP.NET on 9/12/2012 | Points: 10 | Views : 1408 | Status : [Member] | Replies : 4
Hi,

There are 4 columns in Sql DB Username varchar(20),Password varchar(20),NoOfFailedAttempts tinyint,Islocked bit not null default 0.When the User enters his credentials i need to verify the credentials in the DB and if the credentials exists the user has to login.If the Username and Pwd are not matching then the NoofFailedAttempts has to be updated and if it reaches 3 Islocked column has to be updated to 1 .
At the same time in the front end i need to show the user the count of login attempts failed.Suppose if the User successfully logged in the 3 rd attempt again the NoofFailedAttempts column has to be set to 0.

Can any one please provide a stored procedure for the above requirement.




Responses

Posted by: Vuyiswamb on: 9/12/2012 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Start here , and Update the SP to suite your requirement , i will also Update the article later

http://www.dotnetfunda.com/articles/article808-how-to-write-a-simple-login-page-in-aspnet.aspx

Thank you for posting at Dotnetfunda
[Administrator]

Nkkppp, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nkkppp on: 9/12/2012 [Member] Starter | Points: 25

Up
0
Down
Thank you Vuyiswamb.

Can you also update the article with my requirement.

Thanks,
Prathap.

Nkkppp, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 9/12/2012 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
I will later



Thank you for posting at Dotnetfunda
[Administrator]

Nkkppp, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nkkppp on: 9/14/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Vuyiswamb,

I have changed the SP as per my requirement in the front end.Can you please let me know if I can improve my code in the front end as there is lot of boxing which will affect the performance.

CREATE PROCEDURE Validate(@Username VARCHAR(50),@Password VARCHAR(50))

AS
BEGIN
IF(SELECT COUNT(*) FROM UserDetails WHERE Username = @Username AND Password = @Password and Islocked<>1) > 0
BEGIN
update Userdetails set Nooffailedattempts=0 where Username=@Username
END
ELSE
BEGIN
IF(SELECT Nooffailedattempts FROM UserDetails WHERE Username = @Username)=3
BEGIN
UPDATE UserDetails SET Islocked = 1 WHERE Username = @Username
END
ELSE
BEGIN
UPDATE UserDetails SET NoOfFailedAttempts = Nooffailedattempts + 1 WHERE Username = @Username
END
END
--Return the number of attempts
SELECT NoOfFailedAttempts,Islocked FROM UserDetails WHERE Username = @Username
END

_____________________________________________________
Code in front end Login button

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))

{
SqlCommand cmd = new SqlCommand("Validate", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", txtuid.Text);
cmd.Parameters.AddWithValue("@password", txtpwd.Text);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
if (dr[0].ToString() == "0" && dr.GetBoolean(1).ToString() == "False")
FormsAuthentication.RedirectFromLoginPage(txtuid.Text, false);
else if (Convert.ToInt32(dr[0].ToString()) > 0 && Convert.ToInt32(dr[0].ToString())<3)
lbl.Text = "Your " + dr[0].ToString() + " Login attempt has been failed";
else
lbl.Text = "Your " + dr[0].ToString() + " login attempts are failed and Your Account is locked";
}
else
lbl.Text = "UserName or Password is Incorrect";

}



Nkkppp, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response