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

Malcolm Salvador · Accepted Answer · 2013-11-16 14:02:54Z

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, 2013 at 14:02

answered Nov 16, 2013 at 12:58

Malcolm Salvador

1,5663 gold badges22 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user1532468 Over a year ago

Does x cover dr(0) to dr(4)? or do I need to declare var for each line. Thanks

user1532468 Over a year ago

Would x handle dr(0),dr(1) etc. Please see my earlier comment. Thanks

Malcolm Salvador Over a year ago

you can use x for each instance of dr, friend

user1532468 Over a year ago

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 Over a year ago

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.

|

Merijn Den Houting · Accepted Answer · 2013-11-16 12:55:29Z

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, 2013 at 12:55

Merijn Den Houting

1991 silver badge12 bronze badges

3 Comments

Merijn Den Houting Over a year ago

You could maybe add a function to check if the value is NULL

Merijn Den Houting Over a year ago

Or you could check if the length is higher as 0 If dr(0).length > 0 Then txtname.Text = dr(0).ToString.

user1532468 Over a year ago

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

Community · Accepted Answer · 2017-05-23 12:33:44Z

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, 2017 at 12:33

CommunityBot

11 silver badge

answered Nov 16, 2013 at 13:00

Ric

13.3k3 gold badges32 silver badges36 bronze badges

6 Comments

user1532468 Over a year ago

Ric This now causes an error in my listview, code I have placed in original question. I have posted screenshot of error. Thanks

Ric Over a year ago

Are you doing the IsDBNull check and returning an empty string instead of the null?

user1532468 Over a year ago

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

Ric Over a year ago

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

user1532468 Over a year ago

Ric How would that code in Sub filllistview() example above. Thanks

|

schlebe · Accepted Answer · 2022-11-22 16:05:25Z

0

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")

answered Nov 22, 2022 at 16:05

schlebe

3,8395 gold badges44 silver badges60 bronze badges

Collectives™ on Stack Overflow

vb.net DBNull to string error

4 Answers 4

7 Comments

3 Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

3 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related