Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Sep 2007
    Posts
    3

    Unanswered: If username exists, return "try again" otherwise write to db

    I am a SQL newbie trying to create a registration page. The registration data is stored in "tblUsers," and the username is stored in the field named "UID" -- which is the primary key. If the user enters a username that already exists in the database, I want to return the "That username already exists" message and keep them on the registration page. If the username doesn't exist, I want to write all their registration info to the database.

    I tried the code below using both an existing username and then using a username that didn't exist. Both times I got the "that username already exists" error message on the top of the confirmation page, and the record was not written to the database.

    I also tried changing the "If objRec.RecordCount <> 0" to "If objRec.RecordCount >0" I got it to write to the database if the username didn't exist, but if the username did exist, I got the microsoft error '80040e14' (The changes you requested to the table were not successful because they would create duplicate values...)

    Thanks for any help.

    Code:
    <%
    SQLCmd = "SELECT UID From tblUsers WHERE UID = " & "'" & UID & "'"
    Set objRec = objConn.Execute(SQLCmd)
    If objRec.RecordCount <> 0 Then
       Response.write "That username already exists"
    Else
    SQLCmd = "INSERT INTO tblUsers"
    SQLCmd = SQLCmd & " (FIRSTNAME, LASTNAME, UID, PWD, STREET, CITY, STATE, ZIP, EMAIL, PHONE, FAX, PHYSICIAN, SPECIALTY, OTHER) " 
    SQLCmd = SQLCmd & " VALUES ('" & firstname & "',"
    SQLCmd = SQLCmd & "'" & lastname & "',"
    SQLCmd = SQLCmd & "'" & UID & "',"
    SQLCmd = SQLCmd & "'" & PWD & "',"
    SQLCmd = SQLCmd & "'" & street & "',"
    SQLCmd = SQLCmd & "'" & city & "',"
    SQLCmd = SQLCmd & "'" & state & "',"
    SQLCmd = SQLCmd & "'" & zip & "',"
    SQLCmd = SQLCmd & "'" & email & "',"
    SQLCmd = SQLCmd & "'" & phone & "',"
    SQLCmd = SQLCmd & "'" & fax & "',"
    SQLCmd = SQLCmd & "'" & physician & "',"
    SQLCmd = SQLCmd & "'" & specialty & "',"
    SQLCmd = SQLCmd & "'" & other & "')"
    Set objRec = objConn.Execute(SQLCmd)
    End If
    %>

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is UID numeric? If so you do not need the apostrophes (apostrophes imply that it is a string)

    It may be worth checking to see if the recordset is empty - this is when the End Of File and Beginning Of File is true
    Code:
    If objRec.EOF And objRec.BOF Then
        Response.Write "<b>No</b> records returned"
    Else
        Response.Write "Records found"
    End If
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should not do the SELECT at all!!

    just go ahead and do the INSERT, and trap any "duplicate" error that you might get
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oooh, here's where we disagree again Rudy!
    I'm more of a "don't ever let errors happen" kinda guy ~ must be the way I was taught.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm more of a "don't write your own code for stuff the database does automatically" guy

    george, do you or do you not always define a UNIQUE constraint if you have an auto_increment in a table?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm a prevent errors, not catch errors kind of guy
    Quote Originally Posted by r937
    do you or do you not always define a UNIQUE constraint if you have an auto_increment in a table?
    And of course, but I also hate checking @@error after every "line" of SQL.

    P.S. line/batch - what do we call it?
    Each batch is processed as a single command, so perhaps command is the word I'm looking for?

    EDIT: See below.
    Last edited by gvee; 09-10-07 at 10:11.
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let me just get a confirmation on this

    you do not declare a UNIQUE constraint if your table uses an auto_increment???

    if true, then no wonder you get yourself into so many predickaminks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ugh, sorry for the confusion.
    that should read
    "Of course I do ..."

    And what predicaments are those exactly?
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    predicaments such as not knowing the difference between a surrogate key and a natural key (but perhaps you do)

    predicaments such as running twice as many sql statements as you really need to get the job done (e.g. SELECT before INSERT)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2007
    Posts
    3
    Thanks!!! I got it to work with the
    Code:
    If objRec.EOF And objRec.BOF Then
        Response.Write "<b>No</b> records returned"
    Else
        Response.Write "Records found"
    End If

    I'm trying to create an entire process, so I'm sure I'll be posting more questions when I get to the next step. I really appreciate all the help.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    predicaments such as not knowing the difference between a surrogate key and a natural key (but perhaps you do)

    predicaments such as running twice as many sql statements as you really need to get the job done (e.g. SELECT before INSERT)
    I do know the difference between the two - but often confuse the words with their meanings (remember ommitt vs negate )

    And I'm afraid I disagree with your arguement there.
    Code:
    INSERT INTO MyTable(Uname) VALUES ('george')
    INSERT INTO MyTable(Uname) VALUES ('george')
    
    IF @@error <> 0 BEGIN 
        PRINT 'Record not inserted'
    END

    P.S. Just joshing yarrr
    George
    Home | Blog

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by georgev
    Oooh, here's where we disagree again Rudy!
    I'm more of a "don't ever let errors happen" kinda guy ~ must be the way I was taught.

    So does that mean you tend make all of your code do twice as much work?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Prevention instead of handling.
    Two separate techniques, both with their own merits.
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the "merit" of calling the database twice when you really only need to do it once will eventually bite you...

    ... assuming you ever build an app with more than a half dozen users

    badump-bump tshhh


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by georgev
    Prevention instead of handling.
    Don't you still have to handle "it" even if you are taking precautions?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •