2

I am trying to populate a combobox with a list my query returns. When I execute my program it gives me a specified cast is not valid error ( I have it execute on page load event). Every field in the database I have to work with can be null except the primary key. So I tried using DBNull.Value but it can't get my (int)reader fields to work. I have supplied my code below for a better understanding. How can I get my (int)reader's to work with my statements, so they can read when there is a null value?

CustData cd = new CustData();
cd.CustomerID = (int)reader["CustomerID"];
cd.Name = reader["Name"] != DBNull.Value ? reader["Name"].ToString() : string.Empty;
cd.ShippingAddress = reader["ShippingAddress"] != DBNull.Value ? reader["ShippingAddress"].ToString() : string.Empty;
cd.ShippingCity = reader["ShippingCity"] != DBNull.Value ? reader["ShippingCity"].ToString() : string.Empty;
cd.ShippingState = reader["ShippingState"] != DBNull.Value ? reader["ShippingState"].ToString() : string.Empty;
cd.ShippingZip = (int)reader["ShippingZip"];
cd.BillingAddress = reader["BillingAddress"] != DBNull.Value ? reader["BillingAddress"].ToString() : string.Empty;
cd.BillingCity = reader["BillingCity"] != DBNull.Value ? reader["BillingCity"].ToString() : string.Empty;
cd.BillingState = reader["BillingState"] != DBNull.Value ? reader["BillingState"].ToString() : string.Empty;
cd.BillingZip = (int)reader["BillingZip"];
cd.Territory = reader["Territory"] != DBNull.Value ? reader["Territory"].ToString() : string.Empty;
cd.Category = reader["Category"] != DBNull.Value ? reader["Category"].ToString() : string.Emptyy
Kpt.Khaos
  • 673
  • 3
  • 14
  • 37

3 Answers3

5

That is because int is not nullable. You need to use int? or nullable<int> (long hand) to allow it to be an int OR a null value.

You can then use the usual .HasValue and .Value etc to get the value from the item.

EDIT: To enhance the visibility of my comment to this answer. I would advise against checking for NULL and storing Zero into your property because then when you save back you are changing a Null to a Zero even though nothing has been changed by the system. Now, reports etc may distinguish between NULL and Zero (very often) and could start doing strange things!

Null does NOT equal zero!! If you assume it does as a work around... What happens if I truly do want to record zero? How do you differentiate between a real zero and a "was null now zero" trick? Do it right, save yourself the pain!

Belogix
  • 8,129
  • 1
  • 27
  • 32
  • 1
    I tried (int?)reader and it says it can cast it from int to int? – Kpt.Khaos Jan 10 '14 at 14:08
  • No, `int?` is NOT an `int` because it can contain nulls. You need to decide if you want your variable in code to contain null like the database or zero if nothing was specified. However, be cautious with storing zero (or some other value) because then when you write back to the database it will no longer be storing null but zero!! So, best thing to do is change your property to be `int?` rather than `int` and that way it matches the backing database. To summarize: Null does NOT equal Zero! – Belogix Jan 10 '14 at 14:10
  • @Kpt.Khaos - Cool. I have updated my answer to strongly advise changing your property to `int?` for many reasons! Just re-read for a sample where changing to zero can cause you some pain! – Belogix Jan 10 '14 at 14:17
  • Good stuff, glad I could help! – Belogix Jan 10 '14 at 14:22
2

Use nullable int, or just make your control for your int's too

reader["ShippingZip"] != DBNull.Value ? (int)reader["ShippingZip"] : default(int);
Selman Genç
  • 100,147
  • 13
  • 119
  • 184
  • Danger with that is it will store zero into the variable... Now the user presses "Save" for arguments sake without changing anything and now the database is recording zero. What does that mean in reports or the system in general? Null does not equal zero. – Belogix Jan 10 '14 at 14:12
  • @Belogix, technically you right.But how would 0 affect his records dangerously ? If the value is null that means there is no record in this column.so 0 means nothing here.0 or null it doesn't matter – Selman Genç Jan 10 '14 at 14:17
  • Not really ... They are HUGELY different things! A simple example could be an optional box which, if filled, applies some logic somewhere (I have seen this MANY times on reports etc). So, if you have NULL then no processing is done, if it is Zero then it IS a value and therefore additional logic is run. So, by changing it to Zero you have now forced something to change even though the system has not explicitly set it!! Now, say I DO want to record zero because it means something in my world... Whoops! Now, which is null and which is zero?! Now you are in a world of hurt! – Belogix Jan 10 '14 at 14:20
  • Carrying on... How do I know that? Because I have worked on systems JUST like that where reports and additional logic work on multiple fields and if field A is null then it will skip fields B,C and D etc. So, by putting a zero in field A then B,C,D will do some processing and that is not what was intended!! As I say, I have seen and been hurt by this kind of "Null == Zero == All the same" but it really isn't! – Belogix Jan 10 '14 at 14:22
1

You should use a nullable int for your variable and cast it, like (int?). Int can only have a value; nullable types can also be null. When you use a nullable type, you can look at the property .HasValue. Here is the MSDN page: http://msdn.microsoft.com/en-us/library/2cf62fcy.aspx

Pete
  • 180
  • 1
  • 2
  • 16