Results 1 to 14 of 14

Thread: [RESOLVED] LINQ To Dataset - How to handle DBNull?

  1. #1

    Thread Starter
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Resolved [RESOLVED] LINQ To Dataset - How to handle DBNull?

    This is what I'm doing to select and display 3 columns of my datatable in GridView:
    Code:
            Dim myCustomers = From Cust In myDataset.Customers _
                              Select CustomerId = Cust.CustomerId, _
                                     CustomerName = Cust.CustomerName, _
                                     ExpiryDate = Cust.ExpiryDate
            DataGridView1.DataSource = myCustomers.ToList
    The expiry date has null values in many records whose expiry date is not set.
    On executing the query I get runtime error:

    System.Data.StrongTypingException
    "The value for column 'ExpiryDate' in table 'Customers' is DBNull."



    Obviously, my LINQ query is wrong or incomplete, but I am not able to figure the way to do it correctly.

    Pradeep
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  2. #2
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,173

    Re: LINQ To Dataset - How to handle DBNull?

    Run through the dataset before querying it. Or have your stored procedure perform ISNULL() checks and return values.

  3. #3

    Thread Starter
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: LINQ To Dataset - How to handle DBNull?

    Quote Originally Posted by mendhak
    Run through the dataset before querying it. Or have your stored procedure perform ISNULL() checks and return values.
    That was the old ADO.NET way. How should I do that using LINQ query?
    I found many examples of this on the internet but all are in C#, not a single example in VB.
    Last edited by Pradeep1210; Jan 29th, 2008 at 06:46 AM.
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  4. #4
    Hyperactive Member
    Join Date
    Jan 2008
    Location
    Merseyside
    Posts
    456

    Re: LINQ To Dataset - How to handle DBNull?

    What code are you using to populate the dataset before you query it?

  5. #5
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,173

    Re: LINQ To Dataset - How to handle DBNull?

    It wasn't "the old ADO.NET way", it was a programming practice. Using LINQ does not necessarily imply that you use it as an excuse for bad practices.

    What you can do, if you wish to do this the "new LINQ way" is to declare ExpiryDate as Nullable(Of Datetime) so that it then holds null.

  6. #6

    Thread Starter
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: LINQ To Dataset - How to handle DBNull?

    Quote Originally Posted by mendhak
    It wasn't "the old ADO.NET way", it was a programming practice. Using LINQ does not necessarily imply that you use it as an excuse for bad practices.

    What you can do, if you wish to do this the "new LINQ way" is to declare ExpiryDate as Nullable(Of Datetime) so that it then holds null.
    By "the old ADO.NET way", I just meant what I have been doing till now. But now I'm trying to learn doing it with new technology

    declare ExpiryDate as Nullable(Of Datetime)
    This is exactly what I'm looking for.
    Can you please help me with an example in VB?

    Pradeep
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  7. #7

    Thread Starter
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: LINQ To Dataset - How to handle DBNull?

    Quote Originally Posted by kevchadders
    What code are you using to populate the dataset before you query it?
    Fill Method.
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  8. #8

    Thread Starter
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: LINQ To Dataset - How to handle DBNull?

    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: LINQ To Dataset - How to handle DBNull?

    The problem is not your LINQ code at all. It's the fact that you're access the Cust.ExpiryDate property when your row contains a null value. Cust is a typed DataRow so each property is typed to the data it contains. Your ExpiryDate column contains Date values so the type of the ExpiryDate property is Date. A Property of type Date cannot return a value of type DBNull.

    Typed DataRows always provide a method for columns that can contain nulls so you can test the value first, e.g.
    vb Code:
    1. If Cust.IsExpiryDateNull() Then
    2.     'The ExpiryDate column contains null so accessing the ExpiryDate property would throw an exception.
    3. Else
    4.     'You can safely access the ExpiryDate property because you know it has a value.
    5. End If
    As far as I'm aware you cannot allow for this in LINQ using the typed properties of your DataRow. Of course, my knowledge of LINQ is far from complete, but if you use the row as though it's untyped then null values will not hurt you:
    Code:
            Dim myCustomers = From Cust In myDataset.Customers _
                              Select CustomerId = Cust.CustomerId, _
                                     CustomerName = Cust.CustomerName, _
                                     ExpiryDate = Cust("ExpiryDate")
            DataGridView1.DataSource = myCustomers.ToList

  10. #10

    Thread Starter
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: LINQ To Dataset - How to handle DBNull?

    Great Info.
    Thanks.
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [RESOLVED] LINQ To Dataset - How to handle DBNull?

    As I now know, the Fields extension method on the DataRow class can handle null values. I worked that out by reading the documentation but I think that doco contains an error because it doesn't actually use nullable types in the code example. That original code should be redone like this:
    Code:
            Dim myCustomers = From Cust In myDataset.Customers _
                              Select CustomerId = Cust.CustomerId, _
                                     CustomerName = Cust.CustomerName, _
                                     ExpiryDate = Cust.Field(Of Date?)("ExpiryDate")
            DataGridView1.DataSource = myCustomers.ToList
    Because the generic type of the Field method is nullable it can handle null values.

  12. #12

    Thread Starter
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: [RESOLVED] LINQ To Dataset - How to handle DBNull?

    Great info. Really enlightening.

    Thanks.
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  13. #13
    New Member
    Join Date
    Mar 2012
    Posts
    2

    Re: LINQ To Dataset - How to handle DBNull?

    Quote Originally Posted by Pradeep1210 View Post
    That was the old ADO.NET way. How should I do that using LINQ query?
    I found many examples of this on the internet but all are in C#, not a single example in VB.
    Hi,
    I have exactly the same issue in C#, but I don't find all the examples you are talking about, can you give me some please?
    I have a dataset containing a datatable containing columns that are set to DateTime or double, but impossible to set the nullable types "DateTime?" or "double?". And eventhough AllowDBNull is set to True, I have the same error as you when retrieving rows having null values. I see that I have access to the method IsColNameNull(), but I don't know how to use it in my LINQ query. And the thing is that I don't want to do something else when I have null values, I do want to insert null.
    Or if you could give me the syntax of your vb code in c#, that would be great.
    Thanks.

  14. #14
    New Member
    Join Date
    Mar 2012
    Posts
    2

    Re: [RESOLVED] LINQ To Dataset - How to handle DBNull?

    Quote Originally Posted by jmcilhinney View Post
    As I now know, the Fields extension method on the DataRow class can handle null values. I worked that out by reading the documentation but I think that doco contains an error because it doesn't actually use nullable types in the code example. That original code should be redone like this:
    Code:
            Dim myCustomers = From Cust In myDataset.Customers _
                              Select CustomerId = Cust.CustomerId, _
                                     CustomerName = Cust.CustomerName, _
                                     ExpiryDate = Cust.Field(Of Date?)("ExpiryDate")
            DataGridView1.DataSource = myCustomers.ToList
    Because the generic type of the Field method is nullable it can handle null values.
    I have managed to write this in c#. It solves indeed the linq problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width