How to handle ExcuteScalar exceptions on Null values?

Question

I have a problem, this works if the value returned isn't null, however I get a System.NullreferenceException when the return is null. I have tried many things but cant seem to crack it. Any help here would be appreciated.

string HwPlts = "select top 1 plts from (select sum(ISNULL(Heywood_Plts, 0)) as plts, date_Uploaded, time_Uploaded, Upload_ID from InboundRawData Group by date_Uploaded, time_Uploaded, dispatch_Date, Upload_ID having dispatch_Date = @prod_Date) as p order by Upload_ID desc";
SqlCommand cmd1001 = new SqlCommand(HwPlts, connection);
cmd1001.Parameters.AddWithValue("@prod_Date", dateTimePicker1.Text);
HWplt.Text = cmd1001.ExecuteScalar().ToString();

If `cmd1001.ExecuteScalar()` returns null, then you're attempting to call `ToString()` on a null. That's what causes the exception. `cmd1001.ExecuteScalar()?.ToString()` means that if the first part returns null, don't call `ToString()` - just return null. But if the result isn't null, return `ToString()`. — Scott Hannen, Apr 09 '19 at 19:20

Vivian River · Accepted Answer · 2019-04-25T21:34:45.187

The problem is on this line:

HWplt.Text = cmd1001.ExecuteScalar().ToString();

Instead, use

HWplt.Text = Convert.ToString(cmd1001.ExecuteScalar());

This avoids the null-reference exception because Convert.ToString() is a static method. ToString(), by contrast, is an instance method, which depends on an implementation for the particular object you're converting to a string.

Edit: I should add that it is considered a good practice in most use-cases to use Convert.ToString() rather than Object.ToString().

How to handle ExcuteScalar exceptions on Null values?

1 Answers1