Run a SELECT query THEN an INSERT if it returns FALSE

[michaelmcguk]

Hi,

I’m currently learning .NET…at a snails’ pace, but getting there.

I’ve created a script that adds user data to a database, however, I’d like to check the email entered is unique within the database.

I’ve already started the query at comm2 but not sure how to progress the script so it tells the user if the email address is already taken.

protected void submitButton_Click(object sender, EventArgs e)
    {
        if (Page.IsValid)
        {
            // Define data objects
            SqlConnection conn;
            SqlCommand comm;
            SqlCommand comm2;

            // Read the connection string from Web.config
            string connectionString = ConfigurationManager.ConnectionStrings["Dorknozzle"].ConnectionString;

            // Initialize connection
            conn = new SqlConnection(connectionString);

            // Search for Existing Account
            comm2 = new SqlCommand("SELECT email FROM users WHERE email = @Email)", conn);
            
            // Enclose database code in Try-Catch-Finally
            try
            {
                // Open the connection
                conn.Open();
                // Execute the command
                comm2.ExecuteNonQuery();
                // Reload page if the query executed successfully
                
                // Create command 
                comm = new SqlCommand("INSERT INTO users (id, Title, Email, Password) VALUES ('', @Title, @Email, @Password)", conn);

                comm.Parameters.Add("@Title", System.Data.SqlDbType.NVarChar, 50);
                comm.Parameters["@Title"].Value = title.Text;

                comm.Parameters.Add("@Email", System.Data.SqlDbType.NVarChar, 50);
                comm.Parameters["@Email"].Value = email.Text;

                comm.Parameters.Add("@Password", System.Data.SqlDbType.NVarChar, 50);
                comm.Parameters["@Password"].Value = password.Text;

                // Enclose database code in Try-Catch-Finally
                try
                {
                    // Open the connection
                    conn.Open();
                    // Execute the command
                    comm.ExecuteNonQuery();
                    // Reload page if the query executed successfully
                    Response.Redirect("CreateAccountThanks.aspx");
                }
                catch
                {
                    // Display error message
                    dbErrorMessage.Text =
                        "Not a Unique DB Entry";
                }
                finally
                {
                    // Close the connection
                    conn.Close();
                }


            }
            catch
            {
                // Display error message
                dbErrorMessage.Text =
                    "Error submitting the help desk request! Please try again later, and/or change the entered data!";
            }
            finally
            {
                // Close the connection
                conn.Close();
            }  

        }
    }

Many thanks for any help.

[chieftain]

@michaelmcguk

            comm2.ExecuteNonQuery();
            // Reload page if the query executed successfully

You should use a DataReader to execute comm2, and if it has value, then display necessary message

[michaelmcguk]

Thanks for the reply

So this area here:

// Search for Existing Account

            comm2 = new SqlCommand("SELECT email FROM users WHERE email = @Email)", conn);

should be:

myDataReader = comm2.ExecuteReader();
if (myDataReader.Read())
 {
   
 } 

Sorry. I’m very new to C# and .NET. Can you show me how I would best do this?

Thank you once more

[praetor]

Take a look at this version (approximate code haven’t tested it)

protected void submitButton_Click(object sender, EventArgs e)
    {
        if (Page.IsValid)
        {
             
            // Read the connection string from Web.config
            string connectionString = ConfigurationManager.ConnectionStrings["Dorknozzle"].ConnectionString;
                
   using (var conn= new SqlCOnnection(connectionString))
{
conn.Open();          
using (var cmd= conn.CreateCommand())
{
      cmd.CommandText="SELECT 1 FROM users WHERE email = @Email";
     cmd.Parameters.AddWithValue("@email",email.Text);
    var rez=cmd.ExecuteScalar();
if (rez==null) //nothing returned, email doesn't exist
{
 //proceed with user insertion
}
else
{
 //handle duplicate email.
}
}
}
              
           }
}          

[chieftain]

myDataReader = comm2.ExecuteReader();
if (reader.HasRows)
{
      //Then email exists, display appropriate msg
}
else
{
     //Email doesn't exist, you can insert here
}

[wwb_99]

What do you want to do if the email exists?

In either case, you could easily do it all in one trip to the database. No need to double dip.

[praetor]

@wwb_99

What do you want to do if the email exists?

In either case, you could easily do it all in one trip to the database. No need to double dip.

In case that wonder how to do that, you need an unique index on the email column. When you insert the user, you’ll get the SqlException with number 2601 . That will be the sign you’re trying to insert duplicate values, well you violate an unique index

If you have an unique index on the username too, you have to check the actual message to see if it contains the word ‘email’

[michaelmcguk]

Thanks for the replies

I had thought of setting isUnique to ‘Yes’ in the email column, but couldn’t find how to do it in the DB Explorer part of Visual Web Developer.

[michaelmcguk]

@praetor

Take a look at this version (approximate code haven’t tested it)

protected void submitButton_Click(object sender, EventArgs e)
    {
        if (Page.IsValid)
        {
             
            // Read the connection string from Web.config
            string connectionString = ConfigurationManager.ConnectionStrings["Dorknozzle"].ConnectionString;
                
   using (var conn= new SqlCOnnection(connectionString))
{
conn.Open();          
using (var cmd= conn.CreateCommand())
{
      cmd.CommandText="SELECT 1 FROM users WHERE email = @Email";
     cmd.Parameters.AddWithValue("@email",email.Text);
    var rez=cmd.ExecuteScalar();
if (rez==null) //nothing returned, email doesn't exist
{
 //proceed with user insertion
}
else
{
 //handle duplicate email.
}
}
}
              
           }
}          

Based on this code, I’m using the following but I’ve a feeling it’s not right either:

if (Page.IsValid)
        {
            // Define data objects
            SqlConnection conn;
            SqlCommand comm;
            SqlCommand comm2;

            // Read the connection string from Web.config
            string connectionString = ConfigurationManager.ConnectionStrings["Dorknozzle"].ConnectionString;

            // Initialize connection
            conn = new SqlConnection(connectionString);

            // Search for Existing Account
            comm2 = new SqlCommand("SELECT email FROM users WHERE email = @Email)", conn);
            
            // Read the connection string from Web.config
            string connectionString = ConfigurationManager.ConnectionStrings["Dorknozzle"].ConnectionString;
               
            using (var conn= new SqlCOnnection(connectionString))
            {
            conn.Open();         
            using (var cmd= conn.CreateCommand())
            {
                  cmd.CommandText="SELECT 1 FROM users WHERE email = @Email";
                  cmd.Parameters.AddWithValue("@email",email.Text);
                var rez=cmd.ExecuteScalar();
            if (rez==null) //nothing returned, email doesn't exist
            { 
                    // Open the connection
                    conn.Open();
                    // Execute the command
                    //comm2.ExecuteNonQuery();
                    // Reload page if the query executed successfully

                    // Create command 
                    comm = new SqlCommand("INSERT INTO users (id, Title, Email, Password) VALUES ('', @Title, @Email, @Password)", conn);

                    comm.Parameters.Add("@Title", System.Data.SqlDbType.NVarChar, 50);
                    comm.Parameters["@Title"].Value = title.Text;

                    comm.Parameters.Add("@Email", System.Data.SqlDbType.NVarChar, 50);
                    comm.Parameters["@Email"].Value = email.Text;

                    comm.Parameters.Add("@Password", System.Data.SqlDbType.NVarChar, 50);
                    comm.Parameters["@Password"].Value = password.Text;

                    // Enclose database code in Try-Catch-Finally
                    try
                    {
                        // Open the connection
                        conn.Open();
                        // Execute the command
                        comm.ExecuteNonQuery();
                        // Reload page if the query executed successfully
                        Response.Redirect("CreateAccountThanks.aspx");
                    }
                    catch
                    {
                        // Display error message
                        dbErrorMessage.Text =
                            "Not a Unique DB Entry";
                    }
                    finally
                    {
                        // Close the connection
                        conn.Close();
                    }


                catch
                {
                    // Display error message
                    dbErrorMessage.Text =
                        "Error submitting the help desk request! Please try again later, and/or change the entered data!";
                }
                finally
                {
                    // Close the connection
                    conn.Close();
                }

            }

        }
    }

}

Can you show me where I’m going wrong?

I appreciate your patience with this.

[michaelmcguk]

Apologies for the last post.

The following code works perfectly:

```csharp

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

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

    }

    protected void submitButton_Click(object sender, EventArgs e)
    {
        if (Page.IsValid)
        {
            // Define data objects
            //SqlConnection conn;
            SqlCommand comm;
            SqlCommand comm2;
            SqlDataReader reader;

            // Read the connection string from Web.config
            string connectionString =
                ConfigurationManager.ConnectionStrings[
                "Dorknozzle"].ConnectionString;

            // Initialize connection
            //conn = new SqlConnection(connectionString);

            // Search for Existing Account
            comm2 = new SqlCommand("SELECT email FROM users WHERE email = @Email)");

            // Read the connection string from Web.config
            //string connectionString = ConfigurationManager.ConnectionStrings["Dorknozzle"].ConnectionString;

            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM users WHERE email = @Email";
                    cmd.Parameters.AddWithValue("@Email", email.Text);
                    var rez = cmd.ExecuteScalar();

                    if (rez == null) //nothing returned, email doesn't exist
                    {

                        // Create command 
                        comm = new SqlCommand("INSERT INTO users (Title, Email, Password) VALUES (@Title, @Email, @Password)", conn);

                        comm.Parameters.Add("@Title", System.Data.SqlDbType.NVarChar, 50);
                        comm.Parameters["@Title"].Value = title.Text;

                        comm.Parameters.Add("@Email", System.Data.SqlDbType.NVarChar, 50);
                        comm.Parameters["@Email"].Value = email.Text;

                        comm.Parameters.Add("@Password", System.Data.SqlDbType.NVarChar, 50);
                        comm.Parameters["@Password"].Value = password.Text;

                        // Execute the command
                        comm.ExecuteNonQuery();

                        // Reload page if the query executed successfully
                        Response.Redirect("CreateAccountThanks.aspx");

                    }
                }
            }
        }
    }
}