Formatting Date Column form Excel Data - New to this and lost 1

[lameid]

Below is a snippet with a simplified version of what is giving me trouble... The issue is that the date data is coming across formatted incorrectly.

I realize that I really need to make the PL/SQL take parameters and pass them but I am just trying to make it work first before I go bone up on PL/SQL as I am a T-SQL and VBA programmer first. So this foray into VB.net is a bit bumpy for me. I am lost trying to understand if the Excel data is coming across as a date or it is not and my attempts to format it are foolish. I did not see a way to determine the datatype of the column in a dataset. That's pretty much it, there are too many things I don't understand yet to figure out how to troubleshoot or fix this. Writing this, it occurs to me I could probably just adjust the format on the SQL side but I'd like to understand what fundamental I am missing other than Excel data is not strongly typed so many experiences are painful.

    Dim ds As DataSet = OExcelHandler.GetDataFromExcel(Session("Fullname"), lblStatus, Session("FileType"))

    If ds IsNot Nothing Then
        For Each dr As DataRow In ds.Tables(0).Rows
            'There is an issue with the formatting of dr.Item("Request Date")... it is using a Euro dd/mm/yyyy format using tostring instead of the desired mm/dd/yyyy.
            strSQL = "INSERT INTO SomeTable (Somefield)" & _
                            " VALUES (" & "to_date('" & [red]dr.Item("Request Date").Tostring()[/red] & "', 'mm/dd/yyyy') " & _
                                      ") "
         Next
    End if

And the class I cobbled together from a few sites...

Public Class ExcelHandler

    ' Return data in dataset from excel file. '   
    Public Function GetDataFromExcel(ByVal a_sFilepath As String, lbl As Label, strExtenstion As String) As DataSet

        Dim ds As New DataSet()
        Dim cn As OleDbConnection
        Select Case strExtenstion
            Case ".xls"
                cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & a_sFilepath & ";Extended Properties= Excel 8.0")
            Case ".xlsx"
                cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & a_sFilepath & ";Extended Properties= Excel 8.0")
        End Select

        Try
            cn.Open()
        Catch ex As OleDbException
            Console.WriteLine(ex.Message)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        ' It Represents Excel data table Schema.'
        Dim dt As New System.Data.DataTable()
        dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        If dt IsNot Nothing OrElse dt.Rows.Count > 0 Then
            For sheet_count As Integer = 0 To dt.Rows.Count - 1
                Try
                    ' Create Query to get Data from sheet. '
                    Dim sheetname As String = dt.Rows(sheet_count)("table_name").ToString()
                    Dim da As New OleDbDataAdapter("SELECT * FROM [" & sheetname & "]", cn)
                    da.Fill(ds, sheetname)
                Catch ex As DataException
                    lbl.Text = ex.Message
                Catch ex As Exception
                    lbl.Text = ex.Message
                End Try
            Next
        End If
        cn.Close()
        Return ds
    End Function
End Class

 

[jebenson]

Try using VB's Format function:

Dim ReqDate As String

For Each dr As DataRow In ds.Tables(0).Rows
    
    ReqDate = dr.Item("Request Date")
    ReqDate = Format("ReqDate, "MM/dd/yyyy")  'note: MM is month with leading 0 if needed; mm is minutes

    strSQL = "INSERT INTO SomeTable (Somefield)" & _
    " VALUES (" & "to_date('" & ReqDate & "', 'mm/dd/yyyy') " & _
                                      ") "
Next

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[lameid]

I see. That should work assuming it is a date which I suspect it is. My mind was trying to make everything a method off the base type since my earliest playing overlooked base type methods so I was lost in the weeds. Good comment on "mm" being minutes since I am coming from the VBA world where mm is month and nn is minutes. Reminds me of the time I got lost with DateAdd in TSQL because it is the same as Jet/ACE SQL EXCEPT that it takes some sort of constant parameters instead of format strings.

Now to figure out why the whole solution quit building before I can test... It's related to the legacy Oracle use for sure. I still haven't figured out the resistance to ODAP.Net but that's a problem for another day.