locked
Testing the value of an integer type in VB RRS feed

  • Question

  • User1764167293 posted

    I have an int datatype in my SQL database table (NULLS allowed).

    Three values of this int datatype are significant:  NULL, 0 and > 0.

    I create a variable with:

    Dim IntType as Integer = Nothing

    And then, using SQL Linq, transfer the value from the int datatype in the table into the variable.

    If the int datatype is NULL, I leave IntType = Nothing.

    Now, when I test the value of IntType:

    If IntType Is Nothing Then

    gives an error because cannot use Is with an integer variable.

    So I tried:

    If IsDbNull(IntType) Then

    is never true, even when IntType is set to nothing.

    In fact, if IntType is set to Nothing, whilst debugging, IntType = 0 !!!

    What am I doing wrong here?  How do I code this to achieve the correct answers?

    Your help would be much appreciated (code snippets in VB please).

    Thanking you in anticipation.

    Roger

     

     

    Wednesday, June 2, 2010 12:27 PM

Answers

  • User-1536031397 posted

    You have to use Nullable types. If you are using .NET 3.5, then the shorthand in VB is the same as in C#: just putting a question mark at the end of the type makes it nullable. So our statement Dim IntType as Integer = Nothing would be modified to Dim IntType as Integer? = Nothing. The long-hand way is this:


    Dim IntType as Nullable (Of Integer) = Nothing


    When you assign Nothing in VB to any type, you are not actually assigning null necessarily, but rather the type's default value. For Int32 type (Integer), that default is zero. But for Nullable (Of Integer), the default is null.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 2, 2010 2:25 PM
  • User560403387 posted

    Roger,

    I don't have a VS2008 around to test this code, and my VB.Net is somewhat rusty, but here goes:

    Your IntType is of the type Integer. Integer is a value type, and cannot be Nothing. They will always be initialized with a default value (in case of your Integer: 0) (This is because value types store their value on the stack. Reference types store a pointer on the stack, and that pointer points to the value on the heap. "Nothing" represents a pointer on the stack that isn't pointing anywhere yet.)

    Your LINQ datacontext should have caught on to your database field allowing NULL values, and the type of your field in LINQ should be a nullable integer. So if you also declare your variable IntType to be not an integer, but a nullable Integer, you should be fine:

    Dim IntType As Nullable(Of Integer)
    Dim IsIntNothing As Boolean = Not IntType.HasValue

    As you see, Nullable even has a neat little method to check if it contains a value.

    Menno

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 2, 2010 2:39 PM

All replies

  • User-952121411 posted

    When working with Integers you technically can default the value to "Nothing", but an Integer will not hold that value; it will hold the value of zero '0'.  It is better to initialize an Integer as below:

    Dim MyInt As Integer = 0

     

    Now having it as null, 0, or >0 is perfectly acceptable in the db.  If you are trying to know if '0' comes back and it wasn;t just the defult value from the variable, then try an assignment like below instead:

    Dim MyInt As Integer = -1

     

    Next, testing to see if it is null before assignment.  Use code such as the following:

    'This example is if the data from the databse was in a DataRow object:
    If Not IsDBNull(dr("IntType")) Then MyInt = Integer.Parse(dr("IntType"))
    
    'Now test to see if a value was truely returned based on initialization to -1
    If MyInt <> -1 Then
        'Some value 0 or >0 was returned
    End If


    LINQ queries have a little different syntax.  Here is a line dumping into an object property value named 'MyIntValue':

    'Assigns -1 as the default value if the value is NULL in the database
    .MyIntValue = If(Not IsDBNull(MyObject.Field(Of Integer)("IntType")), MyObject.Field(Of Integer)("IntType"), -1)}


    Hope this helps! Smile

    Wednesday, June 2, 2010 2:24 PM
  • User-1536031397 posted

    You have to use Nullable types. If you are using .NET 3.5, then the shorthand in VB is the same as in C#: just putting a question mark at the end of the type makes it nullable. So our statement Dim IntType as Integer = Nothing would be modified to Dim IntType as Integer? = Nothing. The long-hand way is this:


    Dim IntType as Nullable (Of Integer) = Nothing


    When you assign Nothing in VB to any type, you are not actually assigning null necessarily, but rather the type's default value. For Int32 type (Integer), that default is zero. But for Nullable (Of Integer), the default is null.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 2, 2010 2:25 PM
  • User560403387 posted

    Roger,

    I don't have a VS2008 around to test this code, and my VB.Net is somewhat rusty, but here goes:

    Your IntType is of the type Integer. Integer is a value type, and cannot be Nothing. They will always be initialized with a default value (in case of your Integer: 0) (This is because value types store their value on the stack. Reference types store a pointer on the stack, and that pointer points to the value on the heap. "Nothing" represents a pointer on the stack that isn't pointing anywhere yet.)

    Your LINQ datacontext should have caught on to your database field allowing NULL values, and the type of your field in LINQ should be a nullable integer. So if you also declare your variable IntType to be not an integer, but a nullable Integer, you should be fine:

    Dim IntType As Nullable(Of Integer)
    Dim IsIntNothing As Boolean = Not IntType.HasValue

    As you see, Nullable even has a neat little method to check if it contains a value.

    Menno

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 2, 2010 2:39 PM
  • User-1963256800 posted

    Frankly, I dislike the fact that you can write things like:

    Dim i as Integer = Nothing

    (which sets the value to the default i.e. 0)

    But even worse:

    Sub Test (Byval i as Integer)

    accepts Nothing as a value.

    (which sets the value to the default i.e. 0)

    Value types have to have a default (e.g. 'Dim i as Integer' has a value of 0) , but I do wonder why one can explicitly assign Nothing to an integer or any value type? It makes explaining the difference between a reference type and a value type to someone much harder and is confusing when looking at code.

    As far as I know there is no option that makes any difference to this behaviour.

    Does anyone have a good explanation for this behaviour or is it just one of those things?

    James

     

    Thursday, June 3, 2010 7:29 AM