CODEWITHSUNDEEP
vb.netvisual-studio-2008operator-keyworddbnull
ImGonnaEatChocolateTillIBarf
ImGonnaEatChocolateTillIBarf

Reputation: 19

dBNull.value and is operator

I am porting a VB2005 web app to VB2008, and running into problems compiling it.

The problem is caused when I try to compile the project, I get this error on the line:

If m Is DBNull.Value...

Error:

'Is' operator does not accept operands of type 'Integer'. 
Operands must be reference or nullable types.

Code:

Public Shared Function getNextID(ByVal t As String, ByVal f As String)

        Dim m = 0

        Dim c As New Data.SqlClient.SqlConnection
        c = getConn()
        c.Open()

        Dim s As String
        s = "SELECT MAX(" & f  & ")AS max FROM " & t

      Dim q As New Data.SqlClient.SqlCommand(s, c)
        Dim r = q.EmecuteReader

        While r.Read
            m = r.GetValue(0)
        End While

        If m Is DBNull.Value Then
            m = 1
        Else
            m += 1
        End If

        r = Nothing
        q = Nothing
        c.Close()
        c = Nothing

        Return m

End Function

Is there a way to allow this fucntion to operate without changing it? And if not, how would I fix it?

Upvotes: 1

Views: 4696

Answers (3)

Sohail Hameed
Sohail Hameed

Reputation: 988

Just make your variable m nullabale 

  Dim m As Nullable(Of  Integer)

Upvotes: 0

Andrew Morton
Andrew Morton

Reputation: 25027

You could eliminate the problem at the database query stage with something like

SELECT COALESCE(MAX(" & f & "), 0) AS max FROM " & t

and then unconditionally increment m.

Also, you could use ExecuteScalar instead of ExecuteReader, seeing as you are only getting one value.

Upvotes: 0

Steven Doggart
Steven Doggart

Reputation: 43743

Your problem is the following line:

Dim m = 0

I suspect that the reason it worked in VS2005 is because Option Strict was turned off, so the type of m defaulted to Object. In other words, in VS2005, with Option Strict Off, the above line would be the equivalent of:

Dim m As Object = 0

If m is an object, then any value referenced by it would be boxed, so the Is operation would be allowed. In your VS2008 project, however, I suspect that Option Infer is turned on (a feature that did not exist in 2005), so the type of m is inferred at compile-time to be an Integer because you set it equal to an integer literal (Dim m = 0). In other words, in VS2008, with Option Infer On the above line would be the equivalent of:

Dim m As Integer = 0

Since it is declared as an Integer type, rather than an Object type, you can't us the Is operator anymore. More information about this can be found on this page of the MSDN, where this example is given:

Dim qty = 5

  • If Option Infer is on (the default), the variable takes the data type of the initializer. See Local Type Inference (Visual Basic).
  • If Option Infer is off and Option Strict is off, the variable takes the data type of Object.
  • If Option Infer is off and Option Strict is on, a compile-time error occurs.

The easiest way to fix the problem would be to simply turn Option Infer Off so that it will compile in the same way as VS2005. If, however, you want to leave Option Infer On and, instead, fix the code, you'll need to specify the variable type, like this:

Dim x As Object = 0

You might think you can fix it by changing the way you check if m Is DBNull. For instance, either of the following lines would compile:

If Convert.IsDBNull(m) Then

Or:

If TypeOf m Is DBNull Then

However, you'll notice that neither of those two If statements will ever evaluate to true. If m is declared as an Integer, then it can never be DBNull. If the column in the database ever equalled null, the following line would throw an exception because it would try to cast a DbNull object to an Integer which is impossible:

m = r.GetValue(0) ' This will throw exception if column is null and m is an integer

The only way for the m variable to support equaling either an Integer or a DbNull object is if it were declared as Object.

If the project has many places where no type is specified, like this, you should turn off Option Infer. If you want to find all of the places where it is a problem, so that you can fix them all in the code, and then turn Option Infer back on, you can temporarily turn Option Infer Off and turn Option Strict On. When Option Strict is on and Option Infer is off, the project will not compile unless all variable types are always specified.

Upvotes: 3

Related Questions