DBNull check for ADODB.recordset

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.

Re: DBNull check for ADODB.recordset

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" wrote in message
news:81D5FC48-F0EE-45DC-9AA7-14CA25340C60@microsoft.com...
> 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.
>
>

Re: DBNull check for ADODB.recordset

"Mike OKC" wrote in message
news:81D5FC48-F0EE-45DC-9AA7-14CA25340C60@microsoft.com...
> 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.

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