I am trying to send email retrieving the current row when the user click the "YES" option. However, the email won't populate the current record - populating the previous record instead. Please help.'
SqlConnection con3 = new SqlConnection(ConfigurationManager.ConnectionStrings
["Personnel_Info_ConnectionString"]"ConnectionString);
protected void btnInsert_Data_Click(object sender, EventArgs e)
{
SqlCommand cmd1 = new SqlCommand
(
"INSERT INTO Users.[Employee] VALUES " +
" (@txtFirstName, @txtLastName, @txtTitle, @txtEmail, @txtPhone, @txtEffective_Date, " +
" @Email_Access_Boolean, @Remote_Access_Boolean, @Employee_Access_Boolean, " +
" @Parking_Access_Boolean, @Security_Risk_Boolean, @Authorize_Purchase_Boolean, " +
" @Point_Contact_ID, @txtManager, @Dept_ID)",
con
);
System.Threading.Thread.Sleep(3000);
cmd1.Parameters.AddWithValue("@txtFirstName", txtFirstName.Text);
cmd1.Parameters.AddWithValue("@txtLastName", txtLastName.Text);
cmd1.Parameters.AddWithValue("@txtTitle", txtTitle.Text);
cmd1.Parameters.AddWithValue("@txtEmail", this.txtEmail.Text.Trim());
cmd1.Parameters.AddWithValue("@txtPhone", txtPhone.Text);
cmd1.Parameters.AddWithValue("@txtEffective_Date", txtEffective_Date.Text);
/*** Email Access logic ***/
string Email_Access = string.Empty;
// cmd1.Parameters.AddWithValue("@Email_Access_Boolean", Email_Access);
if (Email_Access_YES.Checked)
{
Email_Access = "Yes";
// cmd1.Parameters.AddWithValue("@Email_Access_Boolean", Email_Access);
/*** Send email to IS ***/
MailMessage mail_IS = new MailMessage("TestingApp5677@gmail.com", "jyuen@stbh.org");
mail_IS.IsBodyHtml = true;
SmtpClient client_IS = new SmtpClient("smtp.gmail.com", 587);
mail_IS.Body = "<table>";
StringBuilder Email_Access_builder = new StringBuilder();
Email_Access_builder.Append("Please process the following employee: ");
Email_Access_builder.Append("<br/> <br/>");
Email_Access_builder.Append
(
"<table border='4px' cellpadding='3' cellspacing='4' bgcolor='lightyellow' style='font-family:Garamond; font-size:medium'>"
);
Email_Access_builder.Append("<tr>");
/*** Database content sent to email ***/
String Email_Access_SQL =
"SELECT FirstName AS [First Name], LastName AS [Last Name], Dept AS [Department], Manager FROM Users.Employee (nolock) "
+ "WHERE ID = (SELECT MAX(ID) FROM Users.Employee WHERE Email_Access = 'Yes')";
SqlDataAdapter Email_Access_adapter = new SqlDataAdapter(Email_Access_SQL, con3);
DataSet Email_Access_data = new DataSet();
Email_Access_adapter.Fill(Email_Access_data);
foreach (DataColumn Column_Header in Email_Access_data.Tables[0].Columns)
{
Email_Access_builder.Append("<td >");
Email_Access_builder.Append(Column_Header.ColumnName);
Email_Access_builder.Append("</td>");
}
Email_Access_builder.Append("</tr>");
foreach (DataRow Row_Info in Email_Access_data.Tables[0].Rows)
{
Email_Access_builder.Append("<tr>");
foreach (DataColumn Column_Info in Email_Access_data.Tables[0].Columns)
{
Email_Access_builder.Append("<td >");
Email_Access_builder.Append(Row_Info[Column_Info.ColumnName].ToString());
Email_Access_builder.Append("</td>");
}
}
Email_Access_builder.Append("</tr>");
Email_Access_builder.Append("</table>");
Email_Access_builder.Append("<br/>");
Email_Access_builder.Append("Thanks for your help!");
mail_IS.Body = Email_Access_builder.ToString();
client_IS.DeliveryMethod = SmtpDeliveryMethod.Network;
client_IS.UseDefaultCredentials = false;
client_IS.Host = "smtp.gmail.com";
mail_IS.CC.Add(new MailAddress("ahosu@stbh.org"));
mail_IS.Subject = "Personnel Confirmation for Email Access";
client_IS.EnableSsl = true;
client_IS.Credentials = new System.Net.NetworkCredential()
{
UserName = "*****@gmail.com",
Password = "****"
};
client_IS.Send(mail_IS);
// cmd1.Parameters.AddWithValue("@Email_Access_Boolean", Email_Access);
}
else
{
Email_Access = "No";
}
cmd1.Parameters.AddWithValue("@Email_Access_Boolean", Email_Access);
What I have tried:
Please advise why the sent email populates the previous record from the "Selected" option value.