469,568 Members | 1,350 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

Dealing with NULL values in Integer fields

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 ???

Jun 15 '06 #1
5 1809
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/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" <br**@omegasoftwareinc.com> wrote in message
news:11**********************@p79g2000cwp.googlegr oups.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 ???

Jun 15 '06 #2
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?

Jun 15 '06 #3
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)
---------
"BobRoyAce" <br**@omegasoftwareinc.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.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?

Jun 15 '06 #4
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

Jun 15 '06 #5
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

Jun 15 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Justin | last post: by
9 posts views Thread by John Sidney-Woollett | last post: by
2 posts views Thread by Matt MacDonald | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.