none
Assigning NULL to int in a SQLDataReader RRS feed

  • Question

  • Inherited an older WCF app and am trying to bring it forward.  One of the problems is that NULL [integer] values in the database are being presented to the user as 0.  Given this class:

       [DataContract]
        public class ADAMember
        {
            [DataMember]
            public string ADAText { get; set; }
            [DataMember]
            public int ADAToilets { get; set; }
            [DataMember]
            public int ADAElevators { get; set; }
            [DataMember]
            public bool ADAOtherInd { get; set; }
            [DataMember]
            public int? ADAOther { get; set; }
        }
    is being populated by this code:
            List<ADAMember> IFAPService.ADARead(string FiceCode, string BuildingNumber)
            {
                List<ADAMember> result;
    
                using (var conn = new SqlConnection(Properties.Settings.Default.ConnectionString))
                {
                    using (var cmd = new SqlCommand("ADARead", conn) { CommandType = CommandType.StoredProcedure })
                    {
                        cmd.Connection.Open();
                        cmd.Parameters.Add("@fice_code", SqlDbType.Char, 6).Value = FiceCode;
                        cmd.Parameters.Add("@building_number", SqlDbType.Char, 3).Value = BuildingNumber;
                        using (var rdr = cmd.ExecuteReader())
                        {
                            if (rdr.HasRows)
                            { 
                                List<ADAMember> ADAMembers = new List<ADAMember>();
                                while (rdr.Read())
                                {
                                	ADAMember ADAMember = new ADAMember
                                    { 
                                        ADAText = !rdr.IsDBNull(0) ? rdr.GetString(0) : null,
                                        ADAToilets = !rdr.IsDBNull(1) ? rdr.GetInt16(1) : 0,
                                        ADAElevators = !rdr.IsDBNull(2) ? rdr.GetInt16(2) : 0,
                                        ADAOtherInd = !rdr.IsDBNull(3) ? rdr.GetBoolean(3) : false,
                                        ADAOther = !rdr.IsDBNull(4) ? rdr.GetInt32(4) : 0
                                    };
                                    ADAMembers.Add(ADAMember);
                                }
                                result = ADAMembers;
                            }
                            else
                                result = null;
                        }
                    }
                }
                return result;
            }
    When NULL is returned in the Toilets, Elevators & Other column the user sees 0 and continues on instead of seeing NULL and knowing that he needs to enter some data.  I have tried redefining the members of the class like this:
            [DataMember]
            public int? ADAToilets { get; set; }
    And even
            [DataMember]
            public <Nullable>int ADAToilets { get; set; }
    and then changing the reader to:
    ADAToilets = !rdr.IsDBNull(1) ? rdr.GetInt16(1) : null,

    but that doesn't work.  Is there any way to assign NULL to an int or decimal variable?  Thanks.

    Thursday, March 6, 2014 5:49 PM

Answers

  • Hi,

    >>Type of conditional expression cannot be determined because there is no implicit conversion between 'short' and '<null>'
    I see that you are using ADAToilets = !rdr.IsDBNull(1) ? rdr.GetInt16(1) : null, which is the b?x:y expression.

    For the conditional expression b ? x : y, it will meet the following:

    If only one of x and y has a type, and both x and y are implicitly convertible to that type, then that is the type of the conditional expression.

    The issue here is that in

    ADAToilets = !rdr.IsDBNull(1) ? rdr.GetInt16(1) : null

    Only one of the conditional results has a type. Here x is an short literal, and y is null which does not have a type and null is not implicitly convertible to an short. Therefore, "certain good conditions" aren't met, and a compile-time error occurs.

    There are two ways around this:

    ADAToilets = !rdr.IsDBNull(1) ? (Int16?)rdr.GetInt16(1) : null

    The other way is obviously:

    ADAToilets = !rdr.IsDBNull(1) ? rdr.GetInt16(1) : (Int16?)null
    Best Regards,
    Amy Peng

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, March 13, 2014 6:35 AM
    Moderator

All replies

  • Hello,

    There is a WCF forum. Personally a WCF service generally is not responsible for formatting data but instead the caller...let's say I was issues a GET in my WCF RESTFUL Service, I would get data from a database and return it in a JSON or XML container then the requester would format the data.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, March 6, 2014 6:28 PM
  • Didn't realize this was in the VB section, thought I was in C#.

    I'm not wanting the WCF to format the data, I'm wanting it to unformat it.  Right now it's being formatted as 0 instead of passing through the NULL.

    I'll see if I can move this post over to WCF.

    /*************************************************/

    Don't see a way to move it.

    • Edited by PaulNations Thursday, March 6, 2014 7:02 PM
    Thursday, March 6, 2014 6:51 PM
  • Didn't realize this was in the VB section, thought I was in C#.

    I'm not wanting the WCF to format the data, I'm wanting it to unformat it.  Right now it's being formatted as 0 instead of passing through the NULL.

    I'll see if I can move this post over to WCF.

    /*************************************************/

    Don't see a way to move it.


    You can write a post in the WCF forum, link back to here. My guess is a forum moderator will then do the move for you. I would if I could but do not have that kind of authority.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, March 6, 2014 8:29 PM
  • "but that doesn't work"

    Can you give a little more detail?  Compile error, runtime exception, incorrect result?  Details please.

    What was the code that you were using when it didn't "work"?  What was stored in ADAToilets that makes you say "doesn't work".


    Paul Linton

    Friday, March 7, 2014 12:29 AM
  • When I run with:

    public int? ADAToilets { get; set; }

    and 

    ADAToilets = !rdr.IsDBNull(1) ? rdr.GetInt16(1) : null,

    I get this error before ever seeing the WCF Test Client:

    Error 1 Type of conditional expression cannot be determined because there is no implicit conversion between 'short' and '<null>' \\workspace\shared\PDEV\pdev\FAP\WebFAP\FAPWCF\FAPWCF\FAPService.cs 38 69 FAPWCF


    Friday, March 7, 2014 5:13 PM
  • Hi,

    >>Type of conditional expression cannot be determined because there is no implicit conversion between 'short' and '<null>'
    I see that you are using ADAToilets = !rdr.IsDBNull(1) ? rdr.GetInt16(1) : null, which is the b?x:y expression.

    For the conditional expression b ? x : y, it will meet the following:

    If only one of x and y has a type, and both x and y are implicitly convertible to that type, then that is the type of the conditional expression.

    The issue here is that in

    ADAToilets = !rdr.IsDBNull(1) ? rdr.GetInt16(1) : null

    Only one of the conditional results has a type. Here x is an short literal, and y is null which does not have a type and null is not implicitly convertible to an short. Therefore, "certain good conditions" aren't met, and a compile-time error occurs.

    There are two ways around this:

    ADAToilets = !rdr.IsDBNull(1) ? (Int16?)rdr.GetInt16(1) : null

    The other way is obviously:

    ADAToilets = !rdr.IsDBNull(1) ? rdr.GetInt16(1) : (Int16?)null
    Best Regards,
    Amy Peng

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, March 13, 2014 6:35 AM
    Moderator