Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Check Username before Stored Procedure,..

Reply
Thread Tools

Check Username before Stored Procedure,..

 
 
GTN170777
Guest
Posts: n/a
 
      02-05-2008
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??
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-05-2008
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"


 
Reply With Quote
 
 
 
 
GTN170777
Guest
Posts: n/a
 
      02-05-2008
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"
>
>
>

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-05-2008
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.


 
Reply With Quote
 
GTN170777
Guest
Posts: n/a
 
      02-05-2008
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.
>
>
>

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-05-2008
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.


 
Reply With Quote
 
GTN170777
Guest
Posts: n/a
 
      02-06-2008
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.
>
>
>

 
Reply With Quote
 
GTN170777
Guest
Posts: n/a
 
      02-06-2008
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.
>
>
>

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-07-2008
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I use the password and username stored in cookie files to access the web automatically wqhflp@gmail.com Perl Misc 0 01-05-2007 08:49 AM
loginview control displays network username instead of (web) username Shailesh Patel ASP .Net Web Controls 0 11-08-2006 08:19 PM
Check the username and password with the active directory Filip De Backer ASP .Net Security 3 08-29-2005 07:40 AM
Change the username found in "C:\Documents and Settings\Username" The Reluctant Robot Named Jude Computer Support 1 05-05-2004 07:11 AM
Check If Username Exsists Harry ASP .Net 1 10-27-2003 05:45 PM



Advertisments