If Condition for Query with Empty Result

Question

I have written this SQL query:

 SqlCommand cmd2 = new SqlCommand();
        cmd2.Connection = new SqlConnection(Class1.CnnStr);
        cmd2.CommandText = "SELECT MAX(Code) FROM Table WHERE Number=@Number ";
        cmd2.Connection.Open();
        cmd2.Parameters.AddWithValue("@Number", Hidden_txt.Text);
        cmd2.ExecuteNonQuery();

and I would like to add some if condition like:

if (cmd2.ExecuteScalar()=="Null")
{....}

How can I add an if condition for when my query does not have an answer?

Answer 1

You can use a reader like this:

This assumes that the type of Code is integer, so change as necessary

SqlDataReader reader = cmd2.ExecuteReader;
int code = 0;
if (reader.Read) {
     //whatever if it has a result
     code = reader.GetInt32(0);
} else {
    //Whatever if it finds nothing
}

Answer 2

Remove the call to cmd2.ExecuteNonQuery, then add something like this:

object maxCode = cmd2.ExecuteScalar();
if (maxCode == null)
{
    // Null
}
else
{
    // do something with maxCode, you probably want to cast - e.g. (int)maxCode
}

Answer 3

According to MSDN:

If the first column of the first row in the result set is not found, a null reference (Nothing in Visual Basic) is returned. If the value in the database is null, the query returns DBNull.Value.

So, you could just write:

if (cmd2.ExecuteScalar() == null)
{....}

Alternatively, you could use ExecuteReader, then check whether returned reader HasRows.