CODEWITHSUNDEEP
mysqlsqlvb.net
charlie
charlie

Reputation: 481

vb.net check if database value is null

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

Upvotes: 1

Views: 11042

Answers (2)

Gabor
Gabor

Reputation: 3246

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

Upvotes: 0

Steve
Steve

Reputation: 216293

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

Upvotes: 6

Related Questions