I'm trying to create a windows service that will check a Database and email information from it.

I've created the code in a project and it works great.
However the code doesnt work when in a service.

The Service works and runs. it can email. But only when i dont have anything todo with a database.

THe code is :
Dim ADconn As SqlConnection
Dim Sqlcmd AsString
Dim CmdSelect As SqlCommand
Dim dtrADlist As SqlDataReader
Dim m AsNew System.Web.Mail.MailMessage
Dim strBody AsString
Dim tmpbody AsString
Dim intbody AsInteger
Dim lefttime AsDate
Dim timenow AsDate
Dim overdue AsDate
With m
.From = "****.*******@*****.ac.uk"
.To = "****.*********@*****.uk"
.Subject = "Staff onsite with expired leaving times"
ADconn = New SqlConnection("Server=BURDOCK;UID=forweb;PWD=forwe b;Database=OutOfHrs")
Sqlcmd = "Select *from TimeRecords Where EstimateOUTdate=@date and EstimateOutTime<@time and closed is null"
CmdSelect = New SqlCommand(Sqlcmd, ADconn)
CmdSelect.Parameters.Add("@date", Microsoft.VisualBasic.Strings.Left(Now, 10))
CmdSelect.Parameters.Add("@time", Microsoft.VisualBasic.Strings.Right(DateTime.Now.A ddMinutes(-45), 8))
ADconn.Open()

dtrADlist = CmdSelect.ExecuteReader()
strBody = ""



*** If i Implment this while statment or any code that has anything to do with the databse table then noting happens.

While dtrADlist.Read()

lefttime = dtrADlist("estimateOutTime")
timenow = Microsoft.VisualBasic.Strings.Right(Now, 8)
Dim lngNumHour AsLong
Dim lngNumMinute AsLong
Dim lngNumseconds AsLong
Dim hours
Dim mins
Dim seconds
lngNumseconds = (DateDiff("S", timenow, lefttime))
hours = lngNumseconds \ 3600
mins = (lngNumseconds Mod 3600) \ 60
seconds = (lngNumseconds Mod 3600) Mod 60
overdue = (hours & ":" & mins & ":" & seconds)




* If i change .body to .Body= "hello" then it all works well.
* Have anything to witha databse field then get no email what so ever.


.Body = .Body + "<table width=""420"" border=""0"" <tr><td width=""120""><font face=""Microsoft Sans Serif"" Font size =""2"">" & _
"Name:</td><td colspan=""3""><font face=""Microsoft Sans Serif"" Font size =""2"">" & dtrADlist("username") & "</td></tr><tr>" & _
"<td><font face=""Microsoft Sans Serif"" Font size =""2"">Location</td><td colspan=""3""><font face=""Microsoft Sans Serif"" Font size =""2"">" & dtrADlist("location") & "</td></tr><tr>" & _
"<td><font face=""Microsoft Sans Serif"" Font size =""2"">Extension Number </td><td colspan=""3""><font face=""Microsoft Sans Serif"" Font size =""2"">" & dtrADlist("extension") & "</td></tr>" & _
"<td><font face=""Microsoft Sans Serif"" Font size =""2"">Mobile Number</td><td colspan=""3""><font face=""Microsoft Sans Serif"" Font size =""2"">" & dtrADlist("mobilephone") & "</td></tr><tr><td colspan=""4""></td>" & _
"</tr><td><font face=""Microsoft Sans Serif"" Font size =""2"">Should have left:</td><td width=""118""><font face=""Microsoft Sans Serif"" Font size =""2"">" & dtrADlist("EstimateOutTime") & "</td><td width=""93""><font face=""Microsoft Sans Serif"" Font size =""2"">Overdue by:</td><td width=""94""><font face=""Microsoft Sans Serif"" Font size =""2"" font color =""RED""><b>" & (overdue) & "</b></td>" & _
"</tr><td colspan=""4"">&nbsp;</td></tr></table><hr></font>"



tmpbody = .Body



EndWhile


.BodyFormat = MailFormat.Html


.Priority = MailPriority.High
EndWith
SmtpMail.SmtpServer = "smtp.********.*****.ac.uk"
If tmpbody = "" Then
Else
SmtpMail.Send(m)
EndIf

It seems to be that there is a problem with the data reader not being able to return information. This Service is being run on my own PC desktop. Ive tried telling the service run as if it were me but still no luck.

Please can you help

Ok I've worked out what is going on just not why.

If i run the code from a project exe on my PC the now date is returned in UK format. dd/mm/yyyy which good and what i want.

When i run the code a service it is US format ie mm/dd/yyyy

Why not use strongly typed data? Send the database a datetime variable instead of chopping up the date.


Or use DateTime.Now.ToString("dd/mm/yyyy")