Null data not handled properly

Question

I am reading data from a SQL Server database with this code:

Dim rdr As SqlDataReader = cmd.ExecuteReader
While rdr.Read()
	evnt = New MyEvent With
		{
		.EventID = If(IsDBNull(rdr.GetInt32("EventID")), Nothing, rdr.GetInt32("EventID")),
		....
		.LastUpdate = rdr.GetDateTime("LastUpdate"),
		.OrganizerID = If(IsDBNull(rdr.GetInt32("OrganizerID")), Nothing, rdr.GetInt32("OrganizerID")),
		....
		}

This fails with this message:

System.Data.SqlTypes.SqlNullValueException HResult=0x80131931 Message=Data is Null. This method or property cannot be called on Null values.

If I remove the line that reads the OrganizerID column, all of the data are read correctly so that is the source of the problem. But my code is supposed to handle that null case. If I temporarily set OrganizerID=1 in the underlying tables, the data are read properly. Searching on the error message provides no useful information.

I don't know how to fix this problem.

Answer 1

I was first thinking "what is the AI smoking?", but reading the documentation brought me the missing piece in the puzzle:

That is, you don't feed IsDBNull a value returned from the database, but the ordinal for the column you want to investigate. Maybe you can even feed it the name directly, like you do with GetXxxx methods. (I don't write data-access code that often. My speciality is on the SQL Server side.)

Answer 2

IsDBNull(rdr.GetInt32("OrganizerID")) fails before IsDBNull can help, because GetInt32 is already trying to read the column as a non-null Int32. When the database value is NULL, that call throws SqlNullValueException.

Use a null check on the column value first, and only call GetInt32 when the value is not null.

In ADO.NET, database nulls are not the same as CLR nullable value types. For database ANSI SQL nulls, null handling must be done explicitly.

A correct pattern is:

While rdr.Read()
    evnt = New MyEvent With {
        .EventID = If(rdr.IsDBNull(rdr.GetOrdinal("EventID")), Nothing, rdr.GetInt32(rdr.GetOrdinal("EventID"))),
        .LastUpdate = rdr.GetDateTime(rdr.GetOrdinal("LastUpdate")),
        .OrganizerID = If(rdr.IsDBNull(rdr.GetOrdinal("OrganizerID")), Nothing, rdr.GetInt32(rdr.GetOrdinal("OrganizerID")))
    }
End While

Key points:

• NULL in SQL Server is an unknown or missing value.
• A null is not 0 and not an empty string.
• Comparisons and expressions involving null follow SQL three-valued logic.
• For database values, check for null before calling typed getters like GetInt32 or GetDateTime.

Also verify that the target property can actually hold null:

Public Property OrganizerID As Integer?

If OrganizerID is declared as plain Integer, assigning Nothing will not represent a database null in the way intended.

The same rule applies to any nullable database column, including LastUpdate if that column can also contain NULL.

---

• Handling Null Values
• Handling null values
• LINQ returns System.Data.SqlTypes.SqlNullValueException. Why? - Microsoft Q&A