I have a SQL query where I need to pull email addresses if there is one associated with that row in my table. Depending on how many email addresses are returned could be 1 all the way up to 5 I need to send an email to each of those and then I need to update a field stating that the email was sent so one does not go out again. I know the code to send emails in C# but I do not know how to capture how many emails need to be sent, loop through them to send them and them update the field stating they have been sent. Help please.
Jesi523 0 Junior Poster in Training
Jesi523 0 Junior Poster in Training
I have some code but it is not working, when it calls my email method it states it cannot be null. I am not good with arrays or loops so if someone can look and let me know what they think please?
protected void Page_Load(object sender, EventArgs e)
{
string request = Request.QueryString["request"];
string requestLink = "<html>http://Default.aspx?request=" + request + "</html>";
ArrayList emailAddressList = new ArrayList();
string sqlString = "Select Application.ownerEmail, dbo.RequestItems.applicationProfile, dbo.RequestItems.emailToSystemAdmin from Application join dbo.ApplicationProfile on dbo.ApplicationProfile.application = dbo.Application.aAuid join dbo.RequestItems on dbo.RequestItems.applicationProfile = dbo.ApplicationProfile.apAuid where request = " + request;
string connString = ConfigurationManager.ConnectionStrings["UserAccessConnectionString"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(connString);
SqlCommand sqlComm = new SqlCommand(sqlString, sqlConn);
sqlConn.Open();
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
emailAddressList.Add(reader["ownerEmail"].ToString());
string applicationProfile = reader["applicationProfile"].ToString();
bool emailToSystemAdmin = reader.GetBoolean(reader.GetOrdinal("emailToSystemAdmin"));
//then loop if email is not null and emailToSystemAdmin is not true
foreach (string emailAddress in emailAddressList)
{
if (emailAddress != null && emailToSystemAdmin != true)
{
//thencode to send the email
SendEmail(emailAddress, "The followiing link is provided to you to set up User Access for a new employee. Once IS sets up the network ID you can set-up access for this employee. Check back if network ID is not set-up yet.</br>" + requestLink);
//then update database
string sqlString2 = "UPDATE RequestItems SET emailToSystemAdmin=1 where request= " + request + "and applicationProfile = " + applicationProfile;
SqlCommand sqlComm2 = new SqlCommand(sqlString2, sqlConn);
sqlComm2.ExecuteNonQuery();
}
//then after the loop and the update
reader.Close();
sqlConn.Close();
}
}
} Jesi523 0 Junior Poster in Training
Ok I got it, except in my test it should only send out 3 emails and it is sending out 6.
protected void Page_Load(object sender, EventArgs e)
{
string request = Request.QueryString["request"];
string requestLink = "<html>http://Default.aspx?request=" + request + "</html>";
ArrayList emailAddressList = new ArrayList();
string sqlString = "Select Application.ownerEmail, dbo.RequestItems.applicationProfile, dbo.RequestItems.emailToSystemAdmin from Application join dbo.ApplicationProfile on dbo.ApplicationProfile.application = dbo.Application.aAuid join dbo.RequestItems on dbo.RequestItems.applicationProfile = dbo.ApplicationProfile.apAuid where request = " + request;
string connString = ConfigurationManager.ConnectionStrings["UserAccessConnectionString"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(connString);
SqlCommand sqlComm = new SqlCommand(sqlString, sqlConn);
sqlConn.Open();
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
emailAddressList.Add(reader["ownerEmail"].ToString());
string applicationProfile = reader["applicationProfile"].ToString();
bool emailToSystemAdmin = reader.GetBoolean(reader.GetOrdinal("emailToSystemAdmin"));
//then loop if email is not null and emailToSystemAdmin is not true
foreach (string emailAddress in emailAddressList)
{
if (emailAddress != "" && emailToSystemAdmin != true)
{
//thencode to send the email
SendEmail(emailAddress, "The followiing link is provided to you to set up User Access for a new employee. Once IS sets up the network ID you can set-up access for this employee. Check back if network ID is not set-up yet. </br>" + requestLink);
//then update database
string sqlString2 = "UPDATE RequestItems SET emailToSystemAdmin=1 where request= " + request + "and applicationProfile = " + applicationProfile;
string connString2 = ConfigurationManager.ConnectionStrings["UserAccessConnectionString"].ConnectionString;
SqlConnection sqlConn2 = new SqlConnection(connString2);
SqlCommand sqlComm2 = new SqlCommand(sqlString2, sqlConn2);
sqlConn2.Open();
sqlComm2.ExecuteNonQuery();
sqlConn2.Close();
}
}
}
reader.Close();
sqlConn.Close();
}Any ideas? Help Please
harrypalmerr 0 Newbie Poster
You may use RecordsAffected property of SQLDataReader for counting the number of emails sent per user, but as it is not set until all rows are read and you close the SqlDataReader, you must assign seperate datareader object to select all records per user.
Jesi523 0 Junior Poster in Training
I do not understand, can you explain further or give an example please.
Jesi523 0 Junior Poster in Training
I figured it out thank you.
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.