How to avoid error when displaying empty MS Access fields in VB?

[collin_ola]

Hi,

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.

Collin

[thines01]

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.

[Reverend Jim]

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.

[M.Waqas Aslam]

hello !
please check this link may be it will solve your prob :) http://p2p.wrox.com/sql-language/12823-convert-null-string-concatenation.html

[collin_ola]

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...

[Reverend Jim]

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

COALESCE(a,b,c)

if a is not null then
    return a
else if b is not null then
    return b
else
    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

NZ(middleName,'none')

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

[collin_ola]

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