I produced a console application that emails html reports.
The problem I am having is that when it is run on my client which is a windows 2000 professional it runs fine. When i run it on the windows 2003 server i get exclamation points
sporadically through out the html.
example
I used single quotes because i produce my html email by putting the html into a string such as
Can anyone tell me what i should be looking for.
code that was used. I hope this helps.
Thanks
The problem I am having is that when it is run on my client which is a windows 2000 professional it runs fine. When i run it on the windows 2003 server i get exclamation points
sporadically through out the html.
example
HTML:
<td nowrap align='center' width='138'><font face='Arial'><b>DUE DATE</font></B></td><td nowrap align='left' widt!
h='138'>
VB.NET:
strHTML = strHTML & "<tr><td bgcolor='#FFFFFF' align='center' nowrap width='76' height='0'><font face='Arial' size='2'>" & strEmpid & "</font>"
Can anyone tell me what i should be looking for.
code that was used. I hope this helps.
VB.NET:
Imports System.Web.Mail
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim conn As New SqlConnection("data source=someserver;initial catalog=somedb;user id=someuserid;password=somepassword;")
Dim daSect As New SqlDataAdapter("SELECT sect FROM profile UNION SELECT sect FROM sub_sites", conn)
Dim dsSect As New DataSet
Dim myRowSect As DataRow
Dim strEmailAddress As String
daSect.Fill(dsSect, "profile")
For Each myRowSect In dsSect.Tables("profile").Rows
' get the section numbers
Dim strSect As String
Dim strHTML As String
strEmailAddress = "" ' clear it
strSect = (myRowSect("sect"))
'get section name
Dim daSecName As New SqlDataAdapter
Dim dsSecName As New DataSet
Dim cmdSecName As SqlCommand
Dim dueSecName As DataRow
Dim strSecName As String
cmdSecName = New SqlCommand("select org_name from new_organization where org_id =@ThisSect", conn)
cmdSecName.Parameters.Add("@ThisSect", SqlDbType.VarChar, 4)
cmdSecName.Parameters("@ThisSect").Value = strSect
daSecName.SelectCommand = cmdSecName
daSecName.Fill(dsSecName, "OrgTable")
For Each dueSecName In dsSecName.Tables("OrgTable").Rows
If Not (dsSecName Is Nothing) AndAlso dsSecName.Tables.Count > 0 AndAlso dsSecName.Tables(0).Rows.Count > 0 Then
strSecName = dueSecName("org_name")
' strSecName = "DUE EVALUATIONS For " & dueSecName("org_name")
Else
' strSecName = "DUE EVALUATIONS "
End If
Next
'end of get section name
Dim daMap As New SqlDataAdapter
Dim dsMap As New DataSet
Dim cmdMap As SqlCommand
Dim dueMap As DataRow
' cmdMap = New SqlCommand("select e.sect,e.deptno,e.empid,e.lname,e.fname,e.item,e.pay_location,e.job_code,e.job_desc_abbr,d.ann_due_date,d.eval_type,d.fromdate,d.todate,d.datetobefiled,d.original_pl,d.due_evals_id from due_evals d,emp_information_test e where e.empid = d.empid and payloc_comp_date is NULL and datetobefiled between @startDate and @endDate and e.sect = @sect order by d.eval_type,e.sect,d.datetobefiled,d.empid", conn)
cmdMap = New SqlCommand("select email from dpss_emp_email d,profile p,emp_information_test e where d.employee_number = p.empid And p.sect = @mapSect and p.empid = e.empid and e.item in (select item from map_items where item not in (8008,8013,8018,8019))", conn)
cmdMap.Parameters.Add("@mapSect", SqlDbType.VarChar, 6)
cmdMap.Parameters("@mapSect").Value = strSect
daMap.SelectCommand = cmdMap
daMap.Fill(dsMap, "MapTable")
If Not (dsMap Is Nothing) AndAlso dsMap.Tables.Count > 0 AndAlso dsMap.Tables(0).Rows.Count > 0 Then
For Each dueMap In dsMap.Tables("MapTable").Rows
strEmailAddress = strEmailAddress & (dueMap("email") & ",")
Next
Else
'GET empid information for each section
' Dim eCommand As New SqlCommand("select email from dpss_emp_email d,profile p where d.employee_number = p.empid and sect = @sect", conn)
Dim eCommand As New SqlCommand("select email from dpss_emp_email d,profile p,emp_information_test e where d.employee_number = p.empid and p.empid = e.empid and e.sect = @sect and e.item not in (8008,8013,8018,8019)", conn)
Dim eParm As New SqlParameter
eParm = eCommand.Parameters.Add(New SqlParameter("@sect", SqlDbType.VarChar, 6))
eParm.Value = strSect
conn.Open()
Dim myRead As SqlDataReader
myRead = eCommand.ExecuteReader()
While myRead.Read
strEmailAddress = strEmailAddress & (myRead.Item("email") & ",")
End While
myRead.Close()
conn.Close()
End If
If strEmailAddress = "" Then
Dim sCommand As New SqlCommand("select distinct email from dpss_emp_email d,sub_sites p,emp_information_test e where d.employee_number = p.empid And p.sect = @smapSect and p.empid = e.empid and e.item in (select item from map_items where item not in (8008,8013,8018,8019))", conn)
Dim sParm As New SqlParameter
sParm = sCommand.Parameters.Add("@smapSect", SqlDbType.VarChar, 6)
sParm.Value = strSect
conn.Open()
Dim mysRead As SqlDataReader
mysRead = sCommand.ExecuteReader()
While mysRead.Read
strEmailAddress = strEmailAddress & (mysRead.Item("email") & ",")
End While
mysRead.Close()
conn.Close()
End If
Dim daDue As New SqlDataAdapter
Dim dsDue As New DataSet
Dim cmd As SqlCommand
Dim dueRow As DataRow
Dim strStartDate As Date
Dim strEndDate As Date
cmd = New SqlCommand("select e.sect,e.deptno,e.empid,e.lname,e.fname,e.item,e.pay_location,e.job_code,e.job_desc_abbr,d.ann_due_date,d.eval_type,d.fromdate,d.todate,d.datetobefiled,d.original_pl,d.due_evals_id from due_evals d,emp_information_test e where e.empid = d.empid and payloc_comp_date is NULL and datetobefiled between @startDate and @endDate and e.sect = @sect order by d.eval_type,e.sect,d.datetobefiled,d.empid", conn)
cmd.Parameters.Add("@sect", SqlDbType.VarChar, 6)
cmd.Parameters.Add("@startDate", SqlDbType.SmallDateTime, 10)
cmd.Parameters.Add("@endDate", SqlDbType.SmallDateTime, 10)
Dim dtNow As Date
Dim strMonth As String
dtNow = Now
' dtNow = CDate("06/15/2004")
'strStartDate = DateSerial(Year(dtNow), Month(dtNow), 1) 'get start of this month
strStartDate = CDate("09/01/2000") 'get from the beginning to include deliquent
strEndDate = DateSerial(Year(dtNow), Month(dtNow) + 1, 0)
strMonth = Month(dtNow)
'strStartDate = CDate("09/01/2004")
'strEndDate = CDate("12/30/2004")
cmd.Parameters("@sect").Value = strSect
cmd.Parameters("@startDate").Value = strStartDate
cmd.Parameters("@endDate").Value = strEndDate
daDue.SelectCommand = cmd
daDue.Fill(dsDue, "DueTable")
If Not (dsDue Is Nothing) AndAlso dsDue.Tables.Count > 0 AndAlso dsDue.Tables(0).Rows.Count > 0 Then
' got data
strHTML = strHTML & "<html><head><title>Due Evaluatlions </title></head><body>"
strHTML = strHTML & "<table cellpadding='0' width='700' height='0' border='0' style='border: 1 solid #C0C0C0'>"
strHTML = strHTML & "<tr bgcolor='#6B8AC6'><td nowrap align='center' height='20' colspan='5'><p align='center'><font color='white' face='Arial'><b>" & strSecName & "</b></font></p></td></tr>"
strHTML = strHTML & "<tr bgcolor='#F0F0F0'><td nowrap align='center' height='20' colspan='5'><p align='center'><font face='Arial' ><b>Due Evaluations thru " & strEndDate & "</b></font></p></td></tr>"
strHTML = strHTML & "<tr bgcolor='#6B8AC6'><td nowrap align='center' height='20' colspan='5'> </td></tr>"
strHTML = strHTML & "<tr bgcolor='#F0F0F0'><td nowrap align='center' width='76'><b><font face='Arial'>EMP ID</font></b></td>"
strHTML = strHTML & "<td nowrap align='left' width='150'><B><font face='Arial'>EMPLOYEE NAME</font></B></td>"
strHTML = strHTML & "<td nowrap align='center'><font face='Arial'><b>EVAL TYPE</b></font></td>"
strHTML = strHTML & "<td nowrap align='center' width='138'><font face='Arial'><b>DUE DATE</font></B></td>"
strHTML = strHTML & "<td nowrap align='left' width='138'><font face='Arial'><b>EVAL PERIOD</B></font></td></tr>"
For Each dueRow In dsDue.Tables("DueTable").Rows
Dim strEmpid As String
Dim strFname As String
Dim strLname As String
Dim strEType As String
Dim strDatetobefiled As String
Dim strFromDt As Date
Dim strToDt As Date
strEmpid = (dueRow("empid") & " ")
strFname = (dueRow("fname") & " ")
strLname = (dueRow("lname") & " ")
strEType = (dueRow("eval_type") & " ")
strDatetobefiled = (dueRow("datetobefiled") & " ")
strFromDt = (dueRow("fromdate") & " ")
strToDt = (dueRow("todate") & " ")
strHTML = strHTML & "<tr><td bgcolor='#FFFFFF' align='center' nowrap width='76' height='0'><font face='Arial' size='2'>" & strEmpid & "</font>"
strHTML = strHTML & "</td><td bgcolor='#FFFFFF' align='Left' nowrap width='137' height='0'><font face='Arial' size='2'>" & strFname & strLname & "</font>"
strHTML = strHTML & "</td><td bgcolor='#FFFFFF' align='center' nowrap height='0'><font face='Arial' size='2'>" & strEType & "</font>"
strHTML = strHTML & "</td><td bgcolor='#FFFFFF' align='center' nowrap width='138' height='0'><font face='Arial' size='2'>" & strDatetobefiled & "</font>"
strHTML = strHTML & "</td><td bgcolor='#FFFFFF' align='LEFT' nowrap width='138' height='10' ><font face='Arial' size='2'>" & strFromDt & "-" & strToDt & "</font>"
strHTML = strHTML & "</td></tr>"
Next
strHTML = strHTML & "<tr bgcolor='#6B8AC6'><td nowrap height='20' colspan='5'> </td></tr>"
strHTML = strHTML & "<tr><td nowrap align='left' height='20' colspan='5' bgcolor='#FCFCFC'><BR> "
strHTML = strHTML & "<font face='Arial'>Thank you for the opportunity to serve you.</font><br> </td></tr>"
strHTML = strHTML & "</table></body></html>"
'change
' email.to to strEmailAddress
' email_Subject to " Evaluation Due"
If strEmailAddress <> "" Then
Dim email As New System.Web.Mail.MailMessage
email.To = "[EMAIL="someemail@aol.org"]someemail@aol.org[/EMAIL]"
email.From = "[EMAIL="myEmail@aol.org"]myEmail@aol.org[/EMAIL]"
email.Body = strHTML
'email.Subject = " Evaluations Due"
email.Subject = " Evaluations Due (" & strSect & ") " & strSecName & strEmailAddress
email.BodyFormat = Web.Mail.MailFormat.Html
System.Web.Mail.SmtpMail.SmtpServer = "mysmtp"
System.Web.Mail.SmtpMail.Send(email)
email = Nothing
End If
End If
strHTML = "" 'clear it
Next
End Sub
End Module
Thanks
Last edited by a moderator: