i am running a query in vb.net and i want to test if the column is null. i have tried:
If reader.GetString(2) IsNot Nothing Then
If IsDBNull(reader.GetString(2)) Then
If reader.GetString(2) IsNot NULL Then
If NOT reader.GetString(2) IS Nothing Then
If NOT reader.GetString(2) IS NULL Then
but they all return:
Data is Null. This method or property cannot be called on Null values.
when i run my query in MySQL, the columns show NULL
The problem is the GetString method that internally cast your row value to a string. If the row value is null you get the exception.
The fix is using the VB.NET ternary operator
Dim result = IF(reader.IsDbNull(2), "", reader.GetString(2))
or if you don't want to assign a default value when the third field is null you could simply write
if Not reader.IsDbNull(2) Then
.......
End if
IsNotNull instead?
If Not reader.IsDBNull(2) ThenJust for future reference:
You have a couple of ways to check if the db value is or is not null.
The examples are here with full namespace.
Dim reader As System.Data.SqlClient.SqlDataReader
Dim fieldIndex As Integer = 0
' reader(fieldIndex) is equivalent to reader.Item(fieldIndex)
Dim fieldValue As Object = reader.Item(fieldIndex)
Dim isFieldValueNull As Boolean
' Namespace: System.Data.SqlClient; Class: SqlDataReader
isFieldValueNull = reader.IsDBNull(fieldIndex)
' Namespace: Microsoft.VisualBasic; Module: Information
isFieldValueNull = Microsoft.VisualBasic.IsDBNull(fieldValue)
' Namespace: System; Class: Convert
isFieldValueNull = System.Convert.IsDBNull(fieldValue)
' Namespace: System; Class: DBNull
isFieldValueNull = System.DBNull.Value.Equals(fieldValue)
Note: DBNull.Value always has an instance of DBNull therefore it is never Nothing!
If you would like to check if the database value is not null then you can put the Not keyword before the method call.
Dim isFieldValueNotNull As Boolean
isFieldValueNotNull = Not reader.IsDBNull(fieldIndex)
isFieldValueNotNull = Not Microsoft.VisualBasic.IsDBNull(fieldValue)
isFieldValueNotNull = Not System.Convert.IsDBNull(fieldValue)
isFieldValueNotNull = Not System.DBNull.Value.Equals(fieldValue)
