locked
I need to loop through email addresses in table in the SQL Database and send all an email RRS feed

  • Question

  • User-164116809 posted

    Hi All,
     
    I was wondering if someone could help me. I've got a simple Suppliers table that has the Email Addresses for my Suppliers.
    I need to loop through the email addresses 'SuppEmail' in the Suppliers table in the SQL Database 'SpecCars' and send them all the email below.
    I've been at it for a few days now, looking on-line and trying many different variations, but no matter what I do, it only sends one email
    to the first entry 'frontdesk@jacksauto.com.au' in the table and that's it. I don't think my loop is correct, I'm putting the SuppEmail into
    the variable emailnew, but it doesn't seem to work. If you could help, that would be fantastic. It's an ASP.NET C# Solution.
     
     // This is the Suppliers Table, it just has two record in there:
     
    ..................................................

    use SpecCars 
    Go 
    
    CREATE table Suppliers(
    	SuppId INT IDENTITY(1,1) PRIMARY KEY,
    	SuppName NVARCHAR(60) NOT NULL, 
            SuppAddress NVARCHAR(150) NOT NULL, 
    	SuppSuburb NVARCHAR(60) NOT NULL, 
            SuppState NVARCHAR(30) NOT NULL, 
    	SuppPost NVARCHAR(10) NOT NULL,
    	SuppPhone NVARCHAR(10) NOT NULL,
    	SuppEmail NVARCHAR(100) NOT NULL,
    	SuppCode NVARCHAR(10) NOT NULL
    )
    Go
    
    Command(s) completed successfully.
    
    
    Insert into Suppliers (SuppName, SuppAddress, SuppSuburb, SuppState, SuppPost, SuppPhone, SuppEmail, SuppCode) values ('Jacks Auto', '2 Jill Street', 'Belgrade', 'VIC', '3299', '9555 4457', 'frontdesk@jacksauto.com.au', 'JACBLA')
    Insert into Suppliers (SuppName, SuppAddress, SuppSuburb, SuppState, SuppPost, SuppPhone, SuppEmail, SuppCode) values ('Ultimate Lights', '205 Browns Road', 'Tullamarine', 'VIC', '3011', '9877 2255', 'orders@ultimatlights.com.au', 'ULTTUL') 
    
    (2 row(s) affected)

    ..................................................

     //This is the code snippet :
     
          SqlDataReader sqlData;
          SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=SpecCars;Integrated Security=True");
    
          connection.Open();
          sqlData = new SqlCommand("Select SuppEmail From Suppliers", connection).ExecuteReader();
    
          int count = sqlData.FieldCount;
          while (sqlData.Read())
          {
             for (int i = 0; i < count; i++)
            {
              string emailnew = sqlData[i].ToString();
    
                MailMessage mailMessage = new MailMessage();
    
                mailMessage.From = new MailAddress("myemail.com");
                mailMessage.To.Add("myemail.com");
                //mailMessage.To.Add(emailnew);
                mailMessage.Cc.Add(emailnew);
                mailMessage.Subject = "Assembly Line Stop";
                mailMessage.Priority = MailPriority.High;
    
                mailMessage.Body = "Please be advised that the assembly line at Specialised Cars has STOPPED. You will be notified once the line has started again. Any Services between the LINE STOP and the LINE START will be carried out after 19:00 (7pm).";
                mailMessage.IsBodyHtml = true;
    
                SmtpClient smtpClient = new SmtpClient("smtp-mail.myprovider.com", 587);
                smtpClient.EnableSsl = true;
                smtpClient.Credentials = new System.Net.NetworkCredential("myemail.com", "password");
                smtpClient.Send(mailMessage);
            }
          }
            connection.Close();
    
    

    ..................................................

    Tuesday, March 29, 2016 9:57 AM

Answers

  • User-164116809 posted
    //-----------------
    
    //Hi All, just a quick update:
    
    
    //Trying8.aspx - Finally Works, it sends LINE STOP Email to every SuppEmail recipient in Suppliers Table
    
    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Trying8.aspx.cs" Inherits="SpecCars.Admin.Trying8" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
          <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
          <br />
          <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        </div>
        </form>
    </body>
    </html>
    
    //-----------------
    
    //Trying8.aspx.cs - Finally Works, it sends LINE STOP Email to every SuppEmail recipient in Suppliers Table
    
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data.SqlClient;
    using System.Net;
    using System.Net.Mail;
    using System.Text;
    
    namespace SpecCars.Admin
    {
      public partial class Trying8 : System.Web.UI.Page
      {
        protected void Page_Load(object sender, EventArgs e)
        {
    
        }
    
        protected void Button1_Click(object sender, EventArgs e)
        {
    
          SqlDataReader sqlData;
          SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=SpecCars;Integrated Security=True");
          connection.Open();
    
          sqlData = new SqlCommand("Select * From Suppliers", connection).ExecuteReader();
          using (SmtpClient smtpClient = new SmtpClient("smtp-mail.provider.com", 587))
          {
            while (sqlData.Read())
            {
              string emailnew = sqlData["SuppEmail"].ToString();
              Label1.Text = emailnew.ToString();
    
              using (MailMessage message = new MailMessage())
              {
                try
                {
                  message.From = new MailAddress("myemail@outlook.com");
                  // This doesn't Send (To:) myotheremail@yahoo.com.au
                  MailAddress AddressTo = new MailAddress("myotheremail@yahoo.com.au");
                  // This does Send (To:) to SuppEmail recipient in Suppliers Table
                  message.To.Add(emailnew);
                  //This does Send a (CC:) myotheremail@yahoo.com.au
                  message.CC.Add("myotheremail@yahoo.com.au");
                  message.Subject = "Assembly Line Stop";
                  message.Priority = MailPriority.High;
    
                  message.Body = "Please be advised that the assembly line at Specialised Cars has STOPPED. You will be notified once the line has started again. Any Services between the LINE STOP and the LINE START will be carried out after 19:00 (7pm).";
                  message.IsBodyHtml = true;
    
                  smtpClient.EnableSsl = true;
                  smtpClient.Credentials = new System.Net.NetworkCredential("myemail@outlook.com", "password");
                  smtpClient.Send(message);
                  // smtpClient.Dispose();
                  // message.Dispose();
                }
                catch (Exception ex)
                {
                  //log exceptions here, you can write it to a txt file, or to a label in your form for testing purpose
                  //we are trying to see if you get an exception..
                  Label1.Text = ex.Message;
                }
              }
            }
          }
    
        }
      }
    }
    
    //-----------------

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 1, 2016 12:00 AM

All replies

  • User79986525 posted

    Hi ,

    Put try catch block to get the error .

    I think while sending the email you get the error

    so try it

    try {

     smtpClient.Send(mailMessage);

    }

    catch ()

    {

    }

    Tuesday, March 29, 2016 10:32 AM
  • User753101303 posted

    Hi,

    And you don't have any error? Not directly related but the loop on FieldCount is not needed (you loop on the single field you have, so no loop is needed, you could just use directly sqlData["SuppEmail"].ToString() .

    When debugging what matters is to see first what happens so either you do have a single row or you have an exception that prevents the 2nd to be processed correctly.

    Tuesday, March 29, 2016 10:46 AM
  • User-164116809 posted
    //-----------------
    
    //Hi All, just a quick update:
    
    
    //Trying8.aspx - Finally Works, it sends LINE STOP Email to every SuppEmail recipient in Suppliers Table
    
    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Trying8.aspx.cs" Inherits="SpecCars.Admin.Trying8" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
          <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
          <br />
          <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        </div>
        </form>
    </body>
    </html>
    
    //-----------------
    
    //Trying8.aspx.cs - Finally Works, it sends LINE STOP Email to every SuppEmail recipient in Suppliers Table
    
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data.SqlClient;
    using System.Net;
    using System.Net.Mail;
    using System.Text;
    
    namespace SpecCars.Admin
    {
      public partial class Trying8 : System.Web.UI.Page
      {
        protected void Page_Load(object sender, EventArgs e)
        {
    
        }
    
        protected void Button1_Click(object sender, EventArgs e)
        {
    
          SqlDataReader sqlData;
          SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=SpecCars;Integrated Security=True");
          connection.Open();
    
          sqlData = new SqlCommand("Select * From Suppliers", connection).ExecuteReader();
          using (SmtpClient smtpClient = new SmtpClient("smtp-mail.provider.com", 587))
          {
            while (sqlData.Read())
            {
              string emailnew = sqlData["SuppEmail"].ToString();
              Label1.Text = emailnew.ToString();
    
              using (MailMessage message = new MailMessage())
              {
                try
                {
                  message.From = new MailAddress("myemail@outlook.com");
                  // This doesn't Send (To:) myotheremail@yahoo.com.au
                  MailAddress AddressTo = new MailAddress("myotheremail@yahoo.com.au");
                  // This does Send (To:) to SuppEmail recipient in Suppliers Table
                  message.To.Add(emailnew);
                  //This does Send a (CC:) myotheremail@yahoo.com.au
                  message.CC.Add("myotheremail@yahoo.com.au");
                  message.Subject = "Assembly Line Stop";
                  message.Priority = MailPriority.High;
    
                  message.Body = "Please be advised that the assembly line at Specialised Cars has STOPPED. You will be notified once the line has started again. Any Services between the LINE STOP and the LINE START will be carried out after 19:00 (7pm).";
                  message.IsBodyHtml = true;
    
                  smtpClient.EnableSsl = true;
                  smtpClient.Credentials = new System.Net.NetworkCredential("myemail@outlook.com", "password");
                  smtpClient.Send(message);
                  // smtpClient.Dispose();
                  // message.Dispose();
                }
                catch (Exception ex)
                {
                  //log exceptions here, you can write it to a txt file, or to a label in your form for testing purpose
                  //we are trying to see if you get an exception..
                  Label1.Text = ex.Message;
                }
              }
            }
          }
    
        }
      }
    }
    
    //-----------------

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 1, 2016 12:00 AM