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" <MikeOKC [at] newsgroups.nospam> wrote in message
news:81D5FC48-F0EE-45DC-9AA7-14CA25340C60 [at] 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.
>
>

"Mike OKC" <MikeOKC [at] newsgroups.nospam> wrote in message
news:81D5FC48-F0EE-45DC-9AA7-14CA25340C60 [at] 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