Question about Nullable Types

Aaron Edwards · December 10th, 2006, 02:13 AM

I'm trying to learn how to use Nullable types in my applications.

I have a table in a database, with a column called FacilityID declared as type Int. Some of the rows will have a NULL value in this column.

I wrote this code to test with:

Code:

        Dim cnxn As New SqlConnection
        Dim cmd As New SqlCommand
        Dim reader As SqlDataReader
        Dim FacID As Nullable(Of Integer)

        cnxn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("EmployeeDBConnectionString").ConnectionString
        cnxn.Open()
        cmd.Connection = cnxn
        cmd.CommandText = "Select * from tbl_MyTable"
        reader = cmd.ExecuteReader

        While reader.Read
            FacID = reader("FacilityID")
        End While

The first time it gets to a NULL value in that column, I get an error message "The Specified Cast is Invalid".

What am I doing wrong here?

Thanks.

Aaron

Bob Bedell · December 10th, 2006, 11:56 AM

Since DataReaders don't provide any methods to deal with nullable types, you need to populate the type yourself with code like:

Code:

while (dr.Read())
    if (dr.IsDBNull(dr.GetOrdinal("FacilityID")))
        FacID = null;
    else
        FacID = dr.GetInt32(dr.GetOrdinal("FacilityID"));

Sorry 'bout the C# but I think the VB is virtually identical.

Bob

Aaron Edwards · December 10th, 2006, 12:23 PM

No problem, Bob. Thanks for the response. I think the VB equivalent for the null expression is vbNull.

Just as an editorial aside: it occurs as kind of silly to define a nullable type, then not let it take on the value of DBNull directly, considering that the main reason people might want a nullable type in the first place is to populate data from a database. Anyone have an opinion about that? You know I tried this same code but defined FacID as SqlInt32, and it got the same results. You can't cast a SqlInt32 as DBNull either.

Okay, so my second question then is: Populating controls on a page, e.g. textboxes: do I have to do the same thing:

Code:

If FacID.GetType is VBNull.Value Then
   Textbox1.Text = ""
Else
   Textbox1.Text = FacID
End If

And for that matter, do I need to do the same thing when updating my data store:

Code:

If FacId.GetType is vbNull.Value then
   cmd.Parameters.Insert (New SqlParameter ("@FacID", DbNull.Value))
Else
   cmd.Parameters.Insert (New SqlParameter ("@FacID", FacID))
End If

Thanks.

Aaron

Bob Bedell · December 10th, 2006, 04:10 PM

Hi Aaron,

In the first case, assigning the value of FacID to the text property, no need for the null check. Simply call FacID.ToString() in your assignment statement. If FaqID is null, an empty string will be assigned to the control.

In the second case, parameterizing a stored procedure, yes, you need the additional null checks:

Code:

cmd.Parameters.Add("@FacilityID", SqlDbType.Int);
if (FacID != null)
{
    cmd.Parameters["@FacilityID"].Value = FacID;
}
else
{
    cmd.Parameters["@FacilityID"].Value = DBNull.Value;
}

This will allow you to insert a null value in your database if FacID is null.

HTH,

Bob

Bob Bedell · December 10th, 2006, 11:13 PM

Hi Aaron,

Hold the presses on the paramterized stored procedure bit. In C#, the new nullable type operator - ?? - lets you rewrite the if...else null check as:

Code:

cmd.Parameters.Add("@FacilityID", FacID ?? (object)System.DBNull.Value);

Using ?? and casting DBNull to an object lets you have DBNull inserted for nullable types that have a null value.

Problem is, the ?? operator only exists in C#, and I'm clueless 'bout what the VB version of the above would look like. Hopefully, someone else can get you that snippet.

Bob

Aaron Edwards · December 10th, 2006, 11:57 PM

My understanding from the websites I've checked out is that VB doesn't have this construct available in the current version, but will in the next version of .NET (I read that they ran out of time or something).

It's a slick construct, although I don't know how VB will reconcile it's terseness. Seems so anti-VB.

Thanks.

Aaron