Check if a record exists in the database

Question

I am using these lines of code to check if the record exists or not.

SqlCommand check_User_Name = new SqlCommand("SELECT * FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "') ", conn);

int UserExist = (int)check_User_Name.ExecuteScalar();

But I am getting an error:

Object reference not set to an instance of an object.

I want to do:

if (UserExist > 0)
    // Update record

else

    // Insert record

Answer 1

ExecuteScalar returns the first column of the first row. Other columns or rows are ignored. It looks like your first column of the first row is null, and that's why you get NullReferenceException when you try to use the ExecuteScalar method.

From MSDN;

Return Value

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

You might need to use COUNT in your statement instead which returns the number of rows affected...

Using parameterized queries is always a good practise. It prevents SQL Injection attacks.

And Table is a reserved keyword in T-SQL. You should use it with square brackets, like [Table] also.

As a final suggestion, use the using statement for dispose your SqlConnection and SqlCommand:

SqlCommand check_User_Name = new SqlCommand("SELECT COUNT(*) FROM [Table] WHERE ([user] = @user)" , conn);
check_User_Name.Parameters.AddWithValue("@user", txtBox_UserName.Text);
int UserExist = (int)check_User_Name.ExecuteScalar();

if(UserExist > 0)
{
   //Username exist
}
else
{
   //Username doesn't exist.
}

Answer 2

The ExecuteScalar method should be used when you are really sure your query returns only one value like below:

SELECT ID FROM USERS WHERE USERNAME = 'SOMENAME'

If you want the whole row then the below code should more appropriate.

SqlCommand check_User_Name = new SqlCommand("SELECT * FROM Table WHERE ([user] = @user)" , conn);
check_User_Name.Parameters.AddWithValue("@user", txtBox_UserName.Text);
SqlDataReader reader = check_User_Name.ExecuteReader();
if(reader.HasRows)
{
   //User Exists
}
else
{
   //User NOT Exists
}