Check Username before Stored Procedure,..

Discussion in 'ASP General' started by GTN170777, Feb 5, 2008.

  1. GTN170777

    GTN170777 Guest

    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, Feb 5, 2008
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Feb 5, 2008
    #2
    1. Advertising

  3. GTN170777

    GTN170777 Guest

    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, Feb 5, 2008
    #3
  4. 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.
     
    Bob Barrows [MVP], Feb 5, 2008
    #4
  5. GTN170777

    GTN170777 Guest

    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.
    >
    >
    >
     
    GTN170777, Feb 5, 2008
    #5
  6. 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.
     
    Bob Barrows [MVP], Feb 5, 2008
    #6
  7. GTN170777

    GTN170777 Guest

    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.
    >
    >
    >
     
    GTN170777, Feb 6, 2008
    #7
  8. GTN170777

    GTN170777 Guest

    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, Feb 6, 2008
    #8
  9. 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"
     
    Bob Barrows [MVP], Feb 7, 2008
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sarmad Aljazrawi

    New Stored Procedure Template in .Net

    Sarmad Aljazrawi, Dec 16, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    539
    Sarmad Aljazrawi
    Dec 16, 2003
  2. Mike P
    Replies:
    0
    Views:
    3,310
    Mike P
    Jun 19, 2006
  3. AlexWare
    Replies:
    2
    Views:
    762
    Paul Uiterlinden
    Oct 23, 2009
  4. Shailesh Patel
    Replies:
    0
    Views:
    465
    Shailesh Patel
    Nov 8, 2006
  5. Replies:
    0
    Views:
    115
Loading...

Share This Page