Automatic Email from SQL data

[Okano666]

Hello World


Im trying to send an automatic email every morning to people from data stored on our SQL server, now this was working fine but then i was asked to send a Start Time as well as an End time on the data and im having trouble sending the data; the error i am getting is

Operator '&' is not defined for string "" and type 'TimeSpan'.

on my lines:

         Dim oWord As Word.Application        Dim oDoc As Word.Document


        oWord = CreateObject("Word.Application")
        oDoc = oWord.Documents.Add("\\datastore\database\Templates\engTaskReport.dot")


        'Populate all the bookmarks
        oDoc.Bookmarks("DATE").Range.Text = startDate.ToLongDateString


        'Insert Info
        Dim tbl As Word.Table = oDoc.Tables(1)
        Dim wordRow = 2



 For Each row As DataRow In dailyReportDataSet.Tables(0).Rows
            If (wordRow - 1) < dailyreportDataSet.Tables(0).Rows.Count Then
                tbl.Rows.Add(BeforeRow:=tbl.Rows(wordRow))
            End If


            tbl.Cell(wordRow, 1).Range.Text = "" & row.Item("Engineer") & "" + vbCrLf
            tbl.Cell(wordRow, 2).Range.Text = "" & row.Item("Assisted") & "" + vbCrLf
            tbl.Cell(wordRow, 3).Range.Text = "" & row.Item("Line") & "" + vbCrLf
            tbl.Cell(wordRow, 4).Range.Text = "" & row.Item("Machine") & "" + vbCrLf
[B][COLOR=#ff0000]            tbl.Cell(wordRow, 5).Range.Text = "" & row.Item("Start Time") & "" + vbCrLf[/COLOR][/B]
[B][COLOR=#ff0000]            tbl.Cell(wordRow, 6).Range.Text = "" & row.Item("End Time") & "" + vbCrLf[/COLOR][/B]
            tbl.Cell(wordRow, 7).Range.Text = "" & row.Item("Time Taken (hrs)") & "" + vbCrLf
            tbl.Cell(wordRow, 8).Range.Text = "" & row.Item("Root Cause") & "" + vbCrLf
            tbl.Cell(wordRow, 9).Range.Text = "" & row.Item("Additional Info") & "" + vbCrLf
        Next

Start Time and End Time are stored as Time() on the SQL,

Is this because the type of data is Time() and not a nvarchar or float? and they cant be converted to a string?

An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

Any help would be much appreciated!

Thankyou

 

[jmcilhinney]

As the error message suggests, the data comes from the database as a TimeSpan. You can't implicitly concatenate a TimeSpan and a String. Every object has a ToString method, so you can convert anything to a String. Get rid of those pointless empty Strings too.

 

[Okano666]

Hi mate thanks for your reply, i managed to fix it by converting the Time - which it was stored on the SQL server as, to a varchar by using the CONVERT function.

CONVERT(varchar(10),START_TIME, 20) As [Start Time], CONVERT(varchar(10),END_TIME, 20) As [End Time],

:very_drunk: