Solved

Convert function from vb6 to vb.net

Posted on 2007-04-02
7
670 Views
Last Modified: 2012-06-27
I used this routine in vb6 to capture any null values and return a valid I could do this with.  I would like to convert this to vb.net but doesnt seem to be taken very well.  I was running this in a bas module and would like to do the same tyoe thing.



Global Const ZERODATE As Date = "1/1/1900"

'Enumerated type to define the data types accepted by CDBNull
Public Enum DBType
    dbText = 0
    dbNumber = 1
    dbDate = 2
    dbBool = 3
End Enum

Public Function CDBNull(varInputValue As Variant, Optional intType As DBType = dbText) As Variant

    On Error Resume Next
    If (IsNull(varInputValue)) Then
        Select Case intType
            Case dbText
                CDBNull = ""
            Case dbNumber
                CDBNull = 0
            Case dbDate
                CDBNull = ZERODATE
            Case dbBool
                CDBNull = False
            Case Else
                CDBNull = ""
        End Select
    Else
        Select Case intType
            Case dbText
                CDBNull = CStr(varInputValue)
            Case dbNumber
                If (IsNumeric(varInputValue)) Then
                    CDBNull = CCur(varInputValue)
                Else
                    CDBNull = "0"
                End If
            Case dbDate
                If (IsDate(varInputValue)) Then
                    CDBNull = CDate(varInputValue)
                Else
                    CDBNull = ZERODATE
                End If
            Case dbBool
                CDBNull = CBool(varInputValue)
            Case Else
                CDBNull = CStr(varInputValue)
        End Select
    End If
End Function
0
Comment
Question by:mgmhicks
  • 4
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18837695
you have to replace VARIANT by OBJECT
you have to check for null like this:
    If varInputValue == DBNull.Value Then


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18837705
optional parameters is no longer possible in vb.net, but function overloading:

Public Function CDBNull(varInputValue As object ) As Variant
  return CDBNull(varInputValue, dbText)
End function

Public Function CDBNull(varInputValue As object, Optional intType As DBType ) As object
  ...
End Function
0
 

Author Comment

by:mgmhicks
ID: 18838158
Ok,  not sure things arent working at all.  Can we start with the enum statement.  Is that ok, and then here's what the code is looking like now.  Its having a issue with the inttypes.

Public Function CDBNull(ByVal varInputValue As Object) As Object
        Return CDBNull(varInputValue, dbText)


        On Error Resume Next
        If varInputValue = DBNull.Value Then
            Select Case intType
                Case Text
                    CDBNull = ""
                Case Number
                    CDBNull = 0
                Case dbDate
                    CDBNull = ZERODATE
                Case dbBool
                    CDBNull = False
                Case Else
                    CDBNull = ""
            End Select
        Else
            Select Case intType
                Case dbText
                    CDBNull = CStr(varInputValue)
                Case dbNumber
                    If (IsNumeric(varInputValue)) Then
                        CDBNull = CCur(varInputValue)
                    Else
                        CDBNull = "0"
                    End If
                Case dbDate
                    If (IsDate(varInputValue)) Then
                        CDBNull = CDate(varInputValue)
                    Else
                        CDBNull = ZERODATE
                    End If
                Case dbBool
                    CDBNull = CBool(varInputValue)
                Case Else
                    CDBNull = CStr(varInputValue)
            End Select
        End If
    End Function

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18838690

Public Function CDBNull(ByVal varInputValue As Object) As Object
        Return CDBNull(varInputValue, dbText)
End Function

Public Function CDBNull(ByVal varInputValue As Object, inttypes as DBType  ) As Object
        On Error Resume Next
        If varInputValue = DBNull.Value Then
            Select Case intType
                Case Text
                    CDBNull = ""
                Case Number
                    CDBNull = 0
                Case dbDate
                    CDBNull = ZERODATE
                Case dbBool
                    CDBNull = False
                Case Else
                    CDBNull = ""
            End Select
        Else
            Select Case intType
                Case dbText
                    CDBNull = varInputValue.ToString()
                Case dbNumber
                    If (Int32.TryParse(varInputValue)) Then
                        CDBNull = Int32.Parse(varInputValue)
                    Else
                        CDBNull = 0
                    End If
                Case dbDate
                    Try
                        CDBNull = DateTime.Parse(varInputValue)
                    Catch ex as Exception
                        CDBNull = ZERODATE
                    End Try
                Case dbBool
                    CDBNull = varInputValue
                Case Else
                    CDBNull = varInputValue.ToString
            End Select
        End If
    End Function
0
 
LVL 3

Accepted Solution

by:
r1937 earned 250 total points
ID: 18838772
   Public Const ZERODATE As Date = #1/1/1900#

    'Enumerated type to define the data types accepted by CDBNull
    Public Enum DBType
        dbText = 0
        dbNumber = 1
        dbDate = 2
        dbBool = 3
    End Enum

    Public Function CDBNull(ByVal varInputValue As Object, Optional ByVal intType As DBType = DBType.dbText) As Object

        On Error Resume Next
        If Not varInputValue Is Nothing Then
            Select Case intType
                Case DBType.dbText
                    CDBNull = ""
                Case DBType.dbNumber
                    CDBNull = 0
                Case DBType.dbDate
                    CDBNull = ZERODATE
                Case DBType.dbBool
                    CDBNull = False
                Case Else
                    CDBNull = ""
            End Select
        Else
            Select Case intType
                Case DBType.dbText
                    CDBNull = CStr(varInputValue)
                Case DBType.dbNumber
                    If (IsNumeric(varInputValue)) Then
                        CDBNull = CDec(varInputValue)
                    Else
                        CDBNull = "0"
                    End If
                Case DBType.dbDate
                    If (IsDate(varInputValue)) Then
                        CDBNull = CDate(varInputValue)
                    Else
                        CDBNull = ZERODATE
                    End If
                Case DBType.dbBool
                    CDBNull = CBool(varInputValue)
                Case Else
                    CDBNull = CStr(varInputValue)
            End Select
        End If
    End Function

--------------------------------------------------------------------------
Things to say:
Currency data type is not supported. Double is used instead.
Though 'on error' statement is still supported Try-Catch-Finally is preffered.
Late Binding is not a good practice. In this case Function Overloading is suggested.

0
 
LVL 3

Expert Comment

by:r1937
ID: 18838793
Sorry Decimal is used instead of Currency.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18838807
>Late Binding is not a good practice. In this case Function Overloading is suggested.

I have to agree 100% with that!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question