Call Database Function to validate logon

[CrystalVis]

Here is the validate user function in the database:

CREATE OR REPLACE FUNCTION VALIDATEUSER(
VALIDITYCODE OUT NUMBER,
REGISTERUSERIDY OUT TREGISTERUSER.REGISTERUSERIDY%TYPE,
INUSERNAME IN TREGISTERUSER.REGISTERUSERNAME%TYPE,
INUSERPASSWORD IN TREGISTERUSER.USERPASSWORD%TYPE)
RETURN NUMBER IS
/**********************************************************************
* Name: VALIDATEUSER
* Created:
* Modified:
* Description: Validates user logging into the system
**********************************************************************/
codenum number;

CURSOR c_userdetail IS
SELECT * FROM v_registeruser
WHERE RTRIM(UserName) = INUSERNAME
AND RTRIM(UserPassword) = INUSERPASSWORD;

BEGIN

codenum := -1; --assume that there is a valid user record with username and password

FOR users IN c_userdetail LOOP
IF sysdate > users.userexpdate THEN
codenum := 1;
ELSE
codenum := 0;
REGISTERUSERIDY := users.RegisterUserIDY;
END IF;
END LOOP;
VALIDITYCODE := codenum;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END;

I build a logon screen that contains three objects: two text boxes and a command button. The text boxes are for the user to enter username and password. A command button is to submit the data enter in the text boxes. I would like to call a database function validateuser to validate the username and password that are entered in the text boxes. Can someone please give me sample codes of how to accomplish this task. Please give as much detail as possible because I am a beginner to VB. Your help/suggestion is greatly appreciated.
TIA

 

[CrystalVis]

No expert out there can give me suggestion/help with this???

 

[fulbert]

Is this function resides in DB(Stored Procedure) or just a function in your program?

 

[CrystalVis]

the function is resided in the db.

 

[fulbert]

This is my first stored procedure, so the code is not that good, I learned it from Transact-SQl Help just this day.

Here's the code.

create procedure VALIDATEUSER
	@@ValidityCode int OUTPUT,
	@@RegisterUserIDY int OUTPUT,
	@@INUserName varchar(10),
	@@INUserPassword varchar(10)
AS
	set nocount on
	declare @CodeNum int
	declare @ExpDate datetime
	declare @UserIDY int

	set @CodeNum = -1

	IF NOT EXISTS (SELECT * FROM v_registeruser WHERE UserName = @@INUserName AND UserPassword = @@INUserPassword)
		GOTO SUCCESS

	(SELECT @ExpDate = userexpdate FROM v_registeruser WHERE UserName = @@INUserName AND UserPassword = @@INUserPassword)

	IF ((SELECT GETDATE()) > @ExpDate)
		BEGIN
			set @CodeNum = 1
			GOTO SUCCESS
		END
	ELSE
		BEGIN
			(SELECT @UserIDY = RegisterUserIDY FROM v_registeruser WHERE UserName = @@INUserName AND UserPassword = @@INUserPassword)		
			set @CodeNum = 0
			GOTO SUCCESS
		END

SUCCESS:	
	Set @@ValidityCode = @CodeNum
	Set @@RegisterUserIDY = @UserIDY
	RETURN 0

To check this code, in SQL Server Query Analyzer invoke this command(s):

declare @validity int
declare @Register int
exec VALIDATEUSER @validity OUTPUT,@Register OUTPUT,BERT,BERT
print @validity
print @Register
GO

By the way, what's the purpose of this code?

FOR users IN c_userdetail LOOP
    IF sysdate > users.userexpdate THEN
        codenum := 1;
    ELSE
        codenum := 0;
        REGISTERUSERIDY := users.RegisterUserIDY;
    END IF;
END LOOP;

Are you trying to do a loop? Why? Anyway, honestly I'm not that good in creating a stored procedure cause I've hardly used it in any of my program. I've also a function to validate a user but it doesn't reside in a DB(Stored Procedure) instead it resides in my VB program(hardcoded). Stored Procedure is great and I'm thinking of changing my approach in validating a user, this is fun. Thank for making me crazy for an hour.

 

[CrystalVis]

Fulbertgil,
Thanks for your help. The section of code you question is to compare the date the user logon to the system against the user expiration date set in the user table. You mentioned that this can be done in VB alone. Basically, we have a user table, which store the username and password. Can I just take the username and password enter in the two text boxes on the form and validate that the user is exist in the table? If you know of a function in VB to accomplish this, please let me know. Again, thanks for your helps

 

[fulbert]

----------------
The section of code you question is to compare the date the user logon to the system against the user expiration date set in the user table.
----------------
Then this must be the code. This will compare the date the user logon to the system (GETDATE()).

(SELECT @ExpDate = userexpdate FROM TABLENAME WHERE UserName = @@INUserName AND UserPassword = @@INUserPassword)

IF ((SELECT GETDATE()) > @ExpDate)

-------------------
You mentioned that this can be done in VB alone. Basically, we have a user table, which store the username and password. Can I just take the username and password enter in the two text boxes on the form and validate that the user is exist in the table? If you know of a function in VB to accomplish this, please let me know. Again, thanks for your helps.
--------------------
What I do to validate a user is to make an ADODB.Recordset and do some programming but it's the same anyway, it's just that when you use stored procedure, the processing is done in the server not in the client. All you have to do is to make a procedure/function in VB that will accept Username/Password and do some stuff, you know validating that username, besides I think it's a lot easier to make a program in VB than in SQL.