asked on

How to test for DBNull when using ExecuteScalar?

I have the code below. It returns DBNull and crashes. How do I test for DBNull using ExecuteScalar?

string selectStatement = "select sum(cast(NbrOfCopies as integer)) from tBooks";
            FbCommand cmd = new FbCommand(selectStatement, bookConn);
            if (bookConn.State == ConnectionState.Closed)
                bookConn.Open();
            int Count = Convert.ToInt32(cmd.ExecuteScalar());  //  <-- crashes here!
            lblTotalCount.Text = "Total In Inventory:    " + Count.ToString();

Open in new window

matthewrhoades

I would add another line for the conversion.

Sorry for the sloppy looking formatting.

ing selectStatement = "select sum(cast(NbrOfCopies as integer)) from tBooks";
            FbCommand cmd = new FbCommand(selectStatement, bookConn);
            if (bookConn.State == ConnectionState.Closed)
                bookConn.Open();
            int Count = cmd.ExecuteScalar();  //  <-- crashes here!
if isDBNull(Count) = False then
myCount = ToInt32.Convert(Count)
            lblTotalCount.Text = "Total In Inventory:    " + myCount.ToString();

Open in new window

ASKER CERTIFIED SOLUTION

matthewrhoades

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rmmarsh

ASKER

I found a better way:

int Count = cmd.ExecuteScalar() is DBNull ? 0 : Convert.ToInt32(cmd.ExecuteScalar());

But thank you for putting me on the right track...

Guy Hengel [angelIII / a3]

mmarsh: your "better" method risks to run the command object 2 times!!!!

hence:


object result = cmd.ExecuteScalar();
int Count = (result == DBNull.Value ? 0 : Convert.ToInt32(result));

Open in new window