I have an DBNull Exception in my If, why?

Question

I make a program by displaying values from a database to the user. When I query whether a field is NULL, there is an error.

My attempts:

Dim dtContacts As New DCFLEXNorthwind.ContactsDataTable
taContacts.SelectAllByID(dtContacts, ID)
Dim r As DCFLEXNorthwind.ContactsRow = dtContacts.Rows(0)

If r.Extension Is DBNull.Value Then
    txtExtension.Text = ""
Else
    txtExtension.Text = r.Extension
End If

If r.Extension Is DBNull Then
    txtExtension.Text = ""
Else
    txtExtension.Text = r.Extension
End If

If r.Extension = DBNull.Value Then
    txtExtension.Text = ""
Else
    txtExtension.Text = r.Extension
End If

If r.Extension = DBNull Then
    txtExtension.Text = ""
Else
    txtExtension.Text = r.Extension
End If

If IsDBNull(r.Extension) Then
    txtExtension.Text = ""
Else
    txtExtension.Text = r.Extension
End If

Then the following error occurs with the valid queries:

The value for column extension in table Contacts is DBNull.

and

Invalid conversion from type DBNull to type String.

How is r.Extension defined?

Joel Coehoorn
– Joel Coehoorn

2019-10-08 13:17:13 +00:00
Commented Oct 8, 2019 at 13:17 — Joel Coehoorn
– Joel Coehoorn, Commented Oct 8, 2019 at 13:17

Joel Coehoorn · Accepted Answer · 2019-10-08 13:29:28Z

2

How is r.Extension defined?

r.Extension = DBNull.Value might be correct, but only if it directly reads from an ADO.Net data transfer object field, like a DataReader, DataTable, or DataSet. The fields in those types use the base Object type, which can hold a string, datetime, numeric, etc value... anything you might get back from the database, including DBNull.

Your r variable and it's Extension property look more like the data transfer object was already used to populate a real type with real properties, where the Extension property is explicitly declared as a string. At this point, it's already too late to check for the database null, because you would not be able to assign DBNull to that property in the first place.

edited Oct 8, 2019 at 13:29

answered Oct 8, 2019 at 13:20

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

a.b_om Over a year ago

And How can I do it?

Joel Coehoorn Over a year ago

With the edit, this looks like you're using typed DataSets. Almost no one does that. But in this case, the typed dataset maps the column do a string field. I'm not sure how they handle null, but you may need to check the field in the underlying System.Data.DataRow

Joel Coehoorn Over a year ago

Hmm... this might also be some third-party middleware, in which case you should check if the library is using nullable types, which case you might be able to say r.Extension Is Nothing or String.IsNullOrEmpty(r.Extension) as in regular code. This is because string is already a reference type. For value types, you would check r.Property.HasValue

Joel Coehoorn Over a year ago

I often wish Nullable types had been available when ADO.Net was first written. It would have simplified a LOT of things.

a.b_om Over a year ago

@laancelot No that doesn't work. Why no one use the typed DataSets? What use the others?

|

Ess Kay · Accepted Answer · 2019-10-08 13:33:29Z

1

You should always check if the value field even exists by comparing to nothing

Either use IsNothing(r.Extension) or r.Extension Is Nothing

Check for nothing first, otherwise it will error out if value does not exist.

Here is code to try

Dim dtContacts As New DCFLEXNorthwind.ContactsDataTable
taContacts.SelectAllByID(dtContacts, ID)
Dim r As DCFLEXNorthwind.ContactsRow = dtContacts.Rows(0)

If IsNothing(r.Extension) OR r.Extension Is DBNull.Value Then
    txtExtension.Text = ""
Else
    txtExtension.Text = r.Extension
End If

If IsNothing(r.Extension) OR r.Extension Is DBNull Then
    txtExtension.Text = ""
Else
    txtExtension.Text = r.Extension
End If

If IsNothing(r.Extension) OR r.Extension = DBNull.Value Then
    txtExtension.Text = ""
Else
    txtExtension.Text = r.Extension
End If

If IsNothing(r.Extension) OR r.Extension = DBNull Then
    txtExtension.Text = ""
Else
    txtExtension.Text = r.Extension
End If

If IsNothing(r.Extension) OR IsDBNull(r.Extension) Then
    txtExtension.Text = ""
Else
    txtExtension.Text = r.Extension
End If

answered Oct 8, 2019 at 13:33

Ess Kay

6085 silver badges20 bronze badges

3 Comments

Craig Over a year ago

As far as I know, IsNothing is a legacy-support function originally (in VBA) intended for checking on the value contained in a Variant, and I would not recommend it for new code.

Ess Kay Over a year ago

I am aware but it is quicker to write for now

a.b_om Over a year ago

Yes that is it.

Collectives™ on Stack Overflow

I have an DBNull Exception in my If, why?

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related