Checking for Nulls in Fields

9 Mar 2006 4:25 PM

Bernie Hunt

Is there a cleaner way to check for nulls in fields? Faster, shorter in
code, or just better way?

If Not IsDBNull(drPatients("Address")) Then tmpStrAddress = CStr(drPatients
("Address"))

Thanks,
Bernie

9 Mar 2006 4:39 PM

Marina Levit [MVP]

I think the datareader has its own IsDbNull method, which you could use as
well.

There are probably other equivalent ways of checking that are about as long.
Is this really a lot of code anyway?

If you wanted it to be shorter, you could write helper functions. Something
like (not tested)

Public Sub AssignValue(ByVal val As Object, ByRef target as String)
If Not IsDBNull(val) Then target = cstr(val)
End Sub

Then you would just call:

AssignValue(drPatients("Address"), tmpStrAddress)

Show quote

"Bernie Hunt" <bh***@optonline.net> wrote in message
news:Xns9781742B379F8bhuntoptonlinenet@207.46.248.16...
> Is there a cleaner way to check for nulls in fields? Faster, shorter in
> code, or just better way?
>
> If Not IsDBNull(drPatients("Address")) Then tmpStrAddress =
> CStr(drPatients
> ("Address"))
>
> Thanks,
> Bernie

9 Mar 2006 5:12 PM

Bernie Hunt

> I think the datareader has its own IsDbNull method, which you could
> use as well.

I came up short on the syntax to get the build in one to work. I couldn't
fine an example of how to call out a field and the IsDBNull method at the
same time.

> There are probably other equivalent ways of checking that are about as
> long. Is this really a lot of code anyway?

Yes when there are a couple of hundred fields that are being mapped to
new files, hahahaha.

> If you wanted it to be shorter, you could write helper functions.
> Something like (not tested)

For anyone reading this in the future I came up with this;
    Function CheckForNulls(ByVal IncomingString As Object) As String
        If Not IsDBNull(IncomingString) Then
            Return CStr(IncomingString)
        Else
            Return ""
        End If
    End Function

Which adds the advantage of setting the value to an empty string ("") if
it was null. That way you can use in line in assignment statements.

As always,
Marina, Thanks for all your help!!!!

Bernie

9 Mar 2006 5:30 PM

Cor Ligthert [MVP]

Bernie,

I assume that your are looking for the AndAlso that is the shortcutted
command for VB.

The last means that

If A IsNot Nothing AndAlso A Not = ""

The second evalutation will not be processed if the first is not true and
therefore you don't get those errors.

I hope that this was something you was looking for.

Cor

Show quote

"Bernie Hunt" <bh***@optonline.net> schreef in bericht
news:Xns97817C240532Fbhuntoptonlinenet@207.46.248.16...
>> I think the datareader has its own IsDbNull method, which you could
>> use as well.
>
> I came up short on the syntax to get the build in one to work. I couldn't
> fine an example of how to call out a field and the IsDBNull method at the
> same time.
>
>> There are probably other equivalent ways of checking that are about as
>> long. Is this really a lot of code anyway?
>
> Yes when there are a couple of hundred fields that are being mapped to
> new files, hahahaha.
>
>> If you wanted it to be shorter, you could write helper functions.
>> Something like (not tested)
>
> For anyone reading this in the future I came up with this;
>    Function CheckForNulls(ByVal IncomingString As Object) As String
>        If Not IsDBNull(IncomingString) Then
>            Return CStr(IncomingString)
>        Else
>            Return ""
>        End If
>    End Function
>
> Which adds the advantage of setting the value to an empty string ("") if
> it was null. That way you can use in line in assignment statements.
>
> As always,
> Marina, Thanks for all your help!!!!
>
> Bernie
>

9 Mar 2006 5:32 PM

Cor Ligthert [MVP]

The sample is not related to anything and can really shorter. It is just to
show the AndAlso.

Cor