Best way to handle NULLs

Posted on 2004-11-23
Last Modified: 2010-04-23
What's the best way to deal with NULL values in If one of the values I'm receiving from the database is NULL I get "ErrorSystem.InvalidCastException: Cast from type 'DBNULL' to type 'String' is not valid'

   Private Function LoadOrgansation() As Boolean()

            'Check the status of the connection

            Dim cmd As SqlCommand = cnn.CreateCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "procLoadOrganisation"

            'Input Parameter
            cmd.Parameters.Add(New SqlParameter("@OrganisationIDAuto", SqlDbType.Int))
            cmd.Parameters("@OrganisationIDAuto").Value = CInt(txt_ServiceProvider.Tag)

            Dim reader As SqlDataReader = cmd.ExecuteReader
            If reader.Read Then
                With reader
                    txt_OrganisationName.Text = CType(.GetValue(.GetOrdinal("ProviderName")), String)
                    txt_PostalAddress.Text = CType(.GetValue(.GetOrdinal("Address")), String)
                    txt_PostalCity.Text = CType(.GetValue(.GetOrdinal("City")), String)
                    txt_Phone.Text = CType(.GetValue(.GetOrdinal("Phone")), String)
                    txt_Email.Text = CType(.GetValue(.GetOrdinal("EMail")), String)
                    txt_ContactPerson.Text = CType(.GetValue(.GetOrdinal("ContactPerson")), String)
                    txt_OrganisationNotes.Text = CType(.GetValue(.GetOrdinal("Description")), String)
                End With
            End If

        Catch ex As Exception
            MessageBox.Show("ERROR" & ex.ToString)
        End Try
    End Function
Question by:PeterErhard
    LVL 1

    Accepted Solution

    A function that you will be able to use all the time.

    Heres what your code would look like...
    Instead of Ctype...use nz

                   With reader
                        txt_OrganisationName.Text = nz(.GetValue(.GetOrdinal("ProviderName")))
                        txt_PostalAddress.Text = nz(.GetValue(.GetOrdinal("Address")))
                    End With

        Public Function nz(ByVal obj As Object, Optional ByVal strReturnIfNull As String = "") As String
            If obj Is DBNull.Value Then
                Return strReturnIfNull
                Return CStr(obj)
            End If
        End Function
    LVL 28

    Expert Comment

    check value for null before writing to textbox

    if value is System.DBNUll.Value then
      txt.text = ""
      txt= CType(.GetValue(.GetOrdinal("Phone")), String)
    end if

    keep in mind that you also can do it in TryCatch blok. But the first approach is faster

    Author Comment

    Thanks ndonhauser - your solution seems the most logical :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now