INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.
--

CODE

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) = dr(dc.ColumnName) '<<<<<<<<<<< error here

                End If
            Next
        Next 

TIA

DougP

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.
[code]
SELECT [fkScenarioID] ,[Calc_Date] , CAST([Calc_Time] as varchar(12))
,[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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close