Conversion from type 'DBNull' to type 'String' is not valid vb.net

Question

While using the given below code showing one error. The error is: "Conversion from type 'DBNull' to type 'String' is not valid." Help me to find a proper solution. Thank you.

Code:

cmd2.CommandText = "SELECT [first_name]+' ' +[middle_name]+' ' + [last_name] AS NAME, [staff_id] FROM [staff_profile]"
sdr2 = cmd2.ExecuteReader
While sdr2.Read
drop1l.Items.Add(New ListItem(sdr2("name"), sdr2("staff_id"))) // error popup here
End While
sdr2.Close()

Answer 1

You should try like this:

If Not IsDBNull(dt.Rows(0)("name")) Then
    sdr2.Value = dt.Rows(0)("name")
End If
If Not IsDBNull(dt.Rows(1)("staff_id")) Then
    sdr2.Value = dt.Rows(1)("staff_id")
End If

or a dirty fix like this:

drop1l.Items.Add(New ListItem(sdr2("name").ToString(), sdr2("staff_id").ToString()))