I have a program that auto launches with Windows via scheduler. What it does is runs a query and then emails the results of the query. This all works. What i'd like to do is take the program to the next level. We have 10 locations. The location DM should receive this report daily (only receiving their store). So basically what I'd like to do is repeat the code in the form of a different tableadapter and email that information. My C# code is:
Imports System.Net.Mail
Imports System.Linq
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
Dim SmtpServer As New SmtpClient()
Dim mail As New MailMessage()
Me.Paid_Out_TbTableAdapter.Fill(Me.DataSet.Paid_Out_Tb)
Dim payouts = _
<html>
<body>
<table border="1">
<tr><th>Store #</th><th>Date</th><th>Amount</th><th>User</th><th>Comment</th></tr>
<%= From paidOut In Me.DataSet.Paid_Out_Tb.AsEnumerable _
Select <tr><td><%= paidOut.Store_Id %></td>
<td><%= Convert.ToDateTime(paidOut.Paid_Out_Datetime).ToString("M/d/yy") %>
</td><td><%= "$" & paidOut.Paid_Out_Amount.ToString("0.00") %></td>
<td><%= paidOut.Update_UserName %></td>
<td><%= paidOut.Paid_Out_Comment %></td></tr> %>
</table>
</body>
</html>
If (Me.DataSet.Paid_Out_Tb.Count = 0) Then 'This cheks to see if the dataset is Null. We do not want to email if the set is Null
Me.Close()
Else
SmtpServer.Credentials = New _
Net.NetworkCredential("*****", "****") 'Assign the network credentials
SmtpServer.Port = 25 'Assign the SMTP Port
SmtpServer.Host = "10.0.*.*" 'Assign the Server IP
mail = New MailMessage() 'Starts a mail message
mail.From = New MailAddress("***@***.com") 'Sets the "FROM" address
mail.To.Add("****@****.com") 'Sets the "To" address
'mail.CC.Add("****@****.com") 'set this if you would like to CC
mail.Subject = "Paid Out Report for 1929"
mail.IsBodyHtml = True
mail.Body = payouts.ToString()
SmtpServer.Send(mail)
'MsgBox("mail send")
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
My Query is:
SELECT Store_Id, Paid_Out_Amount, Paid_Out_Comment, Paid_Out_Datetime, Update_UserName, Till_Number
FROM Paid_Out_Tb
WHERE (Store_Id = 1929) AND (Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0,
GETDATE()), 0)) AND (Paid_Out_Amount > 20) OR
(Store_Id = 1929) AND (Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0,
GETDATE()), 0)) AND (Paid_Out_Comment LIKE N'%' + 'Filter' + '%')
Again, this all works. But my second query will be exactly the same except I will substitue "112" for the store_ID. I will then need to email that query result to a DIFFERENT address than the 1929 id... Any suggestions on how best to accomplish this?
Imports System.Net.Mail
Imports System.Linq
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'DataSet.Paid_Out_Tb' table. You can move, or remove it, as needed.
Try
Dim SmtpServer As New SmtpClient()
Dim mail As New MailMessage()
Me.Paid_Out_TbTableAdapter.Fill(Me.DataSet.Paid_Out_Tb)
Me.DataTable1TableAdapter.Fill(Me.DataSet.DataTable1)
Dim payouts = _
<html>
<body>
<table border="1">
<tr><th>Store #</th><th>Date</th><th>Amount</th><th>User</th><th>Comment</th></tr>
<%= From paidOut In Me.DataSet.Paid_Out_Tb.AsEnumerable _
Select <tr><td><%= paidOut.Store_Id %></td>
<td><%= Convert.ToDateTime(paidOut.Paid_Out_Datetime).ToString("M/d/yy") %>
</td><td><%= "$" & paidOut.Paid_Out_Amount.ToString("0.00") %></td>
<td><%= paidOut.Update_UserName %></td>
<td><%= paidOut.Paid_Out_Comment %></td></tr> %>
</table>
</body>
</html>
Dim Payouts453 = _
<html>
<body>
<table border="1">
<tr><th>Store #</th><th>Date</th><th>Amount</th><th>User</th><th>Comment</th></tr>
<%= From paidOut In Me.DataSet.DataTable1.AsEnumerable _
Select <tr><td><%= paidOut.Store_Id %></td>
<td><%= Convert.ToDateTime(paidOut.Paid_Out_Datetime).ToString("M/d/yy") %>
</td><td><%= "$" & paidOut.Paid_Out_Amount.ToString("0.00") %></td>
<td><%= paidOut.Update_UserName %></td>
<td><%= paidOut.Paid_Out_Comment %></td></tr> %>
</table>
</body>
</html>
If (Me.DataSet.Paid_Out_Tb.Count = 0) Then 'This cheks to see if the dataset is Null. We do not want to email if the set is Null
Me.Close()
Else
SmtpServer.Credentials = New _
Net.NetworkCredential("****", "****") 'Assign the network credentials
SmtpServer.Port = 25 'Assign the SMTP Port
SmtpServer.Host = "10.0.*.**" 'Assign the Server IP
mail = New MailMessage() 'Starts a mail message
mail.From = New MailAddress("*@**.com") 'Sets the "FROM" address
mail.To.Add("**@**.com") 'Sets the "To" address
'mail.CC.Add("**@**.com") 'set this if you would like to CC
mail.Subject = "Paid Out Report for 1929"
mail.IsBodyHtml = True
mail.Body = payouts.ToString() & Payouts453.ToString() 'this is to add another chart You would use a seperate dataset obviously
SmtpServer.Send(mail)
'MsgBox("mail send")
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Me.Close() 'Closes the program when it's finished.
End Sub
The easiest way to accomplish this, is to add another tableadapter and run a seperate query... It's probably not the most efficient, but for the amount of data being transmitted... it works..