CODEWITHSUNDEEP
c#sqlsql-server
rai nalasa
rai nalasa

Reputation: 859

Check if user exists in SQL Server user table

I am trying to make a registration form. This code should stop the registration if there is a duplicate of username.

I actually copied this on this answer tried to modify it on my own so I could somehow learn how it works.

Here is my code:

private void register_user()
{
    con.Open();

    bool exist = false;

    // Command that checks if username exist
    cmd = new SqlCommand(@"SELECT COUNT(*) FROM users1 WHERE Username = '@username'", con);

    cmd.Parameters.AddWithValue("@username", txtRegUsername.Text);

    exist = (int)cmd.ExecuteScalar() > 0;

    // If user exist gives error
    if (exist == true)
        lblResults.Text = "Username already exist!";
    else 
    {
        cmd = new SqlCommand(@"INSERT INTO users1 (Fname, Lname, Mname,
                                                   Username, Password, email, user_type)
                               VALUES (@first_name, @last_name, @middle_name,
                                       @username, @password, @email, @user_type)", con);

        cmd.Parameters.AddWithValue("@first_name", txtFname.Text);
        cmd.Parameters.AddWithValue("@last_name", txtLname.Text);
        cmd.Parameters.AddWithValue("@middle_name", txtMi.Text);
        cmd.Parameters.AddWithValue("@username", txtRegUsername.Text);
        cmd.Parameters.AddWithValue("@password", txtRegPassword.Text);
        cmd.Parameters.AddWithValue("@email", txtEmail.Text);
        cmd.Parameters.AddWithValue("@user_type", "user");

        cmd.ExecuteNonQuery();
    }

    con.Close();
}

My problem is that my code allows the registration of Username which already exist. Overall its working.

Upvotes: 0

Views: 2577

Answers (3)

Alejandro A
Alejandro A

Reputation: 1190

exist is false then you return the value of the SelectCount.

I would do something as

If((int)cmd.ExecuteScalar() > 0)
{
exist=true;
}

Also remove the ' ' from @username on the query as others said.

Upvotes: 0

Dev Try
Dev Try

Reputation: 211

Try the below code.. Hope this will help you.

cmd = new SqlCommand(@"SELECT COUNT(*) FROM users1 WHERE upper(Username) = upper(@username)", con);

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460038

Instead of 

SELECT COUNT(*) FROM users1 WHERE Username = '@username'

use

SELECT COUNT(*) FROM users1 WHERE Username = @username

Otherwise that's not a parameter but a static value for Username.

Side note: I would not count records if I want to know if something exists.

This is more efficient:

SELECT 
    CASE WHEN EXISTS(SELECT 1 FROM users1 WHERE Username = @username)
       THEN 1 
       ELSE 0 
    END AS DoesUserExist

Upvotes: 5

Related Questions