Dealing with NULL values in Integer fields

[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 GetIntDBValueFo rInt(ByVal iIntToConvert as Integer) As ???

[CT]

Re: Dealing with NULL values in Integer fields

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/de...tFramework.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)
---------
"BobRoyAce" <broy@omegasoft wareinc.com> wrote in message
news:1150348097 .125777.248650@ p79g2000cwp.goo glegroups.com.. .[color=blue]
> 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 GetIntDBValueFo rInt(ByVal iIntToConvert as Integer) As ???
>[/color]

[BobRoyAce]

Re: Dealing with NULL values in Integer fields

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.Invalid CastException
was unhandled
Message="Specif ied cast is not valid."):

_DeptID = rdr("DeptID")

How do I do this correctly?

[CT]

Re: Dealing with NULL values in Integer fields

One way is this:

If rdr.IsDBNull(.. .) Then
_DeptID = Nothing
Else
_DeptID = CInt(rdr("DeptI D"))
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)
---------
"BobRoyAce" <broy@omegasoft wareinc.com> wrote in message
news:1150353965 .498691.149040@ u72g2000cwu.goo glegroups.com.. .[color=blue]
> 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.Invalid CastException
> was unhandled
> Message="Specif ied cast is not valid."):
>
> _DeptID = rdr("DeptID")
>
> How do I do this correctly?
>[/color]

[BobRoyAce]

Re: Dealing with NULL values in Integer fields

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
GetNullableIntF romIntField, 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 GetNullableIntF romIntField(ByV al oSomething As ???) As ???
If IsDBNull(oSomet hing) Then
Return Nothing
Else
Return CInt(oSomething )
End If
End Function

[BobRoyAce]

Re: Dealing with NULL values in Integer fields

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(_D eptID) Then
_DeptID = Value
_HasChanged = True
End If
End Set
End Property ' DeptID