Check if a record exists in the database
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.
}
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
}
sqlConnection.Open();
using (var sqlCommand = new SqlCommand("SELECT COUNT(*) FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "'", sqlConnection))
{
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
lblMessage.Text ="Record Already Exists.";
}
else
{
lblMessage.Text ="Record Not Exists.";
}
reader.Close();
reader.Dispose();
}
sqlConnection.Close();