Unspecified error when sending multiple emails using a loop c#

Question

I have a windows form program i use to manage my business. I keep all customers details in the database linked inc. email addresses. I have a button that lets me send out an email to everyone that is on the mailing list (code below). But it crashes after sending a few emails with an unspecified error. I can't work out why? I need it to send out the same email to all the customers at once going from CustomerID40- CustomerID1425 Any help would be appreciated. Sorry if the code is messy. Thank you. Error:

System.Data.OleDb.OleDbException

HResult=0x80004005

Message=Unspecified error

Source=System.Data

StackTrace:

at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)

at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.OleDb.OleDbConnection.Open()

at CourseworkDatabase.clsDBConnector.Connect() in C:\Users\rep\OneDrive\Desktop\Repairs\ Repairs_Database\CourseworkDatabase\CourseworkDatabase\clsDBConnector.cs:line 18

Code:

private void button6_Click_1(object sender, EventArgs e)
{
    DialogResult dialogResult = MessageBox.Show("Are you sure to send email to all customers?(check code)", "Warning", MessageBoxButtons.YesNo);
    if (dialogResult == DialogResult.Yes)
    {
        DoButtonTask();
    }
}
private void DoButtonTask()
{
    for (int i = 40; i < 1425; i++) //40-1425 is customerID's
    {
        clsDBConnector dbConnector1 = new clsDBConnector();
        OleDbDataReader dr1;
        string sqlStr1;
        dbConnector1.Connect();
        sqlStr1 = " SELECT CustomerID, DateAdded, FullName, PhoneNumber, EmailAddress, SendOffersByEmail" +
            " FROM Customer" + 
            " WHERE (CustomerID = " + i + ")";
        dr1 = dbConnector1.DoSQL(sqlStr1);
        string name = "";
        string CustomerID = "";
        string email = "";
        string SendOffersBy_Email = "";
        DateTime date = DateTime.Now;
        while (dr1.Read())
        {    
            CustomerID = CustomerID + Convert.ToString(dr1[0]);
            name = name + dr1[2];
            email = email + Convert.ToString(dr1[4]);
            SendOffersBy_Email = SendOffersBy_Email + Convert.ToString(dr1[5]);
        }
        if (email == "na" || email == "[email protected]" || email == "[email protected]" || email == "")
        {
            //MessageBox.Show("Customer " + CustomerID + " does not have an email linked.");
        }
        else
        {
            if (SendOffersBy_Email == "yes" || SendOffersBy_Email == "Yes")
            {    
                try
                {    
                    SendEmail(i, email, name);
                }
                catch (Exception ex)
                {    
                    MessageBox.Show(ex.ToString());
                }
            }
            else
            {   
                //MessageBox.Show("Customer " + CustomerID + " does not accept emails.");
            }
        }
    }
    MessageBox.Show("Emails sent.");
}

private void SendEmail(int i, string email, string name)
{    
    try
    {    
        MailMessage mail = new MailMessage();
        SmtpClient SmtpServer = new SmtpClient("smtp.live.com");
        mail.From = new MailAddress("myemail");
        mail.To.Add(email);
        mail.Subject = "PRICE DROP!! Have your iPhone repaired today.";
        mail.Body = "Hi " + name + ",\n" + 
            "Our iPhone 7 and iPhone 8 series screen prices have now dropped in price!" + 
            " Reply to this email to have your iPhone booked in for repair today - the prices may go back up! " + 
            " All of our repairs come with our 6 months warranty and we can come to you." + 
            "\n\nKind regards,\n Your Mobile Phone & Tablet Repair Specialist." + 
            "\n\nTel: \nWebsite: \nEmail: " + 
            "\nFacebook: " + 
            "\n\n\n Don't want to recieve offers anymore? Just reply to this email to let us know and we will take you off our mailing system.";

        SmtpServer.Port = 587;
        SmtpServer.Credentials = new System.Net.NetworkCredential("myemail", "mypassword");
        SmtpServer.EnableSsl = true;
        SmtpServer.Send(mail);
        mail.Dispose();
    }
    catch (Exception ex)
    {    
        MessageBox.Show(ex.ToString());
    }
}

why open and closed your db around 1385 times? open it at the start, itterate, and close. Your AV maybe blocking it — BugFinder
@BugFinder oh right - I didn't even think of that. I will try moving the below outside the loop and try that. I'm not the most intermediate at programming so thank you. I think that's what you mean anyway? Ill try it later today. clsDBConnector dbConnector1 = new clsDBConnector(); OleDbDataReader dr1; string sqlStr1; dbConnector1.Connect(); — Lewis
another issue could be that the amount of active objects is limited; this burned me when i made a tool for outlook; I ended up having to close and reopen outlook every 100 actions, to prevent the program from crashing on me — ThisIsMe
@BugFinder it is working perfectly now. Thank you for your help. Thank you ThisIsMe too. — Lewis

BugFinder BugFinder · Accepted Answer · 2019-03-06T09:20:02

There is an obvious improvement to the code:

    for (int i = 40; i < 1425; i++) //40-1425 is customerID's
    {
        clsDBConnector dbConnector1 = new clsDBConnector();
        OleDbDataReader dr1;
        string sqlStr1;
        dbConnector1.Connect(); 
        ...
   }

This code opens your DB for every itteration, this adds overhead, and probably your antivirus is checking the file every time.

I would suggest move the connect outside the loop (and the close), so it becomes

clsDBConnector dbConnector1 = new clsDBConnector();
OleDbDataReader dr1;
string sqlStr1;
dbConnector1.Connect();
for (int i = 40; i < 1425; i++) //40-1425 is customerID's
{
   // process data
}
dbConnector1.Close();

Unspecified error when sending multiple emails using a loop c#

Code:

2 Answers