Null value from SQL datareader not evaluating as expected

Question

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;

mlorbetske · Accepted Answer · 2015-03-11 00:27:13Z

3

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"]);

answered Mar 11, 2015 at 0:27

mlorbetske

5,6592 gold badges32 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan Ward Over a year ago

Thanks mlorbetske, I think you're right. However after fixing that I still get the same "Object cannot be cast..." error.

marc_s · Accepted Answer · 2015-03-11 05:42:42Z

0

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"]);

answered Mar 11, 2015 at 5:42

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

3 Comments

Ryan Ward Over a year ago

marc_s, I'm usoing C# in VS2008 and isDBNull doesn't seem to exist (I do get IsNull as an option) and GetOrdinal doesn't show as valid either. Are you using C# s well, or could it be that my data reader is setup differently from the way I should.

marc_s Over a year ago

@RyanWard: ah - sorry - you have dr but it's not the SqlDataReader (as I suspected from the naming) but the DataRow - the .IsDBNull() method exists on the SqlDataReader - so theDr.IsDBNull(....) should work

Ryan Ward Over a year ago

marc_s, just figured out how misleading my code was (in 99% of my other code dr is for the datareader so I really even confused myself). Posted an answer above, thanks for pointing me in the right direction

Ryan Ward · Accepted Answer · 2015-03-11 14:29:18Z

0

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"]);

answered Mar 11, 2015 at 14:29

Ryan Ward

1,7204 gold badges18 silver badges24 bronze badges

Collectives™ on Stack Overflow

Null value from SQL datareader not evaluating as expected

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related