Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    stop users registering with same email address

    hi

    i have a registration page which works fine so far-im using vb.net and connecting to a mysql database wth odbc.
    im just wondering how can i stop someone registering with the same email address more than once.I have a couple of books on asp.net but they only refer how to do this with a stored procedure which my version of mysql doesnt support
    Really appreciate any help on this..im a beginner and i cant figure out how to do it without a stored procedure...can it be done without?
    heres the part of the code i have so far which inserts the users data:


    Code:
    Sub Register_Click(s As Object, e As EventArgs)
    
    Cmd = New OdbcCommand ("INSERT INTO User (firstname, lastname, address1,address2,email,telno,pwd) VALUES (?, ?, ?,?,?,?,?)" ,conn)
    Cmd.Parameters.Add("@firstname",odbctype.varchar,10).value = txtfirstname.text
    Cmd.Parameters.Add("@lastname",odbctype.varchar,10).value = txtlastname.text
    Cmd.Parameters.Add("@addr1",odbctype.varchar,20).value = txtaddress1.text
    Cmd.Parameters.Add("@addr2",odbctype.varchar,20).value = txtaddress2.text
    Cmd.Parameters.Add("@email",odbctype.varchar,20).value = txtemail.text 
    
    Cmd.Parameters.Add("@telno",odbctype.varchar,20).value = txttelno.text
    Cmd.Parameters.Add("@pwd",odbctype.varchar,20).value = txtpassword.text
    
    conn.open()
    Cmd.ExecuteNonQuery() 
    Conn.Close() 
    
    Response.Redirect("login.aspx")
    End Sub
    appreciate all suggestions
    thanks

  • #2
    Senior Coder A1ien51's Avatar
    Join Date
    Jun 2002
    Location
    Between DC and Baltimore In a Cave
    Posts
    2,717
    Thanks
    1
    Thanked 94 Times in 88 Posts
    Do something like
    select count(email) as total form TableX where email = yourField

    if you get a count then you know that the email is there so give them an error message.

    Eric
    Tech Author [Ajax In Action, JavaScript: Visual Blueprint]

  • #3
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your reply eric,
    ive tried that but i keep getting this error:

    Exception Details: System.Data.Odbc.OdbcException: ERROR [07002] SQLBindParameter not used for all parameters

    Source Error:

    Line 39:
    Line 40: conn.open()
    Line 41: Cmd.ExecuteNonQuery()
    Line 42: if count <> 0 then
    Line 43: label1.text = "email already exists"

    this is the code ive used:
    Code:
    Sub AddDetails_Click(s As Object, e As EventArgs)
    
    dim count as integer
    
    Cmd = New OdbcCommand ("INSERT INTO User (firstname, lastname, address1,address2,email,telno,pwd) VALUES (?, ?, ?,?,?,?,?)" ,conn)
    
    
     Cmd.Parameters.Add("@firstname",odbctype.varchar,10).value = txtfirstname.text
     Cmd.Parameters.Add("@lastname",odbctype.varchar,10).value = txtlastname.text
     Cmd.Parameters.Add("@addr1",odbctype.varchar,20).value = txtaddress1.text
     Cmd.Parameters.Add("@addr2",odbctype.varchar,20).value = txtaddress2.text
     Cmd.Parameters.Add("@email",odbctype.varchar,20).value = txtemail.text
     Cmd.Parameters.Add("@telno",odbctype.varchar,20).value = txttelno.text
     Cmd.Parameters.Add("@pwd",odbctype.varchar,20).value = txtpassword.text
    
    cmd = new odbccommand("select count (email) as total from researcher where email = ?",conn)
    
    conn.open()
    Cmd.ExecuteNonQuery() 
    
    if count <> 0 then
    label1.text = "email already exists"
    else
    Response.Redirect("login.aspx")
    Conn.Close()
    end if
    it doesnt give an initial error when i load the page,the error pops up after i click the button to submit the details to db
    any ideas?

  • #4
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Something like:
    Code:
    'assuming txtEmail is the email address they enter
    cmd = new odbccommand("select count(email) from researcher where email = '" & txtEmail & "'",conn)
    If cmd.ExecuteScalar() <> 0 Then
    	'This email allready exists
    Else
    	'email doesnt exist do insert
    End If
    Good luck

  • #5
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi
    thanks for your reply
    i was playing around with that but unfortunately that wouldnt work either.
    in the end i set a unique constraint on the email column in my database and created my own error message page to show up if a user did try to enter an email already in the db...this seems to have done the trick
    thanks again for your help tho

  • #6
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Here is how i handle this

    Code:
     
       'check for existance of userID in database
        Function CheckUserName(ByVal userID As String) As DataSet
            Dim queryString As String = "SELECT userID FROM users WHERE userID = '" & userID & "'"
            Dim dbCommand As IDbCommand = New OleDbCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection
        
            Dim dataAdapter As IDbDataAdapter = New OleDbDataAdapter
            dataAdapter.SelectCommand = dbCommand
            Dim dataSet As DataSet = New DataSet
            dataAdapter.Fill(dataSet)
        
            Return dataSet
        End Function
        
        'check for existance of users email address in database
        Function CheckEmail(ByVal email As String) As DataSet
            Dim queryString As String = "SELECT email FROM users WHERE email = '" & email & "'"
            Dim dbCommand As IDbCommand = New OleDbCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection
        
            Dim dataAdapter As IDbDataAdapter = New OleDbDataAdapter
            dataAdapter.SelectCommand = dbCommand
            Dim dataSet As DataSet = New DataSet
            dataAdapter.Fill(dataSet)
        
            Return dataSet
        End Function
    
       Sub next_Click(sender As Object, e As EventArgs)
           If Page.IsValid Then
               Dim userDS As New DataSet
               userDS = CheckUserName(UserID.Text)
               Dim emailDS As New DataSet
               emailDS = CheckEmail(email.Text)
                If userDS.Tables(0).Rows.Count = 1 Then
                    'userid exists so display a message
                    p1Message.Text = "That UserID Exists.  Please Pick Another"
                ElseIf emailDS.Tables(0).Rows.Count = 1 Then
                    'email address exists so display a message
                    p1Message.Text = "Someone has registered with that Email address."
                End If
           End If
       End Sub

  • #7
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks miranda
    i might try incorporate + adapt your idea into my code when i get a chance,see which works better


  •  

    Posting Permissions

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