Without Using Login control Validate Credentials

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	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

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

Latest Posts