Without Using Login control , Validate Credentials

Question

2.00/5 (1 vote)

See more:

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.

Posted 12-Sep-12 19:17pm

Prathap Gangireddy

Add a Solution

2 solutions

Solution 1

Look at this if you do not want use existing Membership provider:
http://msdn.microsoft.com/en-us/library/aa478962.aspx[^]

Posted 12-Sep-12 19:57pm

Kuthuparakkal

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Amy · Accepted Answer · 2012-09-12T20:14:00

You should use a procedure like this:

SQL

CREATE PROCEDURE spUserLogin
	@Username VARCHAR(50),
	@Password VARCHAR(50)
AS
BEGIN
	IF(SELECT COUNT(*) FROM tblUser WHERE Username = @Username AND Password = @Password) > 0
	BEGIN 
		--Login successfull here
	END
	ELSE
	BEGIN
		DECLARE @NoOfFailedAttempts SMALLINT
		SET @NoOfFailedAttempts = (SELECT NoOfFailedAttempts FROM tblUser WHERE Username = @Username)
		IF @NoOfFailedAttempts = 3
		BEGIN
			 UPDATE tblUser SET Islocked = 1 WHERE Username = @Username
		END
		ELSE
		BEGIN
			 UPDATE tblUser SET NoOfFailedAttempts = @NoOfFailedAttempts + 1 WHERE Username = @Username
		END 
	END
        --Return the number of attempts
        SELECT NoOfFailedAttempts FROM tblUser WHERE Username = @Username
END

--Amit

Without Using Login control , Validate Credentials

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Without Using Login control , Validate Credentials

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us