VB.NET 2015 Create EXcel sheet from SQL Server 2014, TimeSpan conversion error

[DougP]

I tried a couple of things such as dr(dc.ColumnName.ToString) and CSTR(dr(dc.ColumnName)) but neither work. what do I need to do?
I am currently trying to get the data type but no luck.
Error message on line in red below:
An unhandled exception of type 'System.ArgumentException' occurred in mscorlib.dll
Additional information: Method's type signature is not Interop compatible.
the column is a "TimeSpan" datatype whenits in VB.NET and in SQL its "TIME" datatype. Changing it is not an option.
--

Dim oXL As Excel.Application = CType(CreateObject("Excel.Application"), Excel.Application)
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range
        oXL.Visible = True

        oWB = oXL.Workbooks.Add
        oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)

        Dim dc As DataColumn
        Dim dr As DataRow
        Dim CellData As String
        ' Dim dt As DataTable
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            oXL.Cells(1, colIndex) = dc.ColumnName
        Next
        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1

                If IsDBNull(dr(dc.ColumnName)) Then
                    'do nothing
                Else
                    'Select Case dr(dc.ColumnName.GetTypeCode)
                    '    Case "Chars"

                    'End Select
                    oXL.Cells(rowIndex + 1, colIndex) = [COLOR=red]dr(dc.ColumnName)[/color] '<<<<<<<<<<< error here

                End If
            Next
        Next

TIA

DougP

 

[SkipVought]

Please post an example of the source data and the resulting data in your Excel sheet.

I believe that the data type is related to the COLUMN, not the row of the column!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DougP]

Sorry I found a solution on another forum. I am using a SQL statement. So I saw I could just change the datatype in the SQL as shown below.

SELECT [fkScenarioID]       ,[Calc_Date]       ,[COLOR=blue] CAST([Calc_Time] as varchar(12))[/color]
                                          ,[Calc_Close]      ,[Calc_Volume]      ,[NoSh]      ,[BS_Price]      ,[BS_Signal]
                                          ,[RGB1]      ,[RGB2]      ,[RGB3]      ,[RGB4]      ,[RGB5]      ,[Stop_Price]
                                          ,[Limit_Price]      ,[Calc_STS]      ,[Count_Bs]      ,[Count_Ss]      ,[Count_Us]
                                          ,[Count_Ds]      ,[Dir1]      ,[Dir2]      ,[Dir3]      ,[Dir4]
                                          ,[Dir5]
                                          ,[pkRecordID]
                                      FROM [dbo].[Trade_Calcs]  Where [fkScenarioID] = 'L003'

DougP