Email Validation (has another user already chosen this email?)

I have a registration form for my site. One of the fields is an email field, and I don't want multiple users to use the same email address.

I have successfully implemented a OnTextChanged validation using AJAX.

However, when I click Register, the error disappears and the user can proceed with the duplicate email address (which then gives a database error as the email is a unique primary key).

I have tried copied the email check code to the button_click section, but that didn't work.

Thanks in advance
Imports System.Data.OleDb
Imports System.Collections

Partial Class index
    Inherits System.Web.UI.Page

    Public Event ItemInserted As FormViewInsertedEventHandler
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
    End Sub

    Protected Sub tb_regemail_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tb_regemail.TextChanged
        CheckEmail()
    End Sub

    Protected Sub btn_register_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_register.Click
        CheckEmail()

        'This SQL string adds the new user
        Dim registerSQL As String
        registerSQL = "INSERT INTO [tblusers] ([Email], [Password], [First_Name], [Surname], [Gender], [DOB]) VALUES ('" _
            & tb_regemail.Text & "', '" & tb_regpassword1.Text & "' , '" & tb_regfirstname.Text & "', '" _
            & tb_regsurname.Text & "', '" & dd_reggender.Text & "', '" & regdob & "')"
        Dim RegisterDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/database.mdb"))
        Dim RegisterCommand As New OleDbCommand(registerSQL, RegisterDataConn)
        RegisterDataConn.Open()
        RegisterCommand.ExecuteNonQuery()
        RegisterDataConn.Close()
    End Sub

    Protected Sub val_regemail3_ServerValidate(ByVal source As Object, ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs) Handles val_regemail3.ServerValidate
        CheckEmail()
    End Sub

    Protected Sub CheckEmail()
        'has the chosen email address already been chosen by another user?
        Dim checkemailSQL As String
        checkemailSQL = "SELECT User_ID From tblusers WHERE tblusers.Email='" & tb_regemail.Text & "'"
        Dim CheckEmailDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/database.mdb"))
        Dim CheckEmailCommand As New OleDbCommand(checkemailSQL, CheckEmailDataConn)
        CheckEmailDataConn.Open()
        Dim CheckEmailDBReader As OleDbDataReader = CheckEmailCommand.ExecuteReader()

        If CheckEmailDBReader.Read() Then
            val_regemail3.IsValid = False
        Else
            val_regemail3.IsValid = True
        End If
        CheckEmailDBReader.Close()
        CheckEmailDataConn.Close()
    End Sub
End Class

----------

<%@ Page Language="VB" MasterPageFile="~/master_nouser.master" AutoEventWireup="false" CodeFile="index.aspx.vb" Inherits="index" title="Visage Warwick" %>

<asp:Content ID="Content2" ContentPlaceHolderID="cph_left" Runat="Server">
<asp:ScriptManager ID="sm_register" runat="server"></asp:ScriptManager>

    <p>Sign-up is easy. Just fill in some details below and you’re good to go.</p>
            
    <asp:Panel runat="server" ID="pnl_register" DefaultButton="btn_register">
    <div id="form">
        
        <asp:UpdatePanel ID="ajx_email" runat="server" UpdateMode="Conditional" ChildrenAsTriggers="true">
        <ContentTemplate>
        <p><asp:Label AssociatedControlID="tb_regemail" runat="server" ID="lbl_regemail">
        Email Address:
        <asp:TextBox runat="server" id="tb_regemail" MaxLength="40" Text="Email Address" TabIndex="4" CssClass="d" 
            OnTextChanged="tb_regemail_TextChanged" AutoPostBack="true" />
            
            <asp:RequiredFieldValidator display="Dynamic" runat="server" ControlToValidate="tb_regemail" 
                ID="val_regemail" ValidationGroup="RegisterValidators" ErrorMessage="Your email is required">
            </asp:RequiredFieldValidator>
            
            <asp:RegularExpressionValidator ID="val_regemail2" ControlToValidate="tb_regemail" Display="Dynamic" 
            ErrorMessage="Email address must be in the form name@domain.com" CssClass="error" ForeColor="White"
            Text="Invalid email address" ValidationGroup="RegisterValidators" runat="server" 
            ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*">
            </asp:RegularExpressionValidator>
            
            <asp:CustomValidator ID="val_regemail3" ControlToValidate="tb_regemail" Display="Dynamic" 
            ErrorMessage="Another user has this email" CssClass="error" ForeColor="White"
            Text="Another user has this email" ValidationGroup="RegisterValidators" runat="server">
            </asp:CustomValidator>
        </asp:Label></p>
        </ContentTemplate>
            <Triggers>
                <asp:AsyncPostBackTrigger ControlID="tb_regemail" EventName="TextChanged" />
                <asp:AsyncPostBackTrigger ControlID="btn_register" EventName="Click" />
            </Triggers>
        </asp:UpdatePanel>
         
        <p style="text-align: right;"><asp:Button runat="server" ID="btn_register" ValidationGroup="RegisterValidators" TabIndex="13"
            Text="Register" CommandName="Insert"/></p>
                    
    </div>
    </asp:Panel>
    
    <asp:AccessDataSource ID="ads_register" runat="server" DataFile="~/App_Data/database.mdb" 
            InsertCommand="INSERT INTO [tblusers] ([Email], [Password], [First_Name], [Surname], [Gender], [DOB]) VALUES (?, ?, ?, ?, ?, ?)" 
            SelectCommand="SELECT [Email], [Password], [First_Name], [Surname], [Gender], [DOB] FROM [tblusers]" >
            <InsertParameters>
                <asp:Parameter Name="Email" Type="String" />
                <asp:Parameter Name="Password" Type="String" />
                <asp:Parameter Name="First_Name" Type="String" />
                <asp:Parameter Name="Surname" Type="String" />
                <asp:Parameter Name="Gender" Type="String" />
                <asp:Parameter Name="DOB" Type="DateTime" />
            </InsertParameters>
        </asp:AccessDataSource>
        
</asp:Content>

Open in new window

matthallettAsked:
Who is Participating?
 
guru_samiConnect With a Mentor Commented:
Try like this:

Protected Sub btn_register_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_register.Click
        CheckEmail()
      If Page.IsValid Then
        'This SQL string adds the new user
        Dim registerSQL As String
        registerSQL = "INSERT INTO [tblusers] ([Email], [Password], [First_Name], [Surname], [Gender], [DOB]) VALUES ('" _
            & tb_regemail.Text & "', '" & tb_regpassword1.Text & "' , '" & tb_regfirstname.Text & "', '" _
            & tb_regsurname.Text & "', '" & dd_reggender.Text & "', '" & regdob & "')"
        Dim RegisterDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/database.mdb"))
        Dim RegisterCommand As New OleDbCommand(registerSQL, RegisterDataConn)
        RegisterDataConn.Open()
        RegisterCommand.ExecuteNonQuery()
        RegisterDataConn.Close()
End If
    End Sub

Protected Sub val_regemail3_ServerValidate(ByVal source As Object, ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs) Handles val_regemail3.ServerValidate
     args.IsValid   CheckEmail()
    End Sub

Protected Sub CheckEmail() As Boolean
        Dim isValid As Boolean
        'has the chosen email address already been chosen by another user?
        Dim checkemailSQL As String
        checkemailSQL = "SELECT User_ID From tblusers WHERE tblusers.Email='" & tb_regemail.Text & "'"
        Dim CheckEmailDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/database.mdb"))
        Dim CheckEmailCommand As New OleDbCommand(checkemailSQL, CheckEmailDataConn)
        CheckEmailDataConn.Open()
        Dim CheckEmailDBReader As OleDbDataReader = CheckEmailCommand.ExecuteReader()

        If CheckEmailDBReader.Read() Then
            isValid = False
        Else
            isValid  = True
        End If
        CheckEmailDBReader.Close()
        CheckEmailDataConn.Close()
   Return isValid
    End Sub
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Two things:
First, vette the e-mail address field before you use it in your SQL.  As it is, you're just begging someone to use SQL injection against you.
Second, why not check the e-mail address when the user posts the page?  You can still use a validaiton control and it lets you check all the information at once (instead of doing a lot of little postbacks).
0
 
matthallettAuthor Commented:
This is for a university project, we've been told not to use security other than passwords.

As for the second point, I'd like it to be done dynamically. I've seen many sites do this before, it should be possible.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
matthallettAuthor Commented:
It doesn't like the line
args.IsValid = CheckEmail()
from
val_regemail3_ServerValidate
It doesn't like it if it is args.IsValid(CheckEmail())

It also doesn't like the As Boolean after CheckEmail() and the Return isValid at the end of that sub.

Played about to get rid of the errors but the validation is still ineffective.
0
 
guru_samiCommented:
-->Played about to get rid of the errors but the validation is still ineffective.
So the code is executing without any errors but validation is still now working you mean?
0
 
guru_samiCommented:
typo..
So the code is executing without any errors but validation is still NOT working you mean?
0
 
matthallettAuthor Commented:
The code as you gave it to me doesn't run. When I edit it to remove the errors, it runs but the validation problem still happens.
0
 
guru_samiCommented:
did you set breakpoints at following lines:

If CheckEmailDBReader.Read() Then
 bp--->           isValid = False
        Else
 bp--->           isValid  = True
        End If

And also at this line:
--->  If Page.IsValid Then

See what values are assigned to isValid ..

Or post the code you have after you made modification and also your email textbox and validation control
0
 
matthallettAuthor Commented:
I've set various breakpoints and found the following:

Validation on tb_regemail_OnTextChange works fine

But
Protected Sub val_regemail3_ServerValidate(ByVal sender As Object, ByVal e As ServerValidateEventArgs) Handles val_regemail3.ServerValidate
isn't triggered until the button is clicked AND the rest of the form is valid

The register button event is not triggered when there are other errors, but the Email already chosen error message still disappears... but if there are no other errors than it does appear when clicked.

So we've managed to stop the user progressing if they have a non-unique email.

But is there any way to make the error message display with the rest of the group validation errors when the button is clicked?
0
 
CodeCruiserCommented:
>But is there any way to make the error message display with the rest of the group validation errors when the button is clicked?

Use the same validation group on all controls. But if you have client side validation then this would not help.
0
 
CodeCruiserConnect With a Mentor Commented:
The reason you got errors with sami's code is because its declared as sub

So change this line

Protected Sub CheckEmail() As Boolean

to
Protected Function CheckEmail() As Boolean

And
End Sub

to

End Function
0
 
guru_samiCommented:
Upon your textboxchanged do this....

Protected Sub tb_regemail_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tb_regemail.TextChanged
        'CheckEmail()
       val_regemail3.Validate()
    End Sub
0
All Courses

From novice to tech pro — start learning today.