Help solving error: "Conversion from type 'DBNull' to type 'String' is not valid."

cdemott33
cdemott33 used Ask the Experts™
on
Hi Folks - I am getting an error in my procedure below and I'm hoping you can help me fix the problem.  

I have BLL Function that returns a datatable.  Some of the rows returned from my datatable may not have a value, so it returns DBNull.  How can I avoid the "DNull to type 'String' error when there is no value returned?

Please see my code below.
Dim customerName As String
        Dim customerAddrOne As String
        Dim customerAddrTwo As String
        Dim customerAddrThree As String

        Dim dt As DataTable = clientLogic.GetClientInfo(ClientID)

        If dt.Rows.Count > 0 Then
            customerName = dt.Rows(0).Item("SOLDTO")
            customerAddrOne = dt.Rows(0).Item("SOLD1")
            customerAddrTwo = dt.Rows(0).Item("SOLD2")
            customerAddrThree = dt.Rows(0).Item("SOLD3")
        End If

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
customerName = dt.Rows(0).Item("SOLDTO").toString()
customerAddrOne = dt.Rows(0).Item("SOLD1").toString()
customerAddrTwo = dt.Rows(0).Item("SOLD2").toString()
customerAddrThree = dt.Rows(0).Item("SOLD3").toString()
Paul JacksonSoftware Engineer
Top Expert 2011

Commented:
Are you using a stored procedure to return the results you would be best ensuring that no nulls are returned by the stored procedure :

Select ISNULL(mycol, '') as myCol
From mytable

Otherwise check whether it is null before assigning to string:

If dt.Rows.Count > 0 Then 
     If Not IsDBNull(dt.Rows(0).Item("SOLDTO")) Then
            customerName = dt.Rows(0).Item("SOLDTO")
     End If 
     If Not IsDBNull(dt.Rows(0).Item("SOLD1")) Then
            customerAddrOne = dt.Rows(0).Item("SOLD1") 
      End If
      If Not IsDBNull(dt.Rows(0).Item("SOLD2")) Then
            customerAddrTwo = dt.Rows(0).Item("SOLD2") 
      End If
      If Not IsDBNull(dt.Rows(0).Item("SOLD3")) Then
            customerAddrThree = dt.Rows(0).Item("SOLD3") 
      End If
End If

Open in new window

Commented:
you can also do:

IsDBNull(dt.Rows(0).Item("SOLDTO")
IsDBNull(dt.Rows(0).Item("SOLD1"))
IsDBNull(dt.Rows(0).Item("SOLD2"))
IsDBNull(dt.Rows(0).Item("SOLD3"))

Commented:
IsDBNull(dt.Rows(0).Item("SOLDTO")) = False ? dt.Rows(0).Item("SOLDTO") : ""
IsDBNull(dt.Rows(0).Item("SOLD1")) = False ? dt.Rows(0).Item("SOLD1") : ""

...

...

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial