Check Username before Stored Procedure,..

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??

GTN170777 wrote:
> 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.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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]" wrote:

> GTN170777 wrote:
> > 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.
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

GTN170777 wrote:
> 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.

<snip>
> 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?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

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]" wrote:

> GTN170777 wrote:
> > 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.
>
> <snip>
> > 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?
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

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 wrote:
> 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]" wrote:
>
>> GTN170777 wrote:
>>> 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.
>>
>> <snip>
>>> 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?
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

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

"Bob Barrows [MVP]" wrote:

> 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 wrote:
> > 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]" wrote:
> >
> >> GTN170777 wrote:
> >>> 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.
> >>
> >> <snip>
> >>> 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?
> >> --
> >> Microsoft MVP -- ASP/ASP.NET
> >> Please reply to the newsgroup. The email account listed in my From
> >> header is my spam trap, so I don't check it very often. You will get
> >> a quicker response by posting to the newsgroup.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

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]" wrote:

> 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 wrote:
> > 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]" wrote:
> >
> >> GTN170777 wrote:
> >>> 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.
> >>
> >> <snip>
> >>> 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?
> >> --
> >> Microsoft MVP -- ASP/ASP.NET
> >> Please reply to the newsgroup. The email account listed in my From
> >> header is my spam trap, so I don't check it very often. You will get
> >> a quicker response by posting to the newsgroup.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

GTN170777 wrote:
> 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.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"