Dealing with NULL values in Integer fields

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

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 ???
 
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?
 
One way is this:

If rdr.IsDBNull(...) Then
_DeptID = Nothing
Else
_DeptID = CInt(rdr("DeptID"))
End If
 
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
 
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
 
Back
Top