SQLException when reading NULL values

It happened with me. I had one or two NULL values in one of the records in my database. When I tried to read it in VB.NET (or C#), a SQLException popped up saying couldn’t execute SQLDataReader on NULL values. So here’s the remedy.

I will consider my scenario. If your problem is analogous to mine you will surely be helped.

I had a contact database which was created using the following query:

CREATE TABLE [Users] (
  [ID] NVARCHAR(16) PRIMARY KEY,
  [Name] NVARCHAR(32) NOT NULL,
  [AddressLine1] NVARCHAR(32) NOT NULL,
  [AddressLine2] NVARCHAR(32)NULL,
  [City] NVARCHAR(32) NOT NULL,
  [State] NVARCHAR(32) NOT NULL,
  [Country] NVARCHAR(32) NOT NULL,
  [Phone] NVARCHAR(15) NOT NULL,
  [Email] NVARCHAR(32) NOT NULL
);

In the above query, all the columns except [AddressLine2] cannot be null. So the exception would occur while reading [AddressLine2] using a SQLDataReader. The solution to it is to check whether it is NULL or not, and then read the value.

For example, consider a SQLDataReader DBReader which I am using to read the values:

Dim Address2 As String = DBReader.GetString(3)

This would throw an exception if the value is NULL as stored in the database.

So simply wrap it up in an If statement. Like this:

Dim Address2 As String = ""
If Not IsDBNull(DBReader(3)) Then
  Address2 = DBReader.GetString(3)
End If

But remember, don’t call the GetString() method inside the If statement because it will try to read the NULL value and again throw an error.

Advertisements

Your Thoughts -

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s