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

JB4375
JB4375 used Ask the Experts™
on
I have SQL table that is used to track equipment and has the following fields:
Sequence, Serial, AssetTag, Location, Notes, Requestor, Submitted

DropDownList1 lists the search option OR column names
TextBoxt1 lists the search string.

The search function works fine and returns data to the form unless I have no entry in the Notes field. In this case I get the following error: Conversion from type 'DBNull' to type 'String' is not valid. I need to know how to get around this error for any of the fields.

Also, at the top of the sub I set all the variables = " ".

Otherwise I get this error: Variable is used before it's assigned a value. A null reference exception could result at runtime. It seems to work fine, but I'm sure there's probably a better method?  Any comments on this or any other poor practice that you see would be most appreciated.

Thanks!!



Public Sub SQL_Search()
        Dim strAssetTag, strLocation, strNotes, strColumn, strSearch, strSerial As String

        'Set value of " " otherwise error: Variable is used before it's assigned a value. A null reference exception could result at runtime.
        strSerial = " "
        strAssetTag = " "
        strLocation = " "
        strNotes = " "
        strColumn = " "
        strSearch = TextBox1.Text

        If DropDownList1.Text = "Serial Number" Then
            strColumn = "Serial"
        End If
        If DropDownList1.Text = "Asset Tag" Then
            strColumn = "AssetTag"
        End If

        Dim sConn As Data.SqlClient.SqlConnection
        Dim sComm As Data.SqlClient.SqlCommand
        Dim sAdapt As Data.SqlClient.SqlDataAdapter

        sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
        sComm = New Data.SqlClient.SqlCommand("SELECT Sequence, Serial, AssetTag, Location, Notes, Requestor, Submitted FROM Barcode WHERE UPPER(" & strColumn & ")Like'%" & strSearch & "%'", sConn)
        sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)

        sConn.Open()

        Dim objDR As Data.SqlClient.SqlDataReader = sComm.ExecuteReader

        Do While objDR.Read()
            strSerial = objDR("Serial")
            strAssetTag = objDR("AssetTag")
            strLocation = objDR("Location")
            strNotes = objDR("Notes")
        Loop

        If objDR.HasRows = False Then
            UserMsgBox(Me, "No Value Found. Check Your Search Entry.")
        Else
            TextBox2.Text = strSerial
            TextBox3.Text = strAssetTag
            TextBox4.Text = strLocation
            TextMulti.Text = strNotes

        End If

        objDR.Close()
    End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
 sComm = New Data.SqlClient.SqlCommand("SELECT Sequence, Serial, AssetTag, Location, isnull(Notes.''), Requestor, Submitted FROM Barcode WHERE UPPER(" & strColumn & ")Like'%" & strSearch & "%'", sConn)


try this
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Change line 35 above from

    strNotes = objDR("Notes")

to

    If objDR.IsDBNull(4) Then
        strNotes = String.Empty
    Else
        strNotes = objDR("Notes")
    End If

The IsDBNull() method only works with column indexes for some strange reason, that is the reason I used "4" instead of "Notes". Just make sure if you alter the column position in your query then you alter the index on IsDBNull also.
Most Valuable Expert 2012
Top Expert 2008
Commented:
Simple approach with string columns and DataReader--ToString().

    strNotes = objDr("Notes").ToString()

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
TLO,
I'm all about the easy method. Adding ANYTHING more to the SQL Query string can be such a nightmare.
What about at the top of the sub where I set the variables = " " ? Is that acceptable practice? I was really just trying anything to make it work.
 
Most Valuable Expert 2012
Top Expert 2008
Commented:
Thoughts and musings:

1) Presetting values for variables is definitely an acceptable practice.  Strings are NULL (Nothing) by default, which is different than an empty string.

2) Another possibility is to declare and set the variable in the same statement:

   Dim strAssetTag As String = ""
   Dim strLocation As String = ""

3) Something to think about when converting from VB6 to VB.NET is the desired naming convention, which removes the need for Hungarian notation (assetTag instead of strAssetTag).

4) The SqlDataAdapter has overloaded constructors, and there is one that you can use that will create an internal SqlCommand and SqlConnection, so that you don't have to define one for yourself.

Example:

    Dim adapter As New SqlDataAdapter("command text goes here", "connection string goes here")
    Dim dt As New DataTable()
    adapter.Fill(dt)

5) If you are using the SqlDataReader, then you don't need the SqlDataAdapter, so creating an instance of one is unnecessary.  The SqlDataReader will need the SqlConnection and SqlCommand.

Author

Commented:
Great answers as always!!

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