Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have written some code(shown in below) to get a database result to my C# application...
C#
SqlCommand dbCommand = new SqlCommand();
      dbCommand.CommandText = "SElECT MAX(PVRID) FROM PVR";
      int maxPVRNo = Convert.ToInt32(dbCommand.ExecuteScalar());

      if(maxPVRNo == 1)
          //do something
      if(maxPVRNo ==0)
          //do something


when the PVR data table have no records it will return the null value.In this case my conversion code is fail because null value cannot be convert to integer value...

So i want to know, how to check null value in this kind of situation ( When the ExecuteScalar() return a null value )
Posted

If you really want to test for null, don't return the value as an int. By default, ints aren't nullable types, but objects are - so, get the value as an object and test that:
C#
object nullableValue = dbCommand.ExecuteScalar();
int myValue;
if (nullableValue == null || nullableValue == DBNull.Value)
{
  myValue = 0;
}
else
{
  int.TryParse(nullableValue, out myValue);
}
 
Share this answer
 
If you can alter the query, Pete's solution is the best. If you cannot, then you will need to add some additional tests.

VB has a very useful function, IsNumeric. Even though you are using C#, you can still access the method by adding a reference to Microsoft.VisualBasic. (There is probably a C# equivalent, but I don't know what it is. Hey, a .Net method is a .Net method.) So you might want to try this:
C#
    SqlCommand dbCommand = new SqlCommand();
    dbCommand.CommandText = "SElECT MAX(PVRID) FROM PVR";

    object result = dbCommand.ExecuteScalar();

    if (Microsoft.VisualBasic.Information.IsNumeric(result))
    {
        int maxPVRNo = Convert.ToInt32(result);
        if (maxPVRNo == 0)
        {
            // one thing
        }
        if (maxPVRNo == 1)
        {
            // another thing
        }
    }
    else
    {
        // no thing
    }

}

What you are doing here is making the conversion to an integer after you have determined that the result is, in fact, a number. The only non-numeric value that MAX would return is null, so you can catch that as well.
 
Share this answer
 
There's a way to turn this on the head, and not need to test for null. Change your command text to
C#
"SELECT COALESCE(MAX(PVRID), 0) FROM PVR";
This code will return 0 if the value in MAX(PVRID) is null.
 
Share this answer
 
Comments
Gregory Gadow 28-Nov-12 11:23am    
Clean and elegant.
RaisKazi 28-Nov-12 11:30am    
Clean and simple, 5ed.
BillW33 29-Nov-12 9:49am    
Very nice, +5
Herman<T>.Instance 4-Feb-15 4:38am    
Best way, solve this in the DB! +5
Add this to your code:

C#
if (dbCommand.ExecuteReader() != null)
{
     int maxPVRNo = Convert.ToInt32(dbCommand.ExecuteScalar());

}
 
Share this answer
 
You can check if the value of the scalar is DbNull

C#
SqlCommand dbCommand = new SqlCommand();
            dbCommand.CommandText = "SElECT MAX(PVRID) FROM PVR";

           if (maxPvrNo != DbNull.Value) {

               int maxPVRNo = Convert.ToInt32(dbCommand.ExecuteScalar());
 
               if(maxPVRNo == 1)
                //do something
               if(maxPVRNo ==0)
                //do something
            }


To be safe you could also just check that it is != null

C#
if (maxPvrNo != DbNull.Value && maxPvrNo != null ) {
 
Share this answer
 
Comments
Herman<T>.Instance 4-Feb-15 4:37am    
This solution goes wrong. You are checking for DBNUll.Value before the ExecuteScalar() function is executed
AnalogNerd 4-Feb-15 13:46pm    
You're absolutely correct, I missed that. Good catch.

ExecuteScalar would have to be run first into an object, and then you could check that object for DbNull.Value. If it isn't null, conver to an integer.

Basically, Pete's answer above is what I was going for (but didn't quite make it).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900