I have a database with lots of fields, some of which are still empty and are to be filled in at a later stage. Is there any way of writing an IF statement (or any other method) that will allow these empty records to be 'shown' in my textboxes in Visual Studio, without receiving the error:

Conversion from type 'DBNull' to type 'String' is not valid.


Thanks in advance.


5 Years
Discussion Span
Last Post by collin_ola

You could possibly make a function that converts DBNull to an empty string like:

Function SafeValue(ByVal objOrig As Object) As String
      If (IsDBNull(objOrig)) Then
         Return ""
      End If

      Return objOrig.ToString()
   End Function

... then wrap all of your potential DBNull returned strings in that.

Edited by thines01: typo


SQL server supports the COALESCE function. I seem to recall that Access has something similar

Nz(Value, [ValueIfNull])

I don't have Access installed so I can't verify this.

Edited by Reverend Jim: n/a


Hey guys,

Sorry for the very late reply.

I'm sure that the COALESCE function would be a way of solving the issue, but I'm not too sure how to use it, and it isn't described to clearly in the link provided by waqasaslammmeo...


Coalesce takes an arbitrary number of parameters. It returns the value of the first parameter that does not evaluate as NULL. If all parameters are NULL then it returns NULL. For example


if a is not null then
    return a
else if b is not null then
    return b
    return c
end if

I think NZ is similar but takes only two arguments. The second argument is the value you want returned if the first argument is NULL. For example


Retuens the middleName field. If there is no middle name value then it returns "none".

Edited by Reverend Jim: n/a


Oh right..thank you :). I'll try it out tomorrow, it's nearly midnight here :P

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.