penmas penmas - 10 months ago 56
ASP.NET (C#) Question

Prevent Duplicate E-mail Address Submissions c#

I have the following c# code below that I am trying to include additional functionality to prevent duplicate e-mail addresses from being entered into the database using an form. Below is the code, currently it is inserting all entries, duplicate or not, into the database.

using System.Data.OleDb;
using System.Configuration;

public partial class Default2 : System.Web.UI.Page
protected void Page_Load(object sender, EventArgs e)


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

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>

ErrorMessage="Please enter your name!"
runat="server" />

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

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>


Answer Source

As you are a doing a SELECT statement, you need to the ExecuteScalar method on OleDbCommand to get the results. As per the MSDN documentation, ExecuteScalar returns:

The first column of the first row in the result set, or a null reference if the result set is empty.

In your case, the return will be the value of COUNT(ID) from your query.

ExecuteNonQuery is for operations which modify data and will return the number of rows affected by the query. As per the MSDN documentation:

You can use the ExecuteNonQuery to perform catalog operations, for example, to query the structure of a database or to create database objects such as tables, or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements. Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

So change you code as follows:

string query = "SELECT COUNT(ID) FROM Table1 WHERE pEmail='" + TextBox2.Text + "'"; // note: vulnerable to SQLInjection.
OleDbCommand cmd = new OleDbCommand(query, conn);
int count = (int)cmd.ExecuteScalar();

if (count > 0)
  // etc.

Update: I would also recommend using a Transaction on your operation to make sure that a duplicate email address is not added in between your checking for duplicates and you doing the actual insert.

Update 2: Based on the updated code that switches to using ExecuteScalar, this line:

string query = "SELECT COUNT(ID) FROM Table1 WHERE pEmail=' " + TextBox2.Text + "'";

Should be:

string query = "SELECT COUNT(ID) FROM Table1 WHERE pEmail='" + TextBox2.Text + "'";

(Extra whitespace character between pEmail=' and " removed.