Best way to handle NULLs

Posted on 2004-11-23
Medium Priority
Last Modified: 2010-04-23
What's the best way to deal with NULL values in VB.net? 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

Accepted Solution

ndonhauser earned 2000 total points
ID: 12659325
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

ID: 12659336
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

ID: 12659383
Thanks ndonhauser - your solution seems the most logical :)

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 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