Dealing with NULL values in Integer fields

15 Jun 2006 5:08 AM

BobRoyAce

Let's say I have a table called Users which has a field DeptID which is
an int. I also have a User class which has a method for getting the
data for a particular user (i.e. User with specified ID) and which has
a field that corresponds to the DeptID field and it is called _DeptID
and is an Integer. Well, suppose that the value of the field in the
database is NULL. In that case, I can't assign the value from the DB to
the field.

Additionally, when I later save the data from the User object back out
to the database, assuming that the _DeptID field hasn't been assigned a
new value, I want to make sure that I save a NULL value back out to the
DB.

For all I know, there are other variable datatypes for which this will
be a problem as well (types that don't allow assignment to DBNULL).

What's are the variable types that don't allow DBNULL, and what's a
good way to deal with this?

The only idea that I have is to choose some value that I know would
never occur in the data, say -1, and assign that to the _DeptID field.
Then, later, when I go to save the data, using a parameterized query, I
could use a function that would take in an integer and pass back either
that integer, if not equal to -1, or DBNULL otherwise. If that's a good
solution, then what would be the return type of the function?

Function GetIntDBValueForInt(ByVal iIntToConvert as Integer) As ???

15 Jun 2006 5:13 AM

CT

If you're using VS 2005 or just the .NET Framework 2.0, nullable types comes
to the rescue. Check out the "What Are Nullable Types?" section here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/NetFramework.asp.

--
Carsten Thomsen
Communities - http://community.integratedsolutions.dk
---------
Voodoo Programming: Things programmers do that they know shouldn't work but
they try anyway, and which sometimes actually work, such as recompiling
everything. (Karl Lehenbauer)
---------

Show quoteHide quote

"BobRoyAce" <b***@omegasoftwareinc.com> wrote in message
news:1150348097.125777.248650@p79g2000cwp.googlegroups.com...
> Let's say I have a table called Users which has a field DeptID which is
> an int. I also have a User class which has a method for getting the
> data for a particular user (i.e. User with specified ID) and which has
> a field that corresponds to the DeptID field and it is called _DeptID
> and is an Integer. Well, suppose that the value of the field in the
> database is NULL. In that case, I can't assign the value from the DB to
> the field.
>
> Additionally, when I later save the data from the User object back out
> to the database, assuming that the _DeptID field hasn't been assigned a
> new value, I want to make sure that I save a NULL value back out to the
> DB.
>
> For all I know, there are other variable datatypes for which this will
> be a problem as well (types that don't allow assignment to DBNULL).
>
> What's are the variable types that don't allow DBNULL, and what's a
> good way to deal with this?
>
> The only idea that I have is to choose some value that I know would
> never occur in the data, say -1, and assign that to the _DeptID field.
> Then, later, when I go to save the data, using a parameterized query, I
> could use a function that would take in an integer and pass back either
> that integer, if not equal to -1, or DBNULL otherwise. If that's a good
> solution, then what would be the return type of the function?
>
> Function GetIntDBValueForInt(ByVal iIntToConvert as Integer) As ???
>

15 Jun 2006 6:46 AM

BobRoyAce

Thanks CT...

I am using Visual Studio 2005.

I changed my field to be:
Private _DeptID As Nullable(Of Integer)

However, when I try to read in,using a SqlDataReader, as follows, I get
an error saying that it's an invalid cast (System.InvalidCastException
was unhandled
Message="Specified cast is not valid."):

_DeptID = rdr("DeptID")

How do I do this correctly?

15 Jun 2006 8:11 AM

CT

One way is this:

If rdr.IsDBNull(...) Then
_DeptID = Nothing
Else
_DeptID = CInt(rdr("DeptID"))
End If

--
Carsten Thomsen
Communities - http://community.integratedsolutions.dk
---------
Voodoo Programming: Things programmers do that they know shouldn't work but
they try anyway, and which sometimes actually work, such as recompiling
everything. (Karl Lehenbauer)
---------

Show quoteHide quote

"BobRoyAce" <b***@omegasoftwareinc.com> wrote in message
news:1150353965.498691.149040@u72g2000cwu.googlegroups.com...
> Thanks CT...
>
> I am using Visual Studio 2005.
>
> I changed my field to be:
> Private _DeptID As Nullable(Of Integer)
>
> However, when I try to read in,using a SqlDataReader, as follows, I get
> an error saying that it's an invalid cast (System.InvalidCastException
> was unhandled
> Message="Specified cast is not valid."):
>
> _DeptID = rdr("DeptID")
>
> How do I do this correctly?
>

15 Jun 2006 2:07 PM

BobRoyAce

Thanks CT...

Too bad VB doesn't translate a DB field that's NULL to Nothing (there's
a difference?) automatically.

If I wanted to create a function, called let's say
GetNullableIntFromIntField, which took as its input an integer field
(i.e. rdr("DeptID")) and gave back either Nothing or the integer value,
what would the Function declaration look like?

Function GetNullableIntFromIntField(ByVal oSomething As ???) As ???
If IsDBNull(oSomething) Then
Return Nothing
Else
Return CInt(oSomething)
End If
End Function

15 Jun 2006 10:21 PM

BobRoyAce

One more question...In my property declaration, shown below, is it
correct to assign _DeptID = Value like I would have if it was just a
plain ole Integer, or is this like setting two objects equal to each
other where the end result is that the one on the left now points to
the same memory address as the one on the right?

Public Property _DeptID() As Nullable(Of Integer)
    Get
      Return _DeptID
    End Get
    Set(ByVal Value As Nullable(Of Integer))
      If Not Value.Equals(_DeptID) Then
        _DeptID = Value
        _HasChanged = True
      End If
    End Set
End Property   ' DeptID