ASP.NET – Handling Null Return Values from the Database using IsDBNull

If you are retrieving or adding values from and to your database then you will know the difficulties that can be caused by NULL values. The notorious InvalidCastException is one example of receiving a NULL value from your database into an ASP.NET object that doesn’t know what to do with database NULL values.

Here’s typical example of an InvalidCastException error caused by setting a String variable to a value that is returned as a NULL from your database query:

System.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid. at Microsoft.VisualBasic.CompilerServices.Conversions.ToString(Object Value) at ASP.myPageName_aspx.Page_Load(Object sender, EventArgs e) in D:\Inetpub\wwwroot\MySite\myPageName.aspx:line 1xx

If you find this article useful, have a look at some of the other ASP.NET tips and tricks articles I’ve published.

So how to avoid the InvalidCastException?

You can catch  and deal with database NULL values either at the database level or in your ASP.NET code. The typical check at the database level to avoid this error is to use the ISNULL(MyField, ‘N/A’) syntax.

However sometimes you will want or need to check for null values in your ASP.NET code rather than at the database level. To do so, you can use the IsDBNull( method in your ASP.NET code.

Let’s take a look at an example of using IsDBNull in C# and VB.NET in the following code where dr is a datareader:

C#

if (!Convert.IsDBNull(dr.Item("ShoeSize")))
 {
 txtmyShoes.Text = dr.Item("ShoeSize");
 }

VB.NET

If Not Convert.IsDBNull(dr.Item("ShoeSize")) Then
 txtmyShoes.Text = dr.Item("ShoeSize")
 End If

Full Example Function in C# and VB.NET

Here is an example in C# of using a DataTable and using the IsDbNull function to set a text field while looping through the rows

public string GetUserShoeSize(string strFromId)
 {
 string returnValue = string.Empty;
 DataSet ds = new DataSet();
 DataTable dt = new DataTable();
 SqlConnection conn = null;
 SqlDataAdapter da = null;
 SqlCommand cmd = null;
 try
 {
 SqlParameter paramOne = new SqlParameter();
 paramOne.ParameterName = "ClientId";
 paramOne.DbType = DbType.String;
 paramOne.Value = strFromId;
 conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringFromConfig"].ConnectionString);
 conn.Open();
 cmd = new SqlCommand();
 cmd.Connection = conn;
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.CommandText = "myStoredProcedure";
 cmd.Parameters.Add(paramOne);
 da = new SqlDataAdapter();
 da.SelectCommand = cmd;
 da.Fill(ds);
 dt = ds.Tables[0];
 foreach (DataRow dr in dt.Rows)
 {
 if (!Convert.IsDBNull(dr["ShoeSize"]))
 {
 txtmyShoes.Text = dr["ShoeSize"];
 }
 }
 }
 catch (Exception ex)
 {
 returnValue = "Unknown Size";
 }
 finally
 {
 if ((ds != null))
 ds.Dispose();
 ds = null;
 if ((da != null))
 da.Dispose();
 da = null;
 if ((cmd != null))
 cmd.Dispose();
 conn.Close();
 conn.Dispose();
 conn = null;
 }
 return returnValue;
 }

Passing NULL Values Back to your DataBase

You also have to handle NULLS going the other way: back from your ASP.NET code to your database. For starters you’ll need to know how to set parameters going to the database to the NULL values that the database understands.

So  let’s say that you are calling a stored procedure that expects a shoe size parameter. If a user of your application does not select a shoe size in the drop-down list on your Web form, then  let’s say that you want to pass in a NULL value instead of the zero in your blank option (for example) to your stored procedure. You won’t be able to pass in your variable as a particular datatype such as String or Int32 since DBNull is not considered either of these types.

Here is how you would set your object variable to DBNull.Value in either C# or VB.NET:

In C#:

Int32 intSaveShoeSize = Convert.ToInt32(txtShoeSize.Text);
 object objSaveShoeSize = null;
 if (intSaveShoeSize > 0)
 {
 objSaveShoeSize = intSaveShoeSize;
 }
 else
 {
 objSaveShoeSize = DBNull.Value;
 }
 cmd.Parameters.AddWithValue("ShoeSizeParam", objSaveShoeSize);

In VB.NET:

Dim intSaveShoeSize As Int32 = CInt(txtShoeSize)
Dim objSaveShoeSize As Object
If intSaveShoeSize > 0 Then
objSaveShoeSize = intSaveShoeSize
Else
objSaveShoeSize = DBNull.Value
End If
cmd.Parameters.AddWithValue("ShoeSizeParam", objSaveShoeSize)