code executing date 1st jan on 31st jan DateTime.Add
I have this code for sending birthday reminder e-mail. It is executing fine for every date other than 1st jan of every year. The E-mail which is to be sent on 1st jan is actually sent on 31 jan even when in database it is 1 jan and also variable is reading it as 1 jan and not 31 jan. Code is:
public void birthdayReminder(string month)
{
try
{
SqlConnection con;
SqlCommand cmdReminder;
SqlDataReader userReminder;
bool result = false;
string todaydate = "";
DateTime now = DateTime.Now.AddDays(1);
todaydate = now.ToString("dd", CultureInfo.InvariantCulture);
con = new SqlConnection(ConfigurationManager.ConnectionStrings["cs"].ConnectionString);
con.Open();
cmdReminder = con.CreateCommand();
cmdReminder.CommandText = "select staffid, staffmonth, staffdate from tbstaff where staffmonth='" + month + "' and staffdate='" + todaydate + "' and staffcurrstatus='Active'";
userReminder = cmdReminder.ExecuteReader();
//userReminder.Read();
result = userReminder.HasRows;
while (userReminder.Read())
{
try
{
SqlConnection con1;
con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["cs"].ConnectionString);
con1.Open();
SqlDataReader rdr;
SqlCommand cmdRemUpd = con1.CreateCommand();
cmdRemUpd.CommandText = "select * from tbl_BirthdayReminder where staffid='" + userReminder.GetInt32(0) + "' and year='" + DateTime.Today.Year.ToString() + "'";
rdr = cmdRemUpd.ExecuteReader();
bool res = rdr.HasRows;
if(!res)
开发者_运维技巧 sendBirthdayEmail(userReminder.GetInt32(0));
con1.Close();
}
catch (Exception e1) { }
}
userReminder.Close();
con.Close();
}
catch (SqlException ex) { }
}
protected void sendBirthdayEmail(int id)
{
DataTable dt = new DataTable();
try
{
SqlDataAdapter adp = new SqlDataAdapter("select * from tbstaff where staffid='" + id + "'", ConfigurationManager.ConnectionStrings["cs"].ConnectionString);
adp.Fill(dt);
string name=dt.Rows[0]["stafffname"].ToString()+' '+dt.Rows[0]["stafflname"].ToString();
string acmng = dt.Rows[0]["staffacmng"].ToString();
SqlConnection con;
SqlCommand cmd;
con = new SqlConnection(ConfigurationManager.ConnectionStrings["cs"].ConnectionString);
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "select emailAddress from tbuser where firstName='" + acmng + "'";
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
string to= dr.GetValue(0).ToString();
con.Close();
Configuration configurationFile = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~\\Web.config");
MailSettingsSectionGroup mailSettings = configurationFile.GetSectionGroup("system.net/mailSettings") as MailSettingsSectionGroup;
string username = "";
string password = "";
string fromAddress = "";
int port = 0;
string host = "";
if (mailSettings != null)
{
port = mailSettings.Smtp.Network.Port;
host = mailSettings.Smtp.Network.Host;
password = mailSettings.Smtp.Network.Password;
username = mailSettings.Smtp.Network.UserName;
fromAddress = username;
}
string Aliasname = System.Configuration.ConfigurationManager.AppSettings["Alias"].ToString();
string body = "";
SmtpClient emailclient = new SmtpClient();
string path = "http://www.columbuscorp.com/sat/images/happybirthday.jpg";
body += "<html><body>";
body += "Hello <br /><br />";
body += "Please send birthday Card to " + name + " as his/her Birthday Date is on " + dt.Rows[0]["staffmonth"].ToString() + " " + dt.Rows[0]["staffdate"].ToString() + "<br/>";
body +="<img src=" + path;
body += " width=672 height=491></img>";
body += "<br /><br />Thanks from SAT Admin";
body += "</body></html>";
try
{
SqlConnection con1;
con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["cs"].ConnectionString);
con1.Open();
SqlCommand cmdRemUpd = con1.CreateCommand();
cmdRemUpd.CommandText = "insert into tbl_BirthdayReminder(staffid,year) values('" + id + "','" + DateTime.Today.Year.ToString() + "')";
cmdRemUpd.ExecuteNonQuery();
con1.Close();
}
catch (Exception e1) { }
The date you are looking at is always one day in the future:
DateTime now = DateTime.Now.AddDays(1);
That means on December 31st you are looking at a date in the next year. On the other hand this will use the "old" year, not the new one
cmdRemUpd.CommandText = "select * from tbl_BirthdayReminder where staffid='" + userReminder.GetInt32(0) + "' and year='" + DateTime.Today.Year.ToString() + "'";
So you are looking up a record that indeed does exist (last year's birthday reminder) hence the birthday reminder is not sent - it should be the same date as above I assume, so rather:
cmdRemUpd.CommandText = "select * from tbl_BirthdayReminder where staffid='" + userReminder.GetInt32(0) + "' and year='" + now.Year.ToString() + "'";
加载中,请稍侯......
精彩评论