Problems with IsDbNull()

  • Thread starter Thread starter Julian Crofton
  • Start date Start date
J

Julian Crofton

Can anyone suggest why the following does not work?
-----------------
Public Function SafeDbText(ByVal Value As Object) As String
Try
If IsDBNull(Value) Then
Return ""
Else
Return CStr(Value)
End If
Catch ex As Exception
Return ""
End Try
End Function
 
The object you are trying to cast to a string, cannot be. You can try
calling .ToString() on it, but make sure you are not holding a null
reference, as that will result in a null reference exception.
 
Thanks, Marina. I should have explained a bit more...

This works with non-null values, but fails with null values. I'm puzzled
because I would expect IsDbNull to catch the nulls - it doesn't. Not only
that, the try block doesn't catch the cast error that comes up as a result.
And finally, to add insult to injury, the Visual Studio (2003) debugger will
not step into the function at all but goes straight into error. It's not
just one thing that's failing but a whole catalogue of failure. Very
frustrating...
 
When you say null, do you mean DBNull values, or Nothing values? Those are
very different. If you are passing in null references, you need to check for
that separately with IsNothing.
 
DBNull. The purpose of this function is to take any string field from a data
row and return a guaranteed, reliable string value regardless of whether the
field contains DBNull, "" or some other value. I am trying to avoid having
to explicitly test for DBNull in each field individually using the
Is.......Null functions built into each dataset. I'm looking for something
more generic, e.g. myTextBox.Text = SafeDbText(myRow.Notes).
 
Thanks, Dustin, but it's the apparent failure of IsDbNull that gets me. It
takes an Object as its argument, but if that object has a value of DbNull
then IsDbNull doesn't return True as one would expect but instead returns
False (and then fails on the cast to String).
 
Julian Crofton said:
Can anyone suggest why the following does not work?
-----------------
Public Function SafeDbText(ByVal Value As Object) As String
Try
If IsDBNull(Value) Then
Return ""
Else
Return CStr(Value)
End If
Catch ex As Exception
Return ""
End Try
End Function
Click to expand...

Could you post a short but complete program which demonstrates the
problem?

See http://www.pobox.com/~skeet/csharp/complete.html for details of
what I mean by that.
 
Julian,

In my opinion does it works exactly as it should, can you try this?
(It is your routine however than completly running)

I hope this helps?

Cor

\\
Public Module testDBNull
Public Sub Main
Dim dt As New DataTable
dt.Columns.Add("m", GetType(System.DateTime))
dt.Rows.Add(dt.NewRow())
MessageBox.Show(SafeDbText(dt.Rows(0)(0)))
dt.Rows(0)(0) = Now
MessageBox.Show(SafeDbText(dt.Rows(0)(0)))
End Sub
Public Function SafeDbText _
(ByVal Value As Object) As String
Try
If IsDBNull(Value) Then
Return "empty"
Else
Return CStr(Value)
End If
Catch ex As Exception
Return "error"
End Try
End Function
End Module
///
 
Hi Jon (and Cor),

This problem may only exist with strongly typed datasets but here's the
demo. I've included both the dataset and demo code. You should find that the
first call to SafeDbText works fine but that you can't even step into the
second call of SafeDbText - the debugger goes straight to its error page.
(Not very helpful!) This is despite the Try block I put in specifically to
try and intercept the cast error.

Julian
------ Code -----------------------------------------
Module Module1

Public Sub DemoSafeDbText()

Dim myDs As New dsDemo
Dim nr As dsDemo.Table1Row = myDs.Table1.NewRow

nr.Field1 = "Some text"
nr.SetField2Null()

Dim SafeText As String = SafeDbText(nr.Field1)
SafeText = SafeDbText(nr.Field2)

End Sub

Public Function SafeDbText(ByVal Value) As String
Try
If IsDBNull(Value) Then
Return ""
Else
Return CStr(Value)
End If
Catch ex As Exception
Return ""
End Try
End Function

End Module
------ Dataset ---------------------------------------
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="dsDemo" targetNamespace="http://tempuri.org/dsDemo.xsd"
elementFormDefault="qualified"
attributeFormDefault="qualified" xmlns="http://tempuri.org/dsDemo.xsd"
xmlns:mstns="http://tempuri.org/dsDemo.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="dsDemo" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table1">
<xs:complexType>
<xs:sequence>
<xs:element name="Field1" type="xs:string" minOccurs="0" />
<xs:element name="Field2" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
 
Julian,

I have had something like this with the strongly typed dataset, however I
have no sample anymore from that.

You can see what happens when you set the solution explorer to show all
files (in top).
Than you can open beneath the XSD from dsDemo the vb file, that is the class
that is used.

And than search for SetField2Null in that

There you would see probably exactly what is hapening.

I hope this helps?

Cor

Julian Crofton said:
Hi Jon (and Cor),

This problem may only exist with strongly typed datasets but here's the
demo. I've included both the dataset and demo code. You should find that
the first call to SafeDbText works fine but that you can't even step into
the second call of SafeDbText - the debugger goes straight to its error
page. (Not very helpful!) This is despite the Try block I put in
specifically to try and intercept the cast error.

Julian
------ Code -----------------------------------------
Module Module1

Public Sub DemoSafeDbText()

Dim myDs As New dsDemo
Dim nr As dsDemo.Table1Row = myDs.Table1.NewRow

nr.Field1 = "Some text"
nr.SetField2Null()

Dim SafeText As String = SafeDbText(nr.Field1)
SafeText = SafeDbText(nr.Field2)

End Sub

Public Function SafeDbText(ByVal Value) As String
Try
If IsDBNull(Value) Then
Return ""
Else
Return CStr(Value)
End If
Catch ex As Exception
Return ""
End Try
End Function

End Module
------ Dataset ---------------------------------------
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="dsDemo" targetNamespace="http://tempuri.org/dsDemo.xsd"
elementFormDefault="qualified"
attributeFormDefault="qualified" xmlns="http://tempuri.org/dsDemo.xsd"
xmlns:mstns="http://tempuri.org/dsDemo.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="dsDemo" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table1">
<xs:complexType>
<xs:sequence>
<xs:element name="Field1" type="xs:string" minOccurs="0" />
<xs:element name="Field2" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Click to expand...
 
Cor,

That was a useful suggestion. Thanks. Unfortunately I couldn't find what I
was looking for, but it wasn't for want of trying. I'm going to drop this
now and stick with the null handling functions built into strongly typed
datasets. There's not that much difference in coding and at least they work.
Thanks again.

Regards,

Julian

Cor Ligthert said:
Julian,

I have had something like this with the strongly typed dataset, however I
have no sample anymore from that.

You can see what happens when you set the solution explorer to show all
files (in top).
Than you can open beneath the XSD from dsDemo the vb file, that is the
class that is used.

And than search for SetField2Null in that

There you would see probably exactly what is hapening.

I hope this helps?

Cor
Click to expand...
 
Back
Top