How to Return DBnull or Integer from a Function

Hello -

I'm building a web app using ASP.NET 4 with Visual Basic.

I've written a function - basically a lookup function - that uses input parameters to find the matching record ID.  If it finds a match, it should return the ID number, but if it doesn't it should return DBnull.value.

I found nullable types and declared the function As Nullable(of Integer), thinking that would solve it. But then I learned that nullable doesn't mean null, it means nothing!

So, when the lookup function doesn't find a match, it returns nothing instead of null. I realize I could test the returned value and if it is nothing then use Dbnull for the value in the new record, but I call this function many times and that approach seems so cumbersome.  Is there any way to handle this situation one time in the function?

Thank you!
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.

Obadiah ChristopherDeveloper User InterfaceCommented:
What happens if u declare the return type as typeof object. But my guess is u hav 2 do the chk. Can't u use ? operator?
Any function that returns Object, can return Dbnull.Value or a string or any ValueType (Boxed).

By example, the DbCommand.ExecuteEscalar function returns the result for a query to a Database, this result can be null (Dbnull.Value).

   Public Function FindRecord(...) as Object
        If Found then Return id 'This is a Boxed integer
        Return Dbnull.Value
   End Function

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
JMS1965Author Commented:
Changing the Function return an Object worked perfectly, and I didn't have to adjust any of the lines that call this function (20 or so to date, with more to go) at all. Thank you!

In case anyone finds this topic later, I'm including the function code below.  

Thanks again!

FYI ... this function is used during a data migration process. The user uploads an Access database, and the records in that file are processed and imported into the web app's SQL Server database. Each record gets a new ID number on the SQL side, so as child records are added they need to be re-assigned to the parent record's new ID number.

The function code begins by assigning varResult = DBnull.value. If a matching record is found, varResult gets re-assigned to the found record's ID number.
Public Shared Function GetNewIDfromFAid(ByVal intAcct As Integer, ByVal strTable As String, ByVal strIDfaField As String, _
                                        ByVal strIDnewField As String, ByVal intIDfa As Integer) As Object
        'For given SQL Table, locate the new ID of the record based on the AcctID and the [*]idFA provided.
        'Arguments provided:
        '   intAcct         =   Account ID of the current user
        '   strTable        =   Name of SQL table where lookup will be performed
        '   strIDfaField    =   Name of the Field that contains the old FA id number (ends in "IDfa")
        '   strIDnewField   =   Name of the primary key for the table; holds the value being sought
        '   intIDfa         =   Value of the old FA id number

        Dim varResult As Object = DBNull.Value

        'connect to the SQL Server table
        Dim strConnSQLnewID As String = ConfigurationManager.ConnectionStrings("ApplicationServices").ConnectionString
        Dim connSQLnewID As New SqlConnection(strConnSQLnewID)

        Dim strSelect As String = String.Empty
        strSelect = "SELECT * FROM " & strTable _
                    & " WHERE AcctID=" & intAcct & " AND " & strIDfaField & "=" & intIDfa

        Dim qryNewID As New SqlCommand(strSelect, connSQLnewID)
        Dim myReader As SqlDataReader = qryNewID.ExecuteReader
        While myReader.Read
            If myReader.HasRows = True Then
                varResult = myReader(strIDnewField)
            End If
        End While
        qryNewID = Nothing
        myReader.Close() : myReader = Nothing
        connSQLnewID.Close() : connSQLnewID = Nothing
        Return varResult

    End Function

Open in new window

JMS1965Author Commented:
Your solution helped me clear a big hurdle in building my first web application. Thanks again!
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.