DBNull check for ADODB.recordset

  • Thread starter Thread starter Mike OKC
  • Start date Start date
M

Mike OKC

VB 2005

All developers face this issue; so I'm sure Microsoft was a solution for it.

What is the built in method to check DBNull in a recordset field? NOT a
typed dataset.
I don't want to modify my sql script because it is created dynamically.
Below I have the old method to check for DBNull and to deal with DBNull in
the recordset. This method of coding is used over 80 times as I have over 80
fields to check for DBNull, so a one line method call off the value recordset
would be great.

If IsDBNull(rstSQLdetail.Fields("CaseNumber").Value) Then
txtCaseNumber.Text = ""
Else
txtCaseNumber.Text = rstSQLdetail.Fields("CaseNumber").Value
End If


It would be great to have something LIKE this,

txtCaseNumber.Text = rstSQLdetail.Fields("CaseNumber").Value.ChkDBNull("")

In this example the method would return a zero length string.
 
Simply write yourself a function like:

Function ChkDBNull(ByVal value As Object) As String

If IsDbNull(value) Then Return String.Empty

Return value.ToString()

End Function

and then your 'check' becomes something like:

txtCaseNumber.Text = ChkDBNull(rstSQLdetail.Fields("CaseNumber").Value)

but .... DbNull.Value.ToString() returns an empty string, so, instead, you
can simply use:

txtCaseNumber.Text = rstSQLdetail.Fields("CaseNumber").Value.ToString()

The big gotcha of course is when you start dealing with column types that
don't represent a string per se, like datetime, bit, int, image, etc. You're
goning to have to figure out how to deal with the law of unintended
consequences.
 
Mike OKC said:
VB 2005

All developers face this issue; so I'm sure Microsoft was a solution for
it.

What is the built in method to check DBNull in a recordset field? NOT a
typed dataset.
I don't want to modify my sql script because it is created dynamically.
Below I have the old method to check for DBNull and to deal with DBNull in
the recordset. This method of coding is used over 80 times as I have over
80
fields to check for DBNull, so a one line method call off the value
recordset
would be great.

If IsDBNull(rstSQLdetail.Fields("CaseNumber").Value) Then
txtCaseNumber.Text = ""
Else
txtCaseNumber.Text =
rstSQLdetail.Fields("CaseNumber").Value
End If


It would be great to have something LIKE this,

txtCaseNumber.Text = rstSQLdetail.Fields("CaseNumber").Value.ChkDBNull("")

In this example the method would return a zero length string.
Click to expand...

I just wrote an article about this:
http://leon.mvps.org/DotNet/CheckDbNull.html
 
Back
Top