How to ignore eror message "dbnull to string is not valid" and dont load empty cells to my vb form

Question

When I click search button error occurs conversion dbnull to type string is not valid.

I think there's no data in some cells in my database.

its not a problem to have empty cells and if there are empty cells I don't want to load them to my form.

Here's what I tried ButtonClickEvent :

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
        con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Dimuth De Zoysa\Documents\divimaga_DB.accdb")
        cmd = New OleDbCommand("SELECT * FROM MainE WHERE Job ='" + ComboBox1.Text + "'  ", con)

        con.Open()
        dr = cmd.ExecuteReader()

        While dr.Read
            jobid = dr("JobID")
            TextBox1.Text = dr("PROCEDURE")
            TextBox2.Text = dr("ReleventDocuments")
            TextBox3.Text = dr("ImportantPoints")
            TextBox4.Text = dr("officer")
        End While

        con.Close()
    End Sub

jmcilhinney · Accepted Answer · 2018-04-20T10:36:54.697

The code you have wouldn't compile with Option Strict On even if there were no nulls in the data. That means it's bad code because Option Strict should pretty much always be On. The Text property of a TextBox is type String, while you're assigning Object references to those properties. You should be casting or converting to type String already. If you call ToString on each field value then you kill two birds with one stone because null fields will contain DBNull.Value and calling ToString on that returns an empty String.

TextBox1.Text = dr("PROCEDURE").ToString()
TextBox2.Text = dr("ReleventDocuments").ToString()
TextBox3.Text = dr("ImportantPoints").ToString()
TextBox4.Text = dr("officer").ToString()

Sebastian Brosch · Answer 2 · 2018-04-20T08:05:29.140

You can use the following, wrap your column value to CStr("" & dr("column")):

While dr.Read
    jobid = CStr("" & dr("JobID"))
    TextBox1.Text = CStr("" & dr("PROCEDURE"))
    TextBox2.Text = CStr("" & dr("ReleventDocuments"))
    TextBox3.Text = CStr("" & dr("ImportantPoints"))
    TextBox4.Text = CStr("" & dr("officer"))
End While

Another solution, using ToString():

While dr.Read
    jobid = dr("JobID").ToString()
    TextBox1.Text = dr("PROCEDURE").ToString()
    TextBox2.Text = dr("ReleventDocuments").ToString()
    TextBox3.Text = dr("ImportantPoints").ToString()
    TextBox4.Text = dr("officer").ToString()
End While

How to ignore eror message "dbnull to string is not valid" and dont load empty cells to my vb form

2 Answers2