rai nalasa rai nalasa - 1 year ago 157
SQL Question

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()

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!";
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");



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

Answer Source

Instead of

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


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:

    CASE WHEN EXISTS(SELECT 1 FROM users1 WHERE Username = @username)
       THEN 1 
       ELSE 0 
    END AS DoesUserExist
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download