Check Username before Stored Procedure,..

[GTN170777]

I would be greatfull for any help with this check username problem,..I have a
page (register.asp) with a Form (Form1) which posts the data to another page
(afterregistration.asp), where a Stored Procedure inserts the data from
register.asp into two tables.

The stored procedure works great, but as again here I have failed to check
the the value which is being inserted into the Username field I am risking a
dodgy login.

There are multiple sites using the database, each with a unique siteid, a
user could have registered against any number of these sites using the same
email address (username / JBEUserEmail) Therefore before the data is passed
to the page where the stored procedure processes it, i need to check whether
table dbo.JBEmployee on the database already contains a record with the same
values in JBESiteID and JBEUserEmail that are being posted to thev
afterregistration page.

Is there anyway of checking this information on a Form Post, and if it does
not exist redirecting to afterregistration.asp (for processing) or if it
already exists redirecting to a registrationfailure.asp page?

Hope this makes sense??

 

[Bob Barrows [MVP]]

I would be greatfull for any help with this check username
problem,..I have a page (register.asp) with a Form (Form1) which
posts the data to another page (afterregistration.asp), where a
Stored Procedure inserts the data from register.asp into two tables.

The stored procedure works great, but as again here I have failed to
check the the value which is being inserted into the Username field I
am risking a dodgy login.

There are multiple sites using the database, each with a unique
siteid, a user could have registered against any number of these
sites using the same email address (username / JBEUserEmail)
Therefore before the data is passed to the page where the stored
procedure processes it, i need to check whether table dbo.JBEmployee
on the database already contains a record with the same values in
JBESiteID and JBEUserEmail that are being posted to thev
afterregistration page.

?? So do it in the stored procedure ... Use RAISERROR in the procedure if
the name does not exist.

 

[GTN170777]

Hi Bob,

I've got the following stored procedure, which someone helped me with,

CREATE PROCEDURE dbo.NewClient_test
@siteid INT,
@companyname NVARCHAR(50),
@address NVARCHAR(500),
@phone NVARCHAR(50),
@fax NVARCHAR(50),
@email NVARCHAR(225),
@url NVARCHAR(225),
@companytype NVARCHAR(50),
@billingcontact NVARCHAR(50),
@name NVARCHAR(50),
@AccountType NVARCHAR(50),
@PASSWORD NVARCHAR(50),
@AccountLive NVARCHAR(50),
@EmployeeLevel NVARCHAR(50)
AS
BEGIN
DECLARE @NewID INT;

IF EXISTS
(
SELECT 1
FROM dbo.JBEmployee
WHERE JBESiteID = @SiteID
AND JBEUsername = @Email
)
BEGIN
RAISERROR('This username already exists.', 11, 1);
RETURN -1;
END
ELSE
BEGIN
INSERT dbo.JBClient
(
JBCLSiteID,
JBCLName,
JBCLAddress,
JBCLPhone,
JBCLFax,
JBCLEmail,
JBCLCompanyType,
JBCLURL,
JBCLAccountType,
JBCLAccountlive,
JBCLBillingContact
)
SELECT
@siteid,
@companyname,
@address,
@phone,
@fax,
@email,
@companytype,
@url,
@AccountType,
@AccountLive,
@billingcontact;

SET @NewID = SCOPE_IDENTITY();

INSERT dbo.JBEmployee
(
JBEClientID,
JBESiteID,
JBEName,
JBELevel,
JBEUsername,
JBEPassword,
JBEAddress,
JBEPhone
)
SELECT
@NewID,
@siteid,
@name,
@EmployeeLevel,
@email,
@PASSWORD,
@address,
@phone;
END
END

The problem that is confusing me, is how to capture and show that the web
user that the username already exists?

Appreciate your help

 

[Bob Barrows [MVP]]

Hi Bob,

I've got the following stored procedure, which someone helped me with,

CREATE PROCEDURE dbo.NewClient_test
@siteid INT,
@companyname NVARCHAR(50),
@address NVARCHAR(500),
@phone NVARCHAR(50),
@fax NVARCHAR(50),
@email NVARCHAR(225),
@url NVARCHAR(225),
@companytype NVARCHAR(50),
@billingcontact NVARCHAR(50),
@name NVARCHAR(50),
@AccountType NVARCHAR(50),
@PASSWORD NVARCHAR(50),
@AccountLive NVARCHAR(50),
@EmployeeLevel NVARCHAR(50)
AS
BEGIN

Stop right here - the first line that should appear right here is:

SET NOCOUNT ON

It will prevent many confusing problems in the future. Make it a habit.

IF EXISTS
(
SELECT 1
FROM dbo.JBEmployee
WHERE JBESiteID = @SiteID
AND JBEUsername = @Email
)
BEGIN
RAISERROR('This username already exists.', 11, 1);
RETURN -1;
END

The problem that is confusing me, is how to capture and show that the
web user that the username already exists?

Two options:
1.
trap the error returned when executing the procedure and do what you
need to do - this is trivial vbscript error-handling:
on error resume next
'do something that might raise error, then check if an error occurred:
if err <> 0 then
'an error occurred - notify user
else
'no error occurred
end if



2.
A little more robust given the unfortunate proclivity of ADO to
sometimes fail to capture error messages: use an explicit Command object
to execute the procedure and enable you to read the value of that RETURN
parameter. If it's -1, then act on the information that the user already
exists.

What's the problem?

 

[GTN170777]

Thanks Bob, but now you have completely lost me, I'm a dreamweaver developer,
trying to push the boudries, any chance you can explain in a little more
detail?

Thanks

 

[Bob Barrows [MVP]]

Explain what? I'm not sure what you could still be confused about. The
stored procedure both raises an error and returns a return_value of -1
when the username already exists. You have the option of catching the
error upon executing the procedure and handling it, or using a Command
object to allow the return_value to be read.

You'll need to be more explicit/specific about what is still confusing
you. I have no idea what your coding abiliies are, but presumably they
include the abiity to write code to execute a stored procedure. Adding
error-handling should be as easy as I demonstrated in my previous reply.
If you want me to write the thing for you, you'll need to wait until I
get home from work.

 

[GTN170777]

Hi Bob,

Hope you are ok? i've never worked with Error-Handling, infact up until
recently I've relied on Dreamweavers (WYSIWYG) ability, but that just won;t
do all the things i need it to, really in honesty, rather than have someone
else do the coding, I'd like to learn it, so I'm doing some Googleing. Really
what i need to try and do is -

On Error - X
On Completion - Y

Where X is display error message together with data in a form to user, and Y
is proceed to redirect page..

I'll keep googleing, but I might drop you a line later...#

Thanks

 

[GTN170777]

Hi Bob,

I'm starting to understand the whole consept now!!!, however still
struggleing with how to implement, When i test my page, with data i know to
be duplicated i get a standard --

Microsoft OLE DB Provider for SQL Server error '80040e14'

This username already exists.

/employer/afterregistration.asp, line 200
....

What i would like to do is remove the --

"Microsoft OLE DB Provider for SQL Server error '80040e14' "

&

"/employer/afterregistration.asp, line 200 "

parts and just show the -

"This username already exists." part within my standard web design page, and
above a form which has been pre populated. Any ideas?

Thanks again

 

[Bob Barrows [MVP]]

Hi Bob,

I'm starting to understand the whole consept now!!!, however still
struggleing with how to implement, When i test my page, with data i
know to be duplicated i get a standard --

Microsoft OLE DB Provider for SQL Server error '80040e14'

This username already exists.

/employer/afterregistration.asp, line 200
...

I have to guess because you are not showing me the relevant lines of code.
My guess is you left out the crucial "on error resume next" line.