KnowledgeOverflow KnowledgeOverflow - 2 months ago 16
C# Question

Check if record exists in database

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

ExecuteScalar returns the first column of first row. Other columns or rows are ignored. Looks like your first column of first row is null, that's why you get NullReferenceException when you try to use 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 on T-SQL. You should use it with square brackets like [Table] also.

As a final suggestion, use 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.
}
Comments