Member Avatar for Jesi523

My code is not wroking to send out an email. I have 3 conditions that need to be met. managerEmailAddress and emailSentToManager only appear
in the DB once. completeDate is in another table and it can appear several times. So basically if all items are complete and an email has
not been sent out I would like an email to be send. I took out my connection string but really there is one in there. Any help, please?

string sqlString3 = "Select dbo.Requests.managerEmail, dbo.Requests.rAuid, dbo.Requests.emailSentToManager, dbo.RequestItems.completeDate from dbo.Requests join dbo.RequestItems on dbo.Requests.rAuid = dbo.RequestItems.request";
            string connString3 = ConfigurationManager.ConnectionStrings[""].ConnectionString;
            SqlConnection sqlConn3 = new SqlConnection(connString3);
            SqlCommand sqlComm3 = new SqlCommand(sqlString3, sqlConn3);

            sqlConn3.Open();
            SqlDataReader reader2 = sqlComm3.ExecuteReader();

            while (reader2.Read())
            {
                string managerEmailAddress = (reader2["managerEmail"].ToString());
                string completeDate = (reader2["completeDate"].ToString());
                string request2 = reader2["rAuid"].ToString();
                bool emailSentToManager = reader2.GetBoolean(reader2.GetOrdinal("emailSentToManager"));
                string requestLink2 = "<html>http://ViewEmployeeDetail.aspx.aspx?request=" + request2 + "</html>";

                //then loop if email is not null and emailSentToManager is not true and complete date is not null
                if (managerEmailAddress != null && emailSentToManager != true && completeDate != null)
                {
                    //then code to send the email
                    SendEmail(managerEmailAddress, "Click on the following link to the view credentials for your new user: " + requestLink2);
                    //then update database
                    string sqlString4 = "UPDATE Requests SET emailSentToManager= 1 where rAuid= " + request2;
                    string connString4 = ConfigurationManager.ConnectionStrings[""].ConnectionString;
                    SqlConnection sqlConn4 = new SqlConnection(connString4);
                    SqlCommand sqlComm4 = new SqlCommand(sqlString4, sqlConn4);
                    sqlConn4.Open();
                    sqlComm4.ExecuteNonQuery();
                    sqlConn4.Close();
                }
            }
            reader2.Close();
            sqlConn3.Close();
  • 2 Contributors
  • 3 Replies
  • 85 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by Mitja Bonca
Member Avatar for Mitja Bonca

I checked and repair it. This should work now.
BTW: you dont need to create new instances of connectionString and sqlConnection. You can set this only ones (just make sure thats it opened, when its needs to be - but only one, ok?).

Here is the code:

private void TheMethod()
        {
            string sqlString3 = "Select dbo.Requests.managerEmail, dbo.RequestItems.completeDate dbo.Requests.rAuid, dbo.Requests.emailSentToManager " +
                                "from dbo.Requests join dbo.RequestItems on dbo.Requests.rAuid = dbo.RequestItems.request";
            string connString3 = ConfigurationManager.ConnectionStrings[""].ConnectionString;
            SqlConnection sqlConn3 = new SqlConnection(connString3);
            SqlCommand sqlComm3 = new SqlCommand(sqlString3, sqlConn3);

            sqlConn3.Open();
            SqlDataReader reader2 = sqlComm3.ExecuteReader();
            while (reader2.Read())
            {
                string managerEmailAddress = (string)reader2[0];
                string completeDate = (string)reader2[1];
                string request2 = (string)reader2[2];
                bool emailSentToManager = (bool)reader2[3];
                string requestLink2 = "<html>http://ViewEmployeeDetail.aspx.aspx?request=" + request2 + "</html>";

                //then loop if email is not null and emailSentToManager is not true and complete date is not null
                if (!String.IsNullOrEmpty( managerEmailAddress) && !String.IsNullOrEmpty(completeDate) && emailSentToManager != true)
                {
                    //then code to send the email
                    SendEmail(managerEmailAddress, "Click on the following link to the view credentials for your new user: " + requestLink2);
                    //then update database
                    string sqlString4 = "UPDATE Requests SET emailSentToManager= 1 where rAuid= " + request2;
                    SqlCommand sqlComm4 = new SqlCommand(sqlString4, sqlConn3);
                    sqlComm4.ExecuteNonQuery();
                }
            }
            reader2.Close();
            sqlConn3.Close();
        }
Member Avatar for Jesi523

thanks for the help but it sends out 11 emails for the wrong person (because there are 11 complete date for this request???)instead of just one. Please help again?

Member Avatar for Mitja Bonca

What you are saying it all depends on the sql query. You have to rework the it, with better using "Where" clauses.
I cant help you here, becuase I dont know your db, and neither what exactly and to whom you are sanding emails.

sorry,
Mitja

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.