VB6 to Vb.net code conversion 1

[Bluejay07]

Hello,

I have an old piece of VB6 code that accesses SQL to determine the date format in order to provide consistency throughout the program regardess of how the computer date is formatted. Could anyone help with this conversion or provide an alternate method?

Public Function SQLServer_DateFormat(ByVal pValue As Object) As String

'// Returns the proper string Date format from a variant.
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim l_lngLangID As Long
Dim l_datValue As Date

If IsDate(pValue) Then
   l_datValue = CDate(pValue)

   '// Get the SQL Server default language.
   rs = pihCONN.Execute("sp_configure 'default language'")
   If Not (rs.BOF And rs.EOF) Then l_lngLangID = rs.Fields("config_value")
   rs.Close()
   rs = Nothing

   '// Get the date format used by SQL server.
   rs = pihCONN.Execute("sp_helplanguage")
   If Not (rs.BOF And rs.EOF) Then

      rs.MoveFirst()
      Do Until rs.EOF
         If rs.Fields("langid") = l_lngLangID Then
            Select Case rs.Fields("dateformat")
               Case "dmy"
               SQLServer_DateFormat = Day(l_datValue) & "/" & Month(l_datValue) & "/" & Year(l_datValue)

               Case "mdy"
               SQLServer_DateFormat = Month(l_datValue) & "/" & Day(l_datValue) & "/" & Year(l_datValue)

               Case "ymd"
               SQLServer_DateFormat = Year(l_datValue) & "/" & Month(l_datValue) & "/" & Day(l_datValue)

               Case Else
               SQLServer_DateFormat = CStr(pValue)
            End Select
            Exit Do
         End If
         rs.MoveNext()
      Loop

   Else
      SQLServer_DateFormat = CStr(pValue)
   End If   '// Not (rs.BOF And rs.EOF).
   rs.Close()
   rs = Nothing

End If   '// IsDate(pValue)
End Function

Thank you for your assistance.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ousoonerjoe]

Give the following a shot and let me know:

		SQLServer_DateFormat = StringVariable.ToString("MM/dd/yyyy")
		SQLServer_DateFormat = StringVariable.ToString("dd/MM/yyyy")
		SQLServer_DateFormat = StringVariable.ToString("yyyy/MM/dd")

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[Bluejay07]

Thanks for the response ousoonerjoe, however I am also looking on how to query a system stored procedure to obtain the values.

My program currently references a specific database's table, but not a system stored procedure.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[RiverGuy]

    Public Function SQLServer_DateFormat(ByVal pValue As Object) As String
        Dim s As String = ""


        '//Returns the proper string Date format from a variant.
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim dr As SqlClient.SqlDataReader
        'Didn't see where you declared your connection
        Dim pihCONN As New System.Data.SqlClient.SqlConnection("Some Connection String")
        pihCONN.Open()

        Dim l_lnbLangID As Long
        Dim l_datValue As DateTime
        If IsDate(pValue) Then
            l_datValue = CDate(pValue)

            '// Get the SQL Server default language.
            cmd.Connection = pihCONN
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "sp_helplanguage"
            dr = cmd.ExecuteReader
            While dr.Read
                If dr.Item("langid") = l_lnbLangID Then
                    Select Case dr.Item("dateformat")
                        Case "dmy"
                            s = DatePart(DateInterval.Day, l_datValue) & "/" & DatePart(DateInterval.Month, l_datValue) & "/" & DatePart(DateInterval.Year, l_datValue)
                        Case "mdy"
                            s = DatePart(DateInterval.Month, l_datValue) & "/" & DatePart(DateInterval.Day, l_datValue) & "/" & DatePart(DateInterval.Year, l_datValue)
                        Case "ymd"
                            s = DatePart(DateInterval.Year, l_datValue) & "/" & DatePart(DateInterval.Month, l_datValue) & "/" & DatePart(DateInterval.Day, l_datValue)
                        Case Else
                            s = CStr(pValue)
                    End Select
                    Exit While
                End If
            End While
            dr.Close()
            dr = Nothing
            pihCONN.Close()
            pihCONN = Nothing
            cmd = Nothing
        End If
        Return s
    End Function

 

[Bluejay07]

Thank you for your assistance RiverGuy.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[RiverGuy]

No problem