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()
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()))
It means that one of the values you have received, is null, and it cannot be casted to string. You could implement a function that does the casting for you (and checks if a value is dbnull or nothing), something in the line of:
Function GetStringValue(value as Object) as String
if value is Nothing or IsDBNull(value)then
Return String.Empty
End If
Return DirectCast(value, GetType(String))
End Function
and then you could do
drop1l.Items.Add(new ListItem(GetStringValue(sdr2("name")), GetStringValue(sdr2("staff_id")))
You are getting this error because either sdr2("name") or sdr2("staff_id") is null. you can avoid it in two ways:
1.
drop1l.Items.Add(New ListItem(sdr2("name").Tostring(), sdr2("staff_id").Tostring()))
2. or check for null in the query
ToString() is a methodtry like this also
dt.Rows(0)("name").ToString()
