Optional Parameter of type Nullable(Of Integer)

I'm trying to create a sub that can be called from various places in my code.  It will interrogate the database and return a datatable.  Now I want an optional ID parameter in the sub, which gets passed to the database.  

So I could call this:
   getData(536)   which would return the database row with ID 536

Or I can call:
   getData()         which should return every row

The problem is that in Visual Studio I get the following error:
      BC31405: Optional parameters cannot have structure types.

Is my approach possible to implement?  I know I can remove the optional keyword and call getData(Nothing) but I'd like to know the alternatives if there are any!
Protected Sub getData(Optional ByVal Id as Nullable(Of Integer)) As DataTable
  ....
End Sub

Open in new window

LVL 25
RouchieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveH_UKCommented:
Try using ByVal Id as Nullable(Of Integer), and remember you need a Function to return something.
Protected Function ...(ByVal Id as Nullable(Of Integer)) as DataTable
  If Id.HasValue Then
    ' Return selection
  Else
    ' Return whole table 
  End If
End Function

Open in new window

0
PaulHewsCommented:
You don't really need a nullable type here... I usually do something like this.
Private Function GetData(Optional ByVal ID As Integer = -1) As DataTable
    If ID = -1 Then
        'get all rows'
    Else
        'select rows where id = id parameter'
    End If
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveH_UKCommented:
Nullable is a better option, because it forces the developer to intentionally choose what to do.  Yes it is harder to call -- you can always get around this by overloading the function --- but you make your code clear.

Using -1 is a bad idea, because it attaches special significance to a particular number.  If the number is truly always >= 0, then you should use an unsigned type.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

PaulHewsCommented:
>Using -1 is a bad idea, because it attaches special significance to a particular number.  If the number is truly always >= 0, then you should use an unsigned type.

Kind of like when you use IndexOf that returns -1 when the object is not found?  It's just a convention, and not a terrible one.  We use them all the time in .NET.

With the code I posted  you can call
 GetData() or GetData(Number)

which was exactly what the questioner asked for.  You can ignore the value of -1 while using the function as it is the optional default.

If you want to be picky, passing a null to the function isn't particularly self-explaining either.  What is the significance of the null in question?  It's just a convention, it doesn't really mean the ID is null.

To continue being picky, the really clear way to do it is with two functions:

Private Function getAllData() As DataTable

and

Private Function getDataByID(ByVal ID as Integer) As DataTable

but it's more a question of style than anything.
0
SteveH_UKCommented:
Actually, your last comment is what I offered in using an overloaded function.  I agree there are conventions, but not all of them are good and many come from a historical perspective.  With a language that supports Null values, it makes far more sense to use them.

The problem with using -1 is that it reduces the range of acceptable values, and suggests that it is passing a valid value.

I was not suggesting that your response wasn't _a_ solution, but I disagree strongly that using arbitrary values to control behaviour is good practice.
0
Jaime OlivaresSoftware ArchitectCommented:
I support PaulHews's solution. It is more legible and simple than Nullable (specially in vb.net)
About the -1 stuff, it is clear that is an invalid ID, so it can be used without worries. Not a bad idea at all, and not a big deal, because the function GetData() doesn't return it, it returns a valid DataSet or Nothing.
Inside GetData you can evaluate it as:
If ID<0 Then...

This approach is vastly used in WinAPI and .net libraries. By example, .net IndexOf()
0
RouchieAuthor Commented:
Thanks for your replies.  Your advice has confirmed my suspicions.

I'll have a think about which approach is going to be best in the long run.  Just out of interest, what does this error actually mean?  Why isn't it possible to do something like:

  Protected Function getData(Optional ByVal Id as Nullable(Of Integer) = Nothing) As DataTable
0
SteveH_UKCommented:
The Nullable(Of Integer) type is actually represented by a structure, a bit like an envelope around an Integer value.  This is distinct from a reference type.  

Basically, these optional parameters are a VB.NET language construct and there are some things they do not support.  There isn't a really good reason for it in your case.  Note that "Nullable(Of Integer) = Nothing" is a statement involving a conversion, because Nothing is not the same Type as Nullable(Of Integer), so that throws up further errors.  Using an overloading function would let you do exactly what you want, in much the same way you are actually causing the compiler to do it.

You might also want to consider reading:
http://jelle.druyts.net/2003/05/17/OptionalParametersVBNET.aspx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.