April 14, 2012 at 2:53 pm
Hi Guys,
Wondering if someone can help me, im creating a simple login page in my ASP.Net application and the user has to enter a Username and a Password the press login when they hit login it calls a stored procedure called CheckUser but what i want to do is some how check to make sure the username and password they have entered is correct and also check another column called ExamTaken which is a BIT column and if they have taken the Exam they cant login, else they can if that makes sense, im struggling on how i can achieve this functionality.....
heres the start of my SP
ALTER PROCEDURE [dbo].[GetUserDetails]
-- Add the parameters for the stored procedure here
@UserName Varchar(50),
@Password Varchar(50)
AS
BEGIN
SET NOCOUNT ON;
END
Heres my Table its called UserAccount and its laid out as follows
UserName, varchar(50)
uPassword, varchar(50)
FirstName, varchar(50)
Surname, varchar(50)
Email, varchar(75)
ExamTaken, bit
AccountType, int
LastLogin, datetime
So just to clarify, i need to check the username and password they have entered match the records and also check to see if they have taken the exam, if they have taken the exam then i need they cant login.......if someone can help me i would highly appreciate it,
April 14, 2012 at 3:34 pm
Why don't you write your query with the needed logic and return either (0/1) or ('T'/'F') as needed?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 14, 2012 at 4:52 pm
Something like this might be what you require
CREATE Table #UserAccount(UserName varchar(50),uPassword varchar(50)
,FirstName varchar(50),Surname varchar(50),Email varchar(75)
,ExamTaken bit,AccountType int,LastLogin datetime)
INSERT INTO #UserAccount
SELECT 'M123','whoknows','Sam','Johnson','Me@Gmail.com',0,87,GETDATE() UNION ALL
SELECT 'Sal789','sheknows','Jamie','Smith','Metwo@Gmail.com',1,7,GETDATE()
CREATE PROCEDURE [dbo].[GetUserDetails]
@UserName Varchar(50),@Password Varchar(50)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT UserName FROM #UserAccount WHERE RTRIM(uPassword) = RTRIM(@Password))
RETURN 1
ELSE
RETURN 2
END
-- Use as:
DECLARE @return_status int;
EXECUTE @return_status = [dbo].[GetUserDetails] 'M123','GARGAGE'
SELECT 'Return Status' = @return_status;
Note you will have to modify the above to include testing for the EXAMTAKEN field.
The next time you post, please, please post the table definition, sample data (as I did for you in the above) and expected results. To do so quickly and easily please read the article that displays when you click on the first link in my signature block. The article includes the T-SQL code to allow you to post what I asked for quickly and simply, but it does facilitate your getting a tested answer.
1. Something else you might or should be thinking about is encrypting the password field to keep others form learning passwords not their own.
2. You should/could also define the username column as case sensitive (refer to BOL for Collations) to add a little more dificulty for users attempting to use credentials other than their own to take the test.
April 14, 2012 at 8:36 pm
Why aren't you using .net roles and authorisation it has all you need for this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply