Database Null Errors

[MaxSmart]

I'm about ready to drop VS and go back to VBA I'm so frustrated!
Is there a way to get Visual Studio to return Null values from database tables?? 99.999% of the time I have a table of user data, there's always fields that have the option of leaving them blank. But, in VS, it always throws an error while trying to retrieve null values from a database or dataset.

Example:
<Code>
MsgBox("Birthday: " & myDataset.myTable.Rows(1).CustomerBirthday)
</Code>

This would throw an exception if the CustomerBirthday column were left blank. Obviously, I don't want a fake 'default' date in here, I want a null value!

Thanks!

Update: I found this - describes my issue perfectly - he's calling it a bug in VS, is he off his base?
http://www.mikewilson.cc/2008/03/18/allow-null-values-to-be-returned-from-strongly-typed-datasets/

 

[jmcilhinney]

I haven't read that page but I would say yes, he is off-base. First up, you are using a typed DataSet, so you don't use the Rows collection. The table itself is the collection, so your code should be:

myDataset.myTable(1).CustomerBirthday

As for the "issue" of NULL values, the typed DataSet provides functionality for that specifically. If you hadn't used the Rows collection then you would have got a typed DataRow instead of a standard DataRow and Intellisense would have shown you those members:

Dim myTypedDataRow = myTypedDataTable(rowIndex)
Dim customerBirthday As Date? = If(myTypedDataRow.IsCustomerBirthdayNull(), DirectCast(Nothing, Date?), myTypedDataRow.CustomerBirthday)

Dim myTypedDataRow = myTypedDataTable(rowIndex)

If customerBirthday.HasValue Then
    myTypedDataRow.CustomerBirthday = customerBirthday.Value
Else
    myTypedDataRow.SetCustomerBirthdayNull()
End If

It is a bit cumbersome, mainly because typed DataSets were introduced before nullable value types. This is a good reason to use something like the Entity Framework in preference to typed DataSets.

 

[jmcilhinney]

I just read some of that page and it sounds like a complete crock to me.

 

[MaxSmart]

I haven't read that page but I would say yes, he is off-base. First up, you are using a typed DataSet, so you don't use the Rows collection. The table itself is the collection, so your code should be:

myDataset.myTable(1).CustomerBirthday

As for the "issue" of NULL values, the typed DataSet provides functionality for that specifically. If you hadn't used the Rows collection then you would have got a typed DataRow instead of a standard DataRow and Intellisense would have shown you those members:

Dim myTypedDataRow = myTypedDataTable(rowIndex)
Dim customerBirthday As Date? = If(myTypedDataRow.IsCustomerBirthdayNull(), DirectCast(Nothing, Date?), myTypedDataRow.CustomerBirthday)

Dim myTypedDataRow = myTypedDataTable(rowIndex)

If customerBirthday.HasValue Then
myTypedDataRow.CustomerBirthday = customerBirthday.Value
Else
myTypedDataRow.SetCustomerBirthdayNull()
End If

It is a bit cumbersome, mainly because typed DataSets were introduced before nullable value types. This is a good reason to use something like the Entity Framework in preference to typed DataSets.

Thanks!
Can you use a For Each construct with them? Would it be just FOR EACH myRow IN MyTypedDataTable ?

 

[MaxSmart]

I just read some of that page and it sounds like a complete crock to me.

Actually now that I've been working with your answer, it sounds a lot like what the page says is the only (cumbersome) workaround. Is there no way to have a bunch of variant variables and load those with the values/nulls, apart from individually testing each and every variable? It seems like you couldn't even make a general function to handle this, because the testing property has a different name for each column (.IsBirthdayNull as opposed to .Birthday.IsNull). I guess I could make individual functions, or a class with individual properties, to handle the data pulls/writes to each variable...

 

[jmcilhinney]

It's not a cumbersome workaround. It's the way it was intended to be from the start. I'm not going to go into all the details because there's not really much point. If you don't like the way that typed DataSets work then don't use them. The Entity Framework will give you the flexibility you want and more, and you can generate an EF model using the same Data Source wizard that generates typed DataSets.

 

[ss7thirty]

I have been encountering this problem for a few years now. I always did 1 of 2 things when reading fields into strings from datasets / datareaders :

Try
'read field
Catch Ex As Exception
'set to blank
End Try

IIf(IsDbNull(fieldval), "", fieldval)

 

[jmcilhinney]

I have been encountering this problem for a few years now. I always did 1 of 2 things when reading fields into strings from datasets / datareaders :

Try
'read field
Catch Ex As Exception
'set to blank
End Try

IIf(IsDbNull(fieldval), "", fieldval)

Both of those are pointless because DBNull.ToString returns an empty String. If what you want is an empty String when the field is NULL then all you need to do is call ToString on the field, e.g.

myString = myDataReader(columnNameOrIndex).ToString()
myOtherString = myDataRow(columnNameOrIndex).ToString()

This is only an "issue" when working with typed DataSets, because accessing a property of a typed DataRow when the corresponding field is NULL throws an exception. It's not actually an issue though, because there's a mechanism specifically built into typed DataSets to handle NULL values, which I've already described. It is a bit inelegant, but it's been purpose built and it's been there from the start, so it's not a workaround or anything like that. It's the way it is. If it's too cumbersome for you then you shouldn't be using typed DataSets. There are alternatives and they have various advantages, including more elegant handling of NULL values.