I load a datatable with records that have DBNull in some fields (setting default value = "" doesn't stop this from happening on new record).
So I tediously check fields as follows:
Try
Dim lokta
AsNew LicDataSetTableAdapters.LockerTableAdapter Dim lokdt
AsNew LicDataSet.LockerDataTable Dim lokdr
As LicDataSet.LockerRow
lokdt = lokta.GetOnePz(0) 'gets a record
lokdr = lokdt.Rows(0)
With lokdr If IsDBNull(.pzkey)
Then .pzkey = "" End
If Catch ex As Exception
End Try
What happens is that I get an exception telling me that the field I'm testing for DBNull is DBNull.
How can I test a field for DBNull and change it to "" without throwing an exception?
I tried both Convert.IsDBNull and DBNull.Value.Equals methods with same result --
StrongTypingException was caught.
The value for column 'pzkey' in table 'Locker' is DBNull.
InnerException: Make sure the source type is convertible to the destination type.
I closed and reloaded the IDE in case of corruption but still get this exception.
In Access the table 'Locker' has field pzkey defined as Text, Field Size 50, Default Value "", Required-No, Allow Zero Length-Yes, Indexed(Duplicates OK)
Aha -- I see that I can change the NullValue property of columns in the datatable from (Throw exception) to (Empty). This seems to convert a null valued field to "", and seems to survive a reconfigure of the table adapter, so with this setting I don't need
to check for Null at all.
Thanks for prodding me to look in the right direction.
uick383937
Member
81 Points
117 Posts
Testing for DBNull Throws Exception
Jul 01, 2012 12:33 AM|LINK
I'm using VBnet with Access Database
I load a datatable with records that have DBNull in some fields (setting default value = "" doesn't stop this from happening on new record).
So I tediously check fields as follows:
Try
Dim lokta As New LicDataSetTableAdapters.LockerTableAdapter
Dim lokdt As New LicDataSet.LockerDataTable
Dim lokdr As LicDataSet.LockerRow
lokdt = lokta.GetOnePz(0) 'gets a record
lokdr = lokdt.Rows(0)
With lokdr
If IsDBNull(.pzkey) Then
.pzkey = ""
End If
Catch ex As Exception
End Try
What happens is that I get an exception telling me that the field I'm testing for DBNull is DBNull.
How can I test a field for DBNull and change it to "" without throwing an exception?
Advice appreciated.
Careed
All-Star
18764 Points
3637 Posts
Re: Testing for DBNull Throws Exception
Jul 01, 2012 01:21 AM|LINK
Verify that lokdr is not null/Nothing.
"The oxen are slow, but the earth is patient."
uick383937
Member
81 Points
117 Posts
Re: Testing for DBNull Throws Exception
Jul 01, 2012 01:51 AM|LINK
I did check that --
lokr has other fields that are showing expected values
I actually manually changed the fields from DBNull to "" in the database, and the error condition did not show up.
Thanks for the suggestion.
Careed
All-Star
18764 Points
3637 Posts
Re: Testing for DBNull Throws Exception
Jul 01, 2012 02:55 AM|LINK
In place of the IsDBNull VB function, try using either the Convert.IsDBNull or DBNull.Value.Equals method.
"The oxen are slow, but the earth is patient."
uick383937
Member
81 Points
117 Posts
Re: Testing for DBNull Throws Exception
Jul 01, 2012 04:15 PM|LINK
I tried both Convert.IsDBNull and DBNull.Value.Equals methods with same result --
StrongTypingException was caught.
The value for column 'pzkey' in table 'Locker' is DBNull.
InnerException: Make sure the source type is convertible to the destination type.
I closed and reloaded the IDE in case of corruption but still get this exception.
Careed
All-Star
18764 Points
3637 Posts
Re: Testing for DBNull Throws Exception
Jul 01, 2012 08:41 PM|LINK
What is the defined data type for the pzkey property?
What is the data type of the underlying column from the database table?
"The oxen are slow, but the earth is patient."
uick383937
Member
81 Points
117 Posts
Re: Testing for DBNull Throws Exception
Jul 01, 2012 09:40 PM|LINK
In Access the table 'Locker' has field pzkey defined as Text, Field Size 50, Default Value "", Required-No, Allow Zero Length-Yes, Indexed(Duplicates OK)
Aha -- I see that I can change the NullValue property of columns in the datatable from (Throw exception) to (Empty). This seems to convert a null valued field to "", and seems to survive a reconfigure of the table adapter, so with this setting I don't need to check for Null at all.
Thanks for prodding me to look in the right direction.