Code Not Preventing Duplicate E-mail Addresses in Database

Question

The code I have is allowing duplicate e-mails to be added to the database. I added a line of code before allowing entry into the database to prevent duplicate e-mail addresses to be added, however with this code I am still getting duplicate e-mails. I have provided both the form code in asp.net & the c# code. Please help.

Originally I was getting an error & I debugged the code in VS & realized that I actually had the wrong specification for the email entry, that has been corrected. In the VS debugger I see values for TextBox1.Text & TextBox2.Text, i also see the e-mail address passing through the string query = the issue however is that even if the e-mail is already in the database, it still gets added again. Any improvements to fix this issue with my code? Is my logic wrong perhaps?

c# code:

protected void Button1_Click(object sender, EventArgs e)
    {
        OleDbConnection con = new OleDbConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["northwind"].ToString();
        con.Open();
        string query = "SELECT COUNT(ID) FROM Table1 WHERE pEmail= '" + TextBox2.Text +"'";
        OleDbCommand cmd = new OleDbCommand(query, con);

        var count = cmd.ExecuteNonQuery();

        if (count > 0)
        {
            Label1.Text = "email is already in use";
        }
        else {
        cmd.CommandText = "insert into[Table1](pName, pEmail)values(@nm,@em)";
        cmd.Parameters.AddWithValue("@nm", TextBox1.Text);
        cmd.Parameters.AddWithValue("@em", TextBox2.Text);
        cmd.Connection = con;
        int a = cmd.ExecuteNonQuery();
        if (a>0)
            {
                Label1.Text = "Inserted Sucessfully!";
            }
        }
    }
}

Form Code:

<form id="form1" runat="server">
<div style="height: 138px">

    Enter Name:<asp:TextBox ID="TextBox1" runat="server" style="margin-left: 12px"></asp:TextBox>

    <asp:RequiredFieldValidator 
    id="reqName" 
    ControlToValidate="TextBox1"
    Style="color:Red"   
    ErrorMessage="Please enter your name!"
    runat="server"  />

    <br />
    Enter Email:&nbsp;&nbsp;&nbsp;
    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

    <asp:RegularExpressionValidator 
    id="ValidEmail" 
    ControlToValidate="TextBox2" 
    Style="color:Red"
    ValidationExpression="^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$"
    ErrorMessage="Invalid Email Entry" 
    runat="server" />

    <br />
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Submit" />
    <br />
    <asp:Label ID="Label1" runat="server"></asp:Label>

</div>
</form>

I suggest adding a unique constraint to the database and capturing it in your code. But you may prefer a C# option. — Nick.Mc, Oct 27 '16 at 03:58
Possible duplicate of [Prevent Duplicate E-mail Address Submissions c#](http://stackoverflow.com/questions/40273881/prevent-duplicate-e-mail-address-submissions-c-sharp) — Adrian Sanguineti, Oct 27 '16 at 04:08
Possible duplicate of [Check if User Data is Already in Database](http://stackoverflow.com/questions/40252638/check-if-user-data-is-already-in-database) — Nick.Mc, Oct 27 '16 at 05:11

score 3 · Answer 1 · edited May 23 '17 at 12:19

You should use ExecuteScalar instead of ExecuteNonQuery to get the row count

Original:

var count = cmd.ExecuteNonQuery();

Suggest to change:

var count = cmd.ExecuteScalar();

Refer to https://stackoverflow.com/a/4269651/1050927

The ExecuteNonQuery Method returns the number of row(s) affected by either an INSERT, an UPDATE or a DELETE. This method is to be used to perform DML (data manipulation language) statements as stated previously.

The ExecuteScalar Method will return a single value in the first row, first column from a SELECT statement. This method is to be used when you expect only one value from the query to be returned.

score 2 · Answer 2 · answered Oct 27 '16 at 04:19

Use ExecuteScalar and Convert the result to int. I also recommend you to change the sql query concatenation to parameters, and if you are using ASP.NET Validators, you must check the property IsValid of the page as it will tell you if the controls has passed validation (remember that users can disable javascript and post the form).

protected void Button1_Click(object sender, EventArgs e)
{
    if (IsValid)
    {
        using (var con = new OleDbConnection())
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["northwind"].ToString();
            con.Open();
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = "SELECT COUNT(ID) FROM Table1 WHERE pEmail= @em";
                cmd.Parameters.AddWithValue("@em", TextBox2.Text);
                int count = Convert.ToInt32(cmd.ExecutScalar());
                if (count > 0)
                {
                    Label1.Text = "email is already in use";
                }
                else
                {
                    cmd.CommandText = "insert into[Table1](pName, pEmail)values(@nm, @em)";
                    cmd.Parameters.AddWithValue("@nm", TextBox1.Text);
                    // not need to add @em parameter, it was added previously
                    int insertedRows = cmd.ExecuteNonQuery();
                    if (insertedRows > 0)
                    {
                        Label1.Text = "Inserted Sucessfully!";
                    }
                }
            }
        }
    }
}

Code Not Preventing Duplicate E-mail Addresses in Database

2 Answers2