-
Jan 29th, 2008, 06:05 AM
#1
[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
-
Jan 29th, 2008, 06:16 AM
#2
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.
-
Jan 29th, 2008, 06:28 AM
#3
Re: LINQ To Dataset - How to handle DBNull?
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.
-
Jan 29th, 2008, 06:31 AM
#4
Re: LINQ To Dataset - How to handle DBNull?
What code are you using to populate the dataset before you query it?
-
Jan 29th, 2008, 06:36 AM
#5
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.
-
Jan 29th, 2008, 06:53 AM
#6
Re: LINQ To Dataset - How to handle DBNull?
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
-
Jan 29th, 2008, 07:24 AM
#7
Re: LINQ To Dataset - How to handle DBNull?
Originally Posted by kevchadders
What code are you using to populate the dataset before you query it?
Fill Method.
-
Jan 30th, 2008, 02:52 AM
#8
Re: LINQ To Dataset - How to handle DBNull?
-
Jan 30th, 2008, 03:57 AM
#9
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:
If Cust.IsExpiryDateNull() Then 'The ExpiryDate column contains null so accessing the ExpiryDate property would throw an exception. Else 'You can safely access the ExpiryDate property because you know it has a value. 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
-
Feb 1st, 2008, 10:02 AM
#10
Re: LINQ To Dataset - How to handle DBNull?
-
Jul 25th, 2009, 10:14 PM
#11
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.
-
Jul 27th, 2009, 03:20 AM
#12
Re: [RESOLVED] LINQ To Dataset - How to handle DBNull?
Great info. Really enlightening.
Thanks.
-
Mar 7th, 2012, 10:15 AM
#13
New Member
Re: LINQ To Dataset - How to handle DBNull?
Originally Posted by Pradeep1210
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.
-
Mar 7th, 2012, 12:25 PM
#14
New Member
Re: [RESOLVED] LINQ To Dataset - How to handle DBNull?
Originally Posted by jmcilhinney
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|