Null data not handled properly

Question

Null data not handled properly

RogerSchlueter-7899 1,801

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.

0 comments

Answer accepted by question author

Gatlin Le (WICLOUD CORPORATION) 320 Microsoft External Staff Moderator

Hi @RogerSchlueter-7899 ,

Thank you for sharing the details. As Erland Sommarskog correctly pointed out, the key issue is that IsDBNull expects the zero-based ordinal of the column, rather than a value already retrieved from the database.

In the original expression:

IsDBNull(rdr.GetInt32("OrganizerID"))

GetInt32 is evaluated first. If OrganizerID contains SQL NULL, the exception is raised before IsDBNull can perform the check.

You can retrieve the column ordinal first, check whether the column contains DBNull, and only then call GetInt32:

Dim organizerOrdinal As Integer = rdr.GetOrdinal("OrganizerID")

.OrganizerID =
    If(rdr.IsDBNull(organizerOrdinal),
       CType(Nothing, Integer?),
       CType(rdr.GetInt32(organizerOrdinal), Integer?))

The property should also be nullable if it needs to represent a database NULL:

Public Property OrganizerID As Integer?

The same pattern should be applied to any other database columns that may contain NULL. For example:

Dim eventIdOrdinal As Integer = rdr.GetOrdinal("EventID")
Dim lastUpdateOrdinal As Integer = rdr.GetOrdinal("LastUpdate")
Dim organizerOrdinal As Integer = rdr.GetOrdinal("OrganizerID")

While rdr.Read()
    evnt = New MyEvent With {
        .EventID =
            If(rdr.IsDBNull(eventIdOrdinal),
               CType(Nothing, Integer?),
               CType(rdr.GetInt32(eventIdOrdinal), Integer?)),
        .LastUpdate =
            If(rdr.IsDBNull(lastUpdateOrdinal),
               CType(Nothing, DateTime?),
               CType(rdr.GetDateTime(lastUpdateOrdinal), DateTime?)),
        .OrganizerID =
            If(rdr.IsDBNull(organizerOrdinal),
               CType(Nothing, Integer?),
               CType(rdr.GetInt32(organizerOrdinal), Integer?))
    }
End While

Microsoft's SqlDataReader.IsDBNull(Int32) documentation confirms that the parameter is the zero-based column ordinal and recommends calling IsDBNull before typed getters to avoid an exception.

Please try this pattern and let me know whether the exception is resolved. If you found my response helpful or informative, I would greatly appreciate it if you could share your thoughts by reacting to this answer or leaving a comment.

Thank you.

RogerSchlueter-7899 1,801 Reputation points

2026-09-14T15:36:04.9566667+00:00
I don't have to bother getting the ordinal. Here is what I eventually use:

.OrganizerID = If(rdr1.IsDBNull("OrganizerID"), Nothing, rdr1.GetInt32("OrganizerID")),

I think using the column name makes the code clearer.
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-09-14T21:00:23.7866667+00:00

Agreed. I was just confused, because the documentation did not seem to suggest that this option was available. And I was too lazy to try myself.
Gatlin Le (WICLOUD CORPORATION) 320 Reputation points Microsoft External Staff Moderator

2026-09-15T02:40:49.04+00:00

Thanks for sharing the revised version. I agree that using the column name is easier to read than tracking ordinals, especially once the query gets longer.

And the name-based overloads are genuinely easy to miss when you're scanning the docs, so that was a fair point to raise.

Thanks to both of you for following up here — it'll be a useful reference for anyone running into the same null-handling question later.

2 additional answers

Your answer

RogerSchlueter-7899 1,801 Reputation points

2026-09-14T15:36:04.9566667+00:00

I don't have to bother getting the ordinal. Here is what I eventually use:

.OrganizerID = If(rdr1.IsDBNull("OrganizerID"), Nothing, rdr1.GetInt32("OrganizerID")),

I think using the column name makes the code clearer.
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-09-14T21:00:23.7866667+00:00

Agreed. I was just confused, because the documentation did not seem to suggest that this option was available. And I was too lazy to try myself.
Gatlin Le (WICLOUD CORPORATION) 320 Reputation points Microsoft External Staff Moderator

2026-09-15T02:40:49.04+00:00

Thanks for sharing the revised version. I agree that using the column name is easier to read than tracking ordinals, especially once the query gets longer.

And the name-based overloads are genuinely easy to miss when you're scanning the docs, so that was a fair point to raise.

Thanks to both of you for following up here — it'll be a useful reference for anyone running into the same null-handling question later.

Answer 1

Erland Sommarskog 137.4K MVP Volunteer Moderator

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

User's image

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

RogerSchlueter-7899 1,801 Reputation points

2026-09-14T00:18:17.8166667+00:00

It is OK to use the name or ordinal to denote the column to read. I use the name because doing so eliminates mis-numbering a column.
RogerSchlueter-7899 1,801 Reputation points

2026-09-14T00:30:04.6833333+00:00

The AI answer is just plain wrong. The first sentence is wrong and the first code block returns the column NUMBER not the column VALUE. Finally, regarding the second code line, if the CLR variable could hold a NULL, then there is no need for the NULL check.

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.