Option Strict On and SQL stored procedure output parameter of nullable integer

Question

There are several topics in this forum that come tantalisingly close to providing an answer to my question, but not quite what I need.

I am writing in VB.Net, retrieving data via TableAdapters and stored procedures. The stored procedures can return one or more Output parameters in the form of nullable integers.

This is some old code that I am revisiting and tidying up, including the addition of Option Strict On, and the Stored Procedure returns a zero where previously it returned a correct value. I can circumvent the problem but I would like to understand what "best practice" dictates for this circumstance.

This is the code before Option Strict was applied, and returns the correct value in the two Output parameters: RetVal (the return code, defined as an Enum) and UnspecifiedCategoryID (defined as an integer type).

Using oCategoriesTableAdapter As New YachtManagementDataSetTableAdapters.tvf_CategoriesTableAdapter

oCategoriesTableAdapter.sp_UpdateUnspecifiedCategory(
   RetVal:=SQLReturn,
   UnspecifiedCategoryID:=oCP.GviUnspecifiedCategorySubCategory,
   UnspecifiedCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameCategory).UnspecifiedEntityID,
   UnspecifiedSubCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameSubCategory).UnspecifiedEntityID,
   VesselID:=oCP.GvoActiveVessel.ID
   )

End Using

With Option Strict On, if I simply cast both to an integer, using CInt or use CType in order to remove the compiler error ("Option Strict On disallows narrowing from type 'Integer?' to type 'Integer'"), then I will always have a zero returned:

Using oCategoriesTableAdapter As New YachtManagementDataSetTableAdapters.tvf_CategoriesTableAdapter

oCategoriesTableAdapter.sp_UpdateUnspecifiedCategory(
    RetVal:=CType(SQLReturn, Integer),
    UnspecifiedCategoryID:=CType(oCP.GviUnspecifiedCategorySubCategory, Integer),
    UnspecifiedCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameCategory).UnspecifiedEntityID,
    UnspecifiedSubCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameSubCategory).UnspecifiedEntityID,
    VesselID:=oCP.GvoActiveVessel.ID
    )

End Using

I can circumvent the problem using this code:

Using oCategoriesTableAdapter As New YachtManagementDataSetTableAdapters.tvf_CategoriesTableAdapter

    oCategoriesTableAdapter.sp_UpdateUnspecifiedCategory(
        RetVal:=CType(SQLReturn, Integer),
        UnspecifiedCategoryID:=TestNullableInteger,
        UnspecifiedCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameCategory).UnspecifiedEntityID,
        UnspecifiedSubCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameSubCategory).UnspecifiedEntityID,
        VesselID:=oCP.GvoActiveVessel.ID
        )

End Using

If TestNullableInteger.HasValue Then

    oCP.GviUnspecifiedCategorySubCategory = TestNullableInteger.Value

Else

    oCP.GviUnspecifiedCategorySubCategory = 0

End If

Another alternative is to change the data type of GviUnspecifiedCategorySubCategory itself to that of a nullable integer and check there as to whether a value has been returned.

    Friend Property GviUnspecifiedCategorySubCategory As Integer?
        Get
            Return _lviUnspecifiedCategorySubCategory
        End Get
        Set(Value As Integer?)
            If Value.HasValue Then
                _lviUnspecifiedCategorySubCategory = Value
            Else
                _lviUnspecifiedCategorySubCategory = 0
            End If
        End Set
    End Property

However, if the stored procedure has three or four Output parameters then, using this approach, the recoding starts to become onerous. The GetValueOrDefault method shows promise but, using a TableAdapter, I cannot see how this would work.

There's always the chance that I've stopped seeing the wood for the trees.

Any suggestions would be much appreciated.

Can you change the stored procedure, to return ISNULL(@MyInteger, 0)? Ensure the SP always returns an integer value for each output parameter instead of a NULL, if your application doesn't want a NULL. — HardCode, Sep 24 '20 at 14:30
@HardCode - thanks for replying. My understanding is that, whatever I do on the SP side, the auto-generated code around the TableAdapter will always return a nullable type for an Output parameter - meaning I will always have this problem. This appears to be more of a compile-time issue - how do I avoid the errors with Option Strict On but not get a zero returned every time? — Neil Miller, Sep 24 '20 at 14:40
I never use the adapters... I prefer to use all code myself. And if the TableAdapter always returns a type you can't change, that's why :-) — HardCode, Sep 24 '20 at 14:43
Is the **FULL** error message "Error BC32029 Option Strict On disallows Narrowing from type 'Integer?' to type 'Integer' in copying the value of 'ByRef' parameter 'RetVal' back to the matching argument."? — TnTinMn, Sep 24 '20 at 15:55
Ah! @TnTinMn - are you about to suggest changing the AllowDbNull property to False for the Output parameters in the TableAdapter itself? Yes, I've tested that and it works - that's a much better approach, I think. — Neil Miller, Sep 24 '20 at 22:06

score 1 · Accepted Answer · edited Nov 13 '20 at 08:25

From the comments to the question:

Is the FULL error message "Error BC32029 Option Strict On disallows Narrowing from type 'Integer?' to type 'Integer' in copying the value of 'ByRef' parameter 'RetVal' back to the matching argument."? – TnTinMn

Yes @TnTinMn, that is the correct full message. – Neil Miller

You are too focused on purpose of the code (database interaction) while ignoring the code syntax. The following code produces the same error message.

Sub DemoIssue()
  Dim SQLReturn As Integer
  SomeMethod(SQLReturn)
End Sub

Sub SomeMethod(ByRef RetVal As Integer?)
  RetVal = 1
End Sub

Note that SQLReturn is a Integer type being passed as an argument to a method that takes a reference to a nullable integer.

If you would have clicked on the BC32029 in the error window to search for help on the error, you likely would have found Option Strict On disallows narrowing from type 'typename1' to type 'typename2' in copying the value of ByRef parameter 'parametername' back to the matching argument that explains:

A procedure call supplies a ByRef argument with a data type that widens to the argument's declared type, and Option Strict is On. The widening conversion is allowed when the argument is passed to the procedure, but when the procedure modifies the contents of the variable argument in the calling code, the reverse conversion is narrowing. Narrowing conversions are not allowed with Option Strict On.

To correct this error

Supply each ByRef argument in the procedure call with the same data type as the declared type, or turn Option Strict Off.

So all you need to do is define SQLReturn as Integer?

Regarding:

Ah! @TnTinMn - are you about to suggest changing the AllowDbNull property to False for the Output parameters in the TableAdapter itself? Yes, I've tested that and it works - that's a much better approach, I think. – Neil Miller

That is another option, but you need to understand the reason why it works is that TableAdapter code for sp_UpdateUnspecifiedCategory is rewritten to expect an Integer type for SQLReturn so there is no issue with ``SQLReturndefined as anInteger`

Edit To address comment:

SQLReturn is defined as an Enum with datatype Integer. I cannot define SQLReturnEnum as type Integer? How would you handle this case if you wanted to take advantage of Intellisense when using the Enum? If I coerce SQLReturn using CInt or CType I will only get a zero returned, even if I were to set the Output parameter property to AllowDbNull to False.

I believe that you are still trying to perform the type conversion on the argument sent to the method.

oCategoriesTableAdapter.sp_UpdateUnspecifiedCategory(
    RetVal:=CType(SQLReturn, Integer),...

The problem with this is that CType(SQLReturn, Integer) is a function that returns a new value. As the argument is passed by reference (ByRef) it is this new value that can be modified in the method; such modification is not propagated back to SQLReturn.

Assuming SQLReturnEnum is defined like:

Public Enum SQLReturnEnum As Integer
  [Default]
  A
  B
End Enum

Then an example of passing by reference a nullable Integer and retrieving its value as a SQLReturnEnum would be:

Sub CopyBackExample()
  Dim byRefSqlReturn As Integer? ' declare a temp variable to be passed ByRef 
  SomeMethod(byRefSqlReturn)
  ' cast the temp variable's value to type SQLReturnEnum
  Dim SQLReturn As SQLReturnEnum = If(byRefSqlReturn.HasValue, CType(byRefSqlReturn, SQLReturnEnum), SQLReturnEnum.Default)
End Sub

Thank you for your explanation @TnTinMn. SQLReturn is defined as an Enum with datatype Integer. I cannot define SQLReturnEnum as type Integer? How would you handle this case if you wanted to take advantage of Intellisense when using the Enum? If I coerce SQLReturn using CInt or CType I will only get a zero returned, even if I were to set the Output parameter property to AllowDbNull to False. — Neil Miller, Sep 25 '20 at 09:27
@NeilMiller, see the edit at the end of the answer. Hopefully that makes sense to you. — TnTinMn, Sep 25 '20 at 17:41
@NeilMiller, I have rejected your edit because it should have been posted in either your question or as a new answer. How you want to implement a solution is a matter of preference. I don't know the full usage of the return value in your code, but the sample you edited in only used the value once. You may not be aware of this, but you can do a comparison between nullable integers and Enum values. i.e. `If byRefSqlReturn = SQLReturnEnum.A Then` (using the example from this answer). — TnTinMn, Sep 26 '20 at 16:46

Option Strict On and SQL stored procedure output parameter of nullable integer

1 Answers1