How to check if SQL database already has the entered Username?

Question

I've written this registration form which adds data to my SQL Server database. What I want is an exception when the user enters a username that is already in the database.

protected void Button1_Click(object sender, EventArgs e)
{
        try
        {
            SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
            conn2.Open();    

            string CheckUser = "select Username from UserData where Username like @Username";

            SqlCommand com2 = new SqlCommand(CheckUser, conn2);        
            com2.Parameters.AddWithValue("@Username", "'%"+ UsernameTextBox.Text +"%'");

            com2.ExecuteNonQuery();

            int IsMatch = Convert.ToInt32(com2.ExecuteScalar().ToString());
            conn2.Close();

            if (IsMatch == 0)
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
                conn.Open();

                string InsertQuery = "insert into UserData (Username, Email, Password, Country) values (@Username, @Email, @Password, @Country)";

                SqlCommand com = new SqlCommand(InsertQuery, conn);
                com.Parameters.AddWithValue("@Username", UsernameTextBox.Text);
                com.Parameters.AddWithValue("@Email", EmailTextBox.Text);
                com.Parameters.AddWithValue("@Password", PasswordTextBox.Text);
                com.Parameters.AddWithValue("@Country", CountryDropDownList.SelectedItem.ToString());

                com.ExecuteNonQuery();

                Response.Redirect("Manager.aspx");

                conn.Close();
            }
            else
            {
                Response.Write("User Already Exists!");
            }               
        }
        catch (Exception ex)
        {
            Response.Write(Convert.ToString(ex));
        }
}

When I run it, I get an exception on the following line:

int IsMatch = Convert.ToInt32(com2.ExecuteScalar().ToString());

Why are you executing your first command twice - first with `com2.ExecuteNonQuery();` and two lines later with `com2.ExecuteScalar();` ?? Execute it **once** - that's quite good enough! — marc_s, Oct 05 '14 at 17:25

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

Blam's second solution works, but the IsMatch can be simplified a bit by casting to int instead of going to string and parsing.

This should also be handled at the database level. Set a primary key on your username column:

ALTER TABLE UserData ADD CONSTRAINT
PK_UserData PRIMARY KEY CLUSTERED (Username)

If you do it this way, then you don't even have to check for duplicates explicitly, you can just try to create the user and handle the exception if it fails:

        try
        {
            using (var conn = new SqlConnection((ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString)))
            {
                conn.Open();

#if DOUBLE_CHECK
                string CheckUser = "select count(*) from UserData where Username = @Username";
                SqlCommand com2 = new SqlCommand(CheckUser, conn);
                com2.Parameters.AddWithValue("@Username", UsernameTextBox.Text);
                if ((int)com2.ExecuteScalar() > 0)
                {
                    Response.Write("User already exists");
                    return;
                }
#endif                  
                string InsertQuerry = "insert into UserData (Username,Email,Password,Country) values (@Username,@Email,@Password,@Country)";
                SqlCommand com = new SqlCommand(InsertQuerry, conn);
                com.Parameters.AddWithValue("@Username", UsernameTextBox.Text);
                com.Parameters.AddWithValue("@Email", EmailTextBox.Text);
                com.Parameters.AddWithValue("@Password", PasswordTextBox.Text);
                com.Parameters.AddWithValue("@Country", CountryDropDownList.SelectedItem.ToString());
                com.ExecuteNonQuery();
                Response.Redirect("Manager.aspx");
            }
        }
        catch (SqlException se)
        {
            if (se.Errors.OfType<SqlError>().Any(e => e.Number == 2627))
            {
                Response.Write("User already exists");
            }
            else
            {
                Response.Write(se.ToString());                  
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }

If you handle the exception this way, the #if DOUBLE_CHECK section is redundant and can be removed. An attempt to add duplicate name will cause a SQL error and exception, and this will detect and handle the "duplicate key" error.

Two unrelated notes on your code:

Response.Redirect() will abort the current thread and your conn.Close() will not be called. Use a using() to ensure it's called.
Storing a password in the database as plain text is a disaster waiting to happen. PLEASE take a look at Best way to store password in database for some ideas about how to do this correctly

paparazzo · Accepted Answer · 2014-10-05T16:39:31.030

0

That won't return an integer

string CheckUser = "select count(*) from UserData where Username like @Username";
SqlCommand com2 = new SqlCommand(CheckUser, conn2);        
com2.Parameters.AddWithValue("@Username", "'%"+ UsernameTextBox.Text +"%'");
int IsMatch = Convert.ToInt32(com2.ExecuteScalar().ToString());

And you don't need to use two different connections.
Just use one and close it in a Finally.

string CheckUser = "select count(*) from UserData where Username = @Username";
SqlCommand com2 = new SqlCommand(CheckUser, conn2);        
com2.Parameters.AddWithValue("@Username", UsernameTextBox.Text );
int IsMatch = Convert.ToInt32(com2.ExecuteScalar().ToString());

edited Oct 05 '14 at 16:39

answered Oct 05 '14 at 16:24

paparazzo

44,497
23
105
176

but the IsMatch still returns 0 even when i enter a Username that is already in the database – Dmetrey Oct 05 '14 at 16:33
Then you you don't have proper syntax – paparazzo Oct 05 '14 at 16:38
Please note that with '%' in the query paramer you will get a hit if you query for "Deal" and already have "BillTheDealBreaker" in the user list. Also, using '%'s as the first character forces a full table scan, which might be an issue if you have a lot of data. I would suggest a simple equality test. – LL1138 Oct 05 '14 at 18:00
@LL1138 I know how Like works. That is the syntax from the OP. And it is an accepted answer. – paparazzo Oct 05 '14 at 20:00

score 0 · Answer 3 · answered Oct 05 '14 at 16:44

This returns 0 or 1. This should fix your issue. Looks like you need to return an int type. Or you could change it to bool if you want. Either way, this sql statement should help! :)

select
isnull(convert(bit,(select top 1 case 
                    when username != '' then 1
                    else 0 end 
from UserData
where username like @Username)),0)

How to check if SQL database already has the entered Username?

3 Answers3