Click here to Skip to main content
12,396,951 members (65,719 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL-Server
I have written some code(shown in below) to get a database result to my C# application...
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 28-Nov-12 4:20am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

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:
object nullableValue = dbCommand.ExecuteScalar();
int myValue;
if (nullableValue == null || nullableValue == DBNull.Value)
{
  myValue = 0;
}
else
{
  int.TryParse(nullableValue, out myValue);
}
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

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:
    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.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

There's a way to turn this on the head, and not need to test for null. Change your command text to
"SELECT COALESCE(MAX(PVRID), 0) FROM PVR";
This code will return 0 if the value in MAX(PVRID) is null.
  Permalink  
Comments
Gregory.Gadow 28-Nov-12 11:23am
   
Clean and elegant.
RaisKazi 28-Nov-12 11:30am
   
Clean and simple, 5ed.
CIDev 29-Nov-12 9:49am
   
Very nice, +5
digimanus 4-Feb-15 4:38am
   
Best way, solve this in the DB! +5
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Add this to your code:

if (dbCommand.ExecuteReader() != null)
{
     int maxPVRNo = Convert.ToInt32(dbCommand.ExecuteScalar());
 
}
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 6

You can check if the value of the scalar is DbNull

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

if (maxPvrNo != DbNull.Value && maxPvrNo != null ) {
  Permalink  
Comments
digimanus 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 4 Feb 2015
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100