vb.net DBNull to string error

Question

I have a sub which just adds the values from a access db to textboxes. However, some of the fields in the db contain null values or are empty and this is causing vb to throw an error, 'Cannot convert from DBNull to string'.

How can I fix this based on my posted code or if someone could post a tutorial for this type of event, as I am a new user struggling to make sense of this. Many thanks

Sub add()
        While dr.Read()
            txtname.Text = dr(0).ToString()
            txtfathername.Text = dr(1).ToString()
            txtaddress.Text = dr(2).ToString()
            txtemail.Text = dr(3).ToString()
        End While

    End Sub

UPDATE:

Sub filllistview()
        Try
            'creatconn()
            cn.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from Postings", cn)
            dr = cmd.ExecuteReader()
            While dr.Read()
                ListView1.Items.Add(dr(0).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(1))
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(2))
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(3))

            End While

        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        Finally
            dr.Close()
            cn.Close()
        End Try
    End Sub

Sub showcontectsinlistview()
        Try
            'creatconn()
            cn.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("select * from Postings where [Code]='" & ListView1.Text & "'", cn)
            dr = cmd.ExecuteReader()
            add()

        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        Finally
            dr.Close()
            cn.Close()
        End Try
    End Sub

enter image description here

score 1 · Answer 1 · answered Nov 16 '13 at 12:55

1

I think you should just check if the values are not NULL

Sub add()
    While dr.Read()
        If dr(0) Then txtname.Text = dr(0).ToString()
        If dr(1) Then txtfathername.Text = dr(1).ToString()
        If dr(2) Then txtaddress.Text = dr(2).ToString()
        If dr(3) Then txtemail.Text = dr(3).ToString()
    End While
End Sub

There probably is a better way though...

answered Nov 16 '13 at 12:55

Merijn Den Houting

199
1
12

You could maybe add a function to check if the value is NULL – Merijn Den Houting Nov 16 '13 at 13:22
Or you could check if the length is higher as 0 `If dr(0).length > 0 Then txtname.Text = dr(0).ToString`. – Merijn Den Houting Nov 16 '13 at 13:26
I thought something like this would do it: If(IsDBNull(Dr(2), string.Empty, Dr(2)) but not sure how to include in my Sub filllistview() example above. Thanks – user1532468 Nov 16 '13 at 13:30

Malcolm Salvador · Accepted Answer · 2013-11-16T14:02:54.553

1

Detect if the data coming from the row is empty first, then replace it with other values with will make it into a string, then you can pass it onto your textbox

Use the nothing function:

dim x as string
if dr(0).value = nothing then
    x = "Null data"
else
    x = dr(0).value
end if

It seems the problems stems on the part when you read the data from the database. can you try changing your query to not output null values?

something like

   SELECT Field
   FROM Table
   WHERE Not(Field) is null;

edited Nov 16 '13 at 14:02

answered Nov 16 '13 at 12:58

Malcolm Salvador

1,476
2
21
40

Does x cover dr(0) to dr(4)? or do I need to declare var for each line. Thanks – user1532468 Nov 16 '13 at 13:02
Would x handle dr(0),dr(1) etc. Please see my earlier comment. Thanks – user1532468 Nov 16 '13 at 13:35
you can use x for each instance of dr, friend – Malcolm Salvador Nov 16 '13 at 13:43
Malky I have tried after setting your code and replacing mine with what I thought was correct but causes error. ListView1.Items.Add(dr(0).x) – user1532468 Nov 16 '13 at 13:50
Please see my comment, friend. – Malcolm Salvador Nov 16 '13 at 14:03
That means I have to check all fields for null or empty value. In there no way to check in the loop? Thanks – user1532468 Nov 16 '13 at 18:27
1

I shall start a new post because I think we have moved from original question as I am 50% there. Thanks and please accept the answer. – user1532468 Nov 16 '13 at 19:04

score 1 · Answer 3 · edited May 23 '17 at 12:33

1

You will need to check if the value IsDBNull first like so

Dim value As String = If(dr.IsDBNull(dr(0)), "", dr(0).ToString())

then use the value

you can put this logic into a method and do the return from there.

or in your method:

Sub add()
    While dr.Read()
        If dr(0) Then txtname.Text = If(dr.IsDBNull(dr(0)), "", dr(0).ToString())
        If dr(1) Then txtfathername.Text = If(dr.IsDBNull(dr(1)), "", dr(1).ToString())
        If dr(2) Then txtaddress.Text = If(dr.IsDBNull(dr(2)), "", dr(2).ToString())
        If dr(3) Then txtemail.Text = If(dr.IsDBNull(dr(3)), "", dr(3).ToString())
    End While
End Sub

SqlDataReader.IsDBNull

also check other SO answers: SQL Data Reader - handling Null column values

.Add(dr(0).ToString())

becomes:

.Add(If(dr.IsDBNull(dr(0)), "", dr(0).ToString()))

edited May 23 '17 at 12:33

Community

1
1

answered Nov 16 '13 at 13:00

Ric

12,855
3
30
36

Ric This now causes an error in my listview, code I have placed in original question. I have posted screenshot of error. Thanks – user1532468 Nov 16 '13 at 13:09
Are you doing the IsDBNull check and returning an empty string instead of the null? – Ric Nov 16 '13 at 13:13
Ric I am using the code you posted. However, I am posting another piece of code that calls the add(). How do I know what I am returning? Thanks – user1532468 Nov 16 '13 at 13:16
Whenever you try to access a column value of the reader for instance dr(0) you should be doing dr.IsDBNull(dr(0)) first to check for the null – Ric Nov 16 '13 at 13:19
Ric How would that code in Sub filllistview() example above. Thanks – user1532468 Nov 16 '13 at 13:24
Ric errors with: 'index out of bounds of the array' whatever that means. – user1532468 Nov 16 '13 at 13:40

score 0 · Answer 4 · answered Nov 22 '22 at 16:05

The shortest solution is following ...

Sub Add()
    While dr.Read()
        txtname.Text = dr(0) & ""
        txtfathername.Text = dr(1) & ""
        txtaddress.Text = dr(2) & ""
        txtemail.Text = dr(3) & ""
    End While
End Sub

This solution works also with column name !

Sub Add()
    While dr.Read()
        txtname.Text = dr("name") & ""
        txtfathername.Text = dr("father_name") & ""
        txtaddress.Text = dr("address") & ""
        txtemail.Text = dr("email") & ""
    End While
End Sub

When dr("colum_name") is DBNULL, .Text value is an empty string.

When column type is an integer, you can also initialize .Text property with "0" String.

txtNrChildren.Text = CStr(dr("nr_children") + "0")

vb.net DBNull to string error

4 Answers4