CODEWITHSUNDEEP
c#sql-servernullconvertersdbnull
Ryan Ward
Ryan Ward

Reputation: 1690

Null value from SQL datareader not evaluating as expected

Read a lot about null, DBNull and so forth and feel like I get the concepts, but clearly I'm not.

I'm querying a SQL Server database table and have a column that allows null.

I'm putting the query results in a dataset and I can clearly see that the value for row in question appears to be NULL but it just looks "blank" in the DataSet Visualizer. The column is definitely NULL in the SQL Server table for the row in question, and my select query is a direct select statement (i.e. no isnull code is used in my SQL).

I've tried to work around this seemingly simple issue but my test for nulls keeps treating the value as not being null. However my code skips to the else section and tries to convert the value in the pct cancelled column and keeps giving me an error with the Convert.ToDouble

Object cannot be cast from DBNull to other types.

I've tried playing around with the datatypes on the _cancelledPct column but nothing seems to help. I feel like I'm missing something really obvious but can't figure out why this value that I'm 99.9999% sure is null isn't evaluating as such in my System.DBNull.Value section (note I've also tried dr["pct cancelled"] == null and had similar results).

if (dr["pct cancelled"] != System.DBNull.Value)                    
    this._cancelledPct = 0; 
else
    this._cancelledPct = Convert.ToDouble(dr["pct cancelled"]);

UPDATE:

This is how my data reader and data row are setup:

DataRow dr = dimCampaignSplitRow;
SqlCommand cmd = new SqlCommand();
cmd.Connection = _etlTask.Job.CrsConnection;
cmd.CommandTimeout = 1800;
SqlDataReader theDr = null;
SqlDataAdapter da = null;

Upvotes: 0

Views: 318

Answers (3)

Ryan Ward
Ryan Ward

Reputation: 1690

Actually thanks to both marc_s and mlorbetske for putting me on the right track. In hindsight I think I just needed a good night's sleeping. Fixing my code per mlorbetske's post and then in looking into marc_s's advice, I came up with this, which works. I simply needed to do a Convert.IsDBNull on the field in question.

There's 2 hours of a Tuesday night I'll never get back lol. Thanks again.

if (Convert.IsDBNull(dr["pct cancelled"]))
    this._cancelledPct = 0;  
else
    this._cancelledPct = Convert.ToDouble(dr["pct cancelled"]);

Upvotes: 0

marc_s
marc_s

Reputation: 755013

You can ask the SqlDataReader by calling the .IsDBNull() method like this:

if (dr.IsDBNull(dr.GetOrdinal("pct cancelled"))
   this._cancelledPct = 0; 
else
   this._cancelledPct = Convert.ToDouble(dr["pct cancelled"]);

Upvotes: 0

mlorbetske
mlorbetske

Reputation: 5649

It looks like your condition is flipped.

//Changed from != to ==
if (dr["pct cancelled"] == System.DBNull.Value)                    
    //If it is null, 0
    this._cancelledPct = 0; 
else
    //If not, convert
    this._cancelledPct = Convert.ToDouble(dr["pct cancelled"]);

Upvotes: 3

Related Questions