After the user selects a date and specifies a time, she clicks the image control button. That fires off some code in a function called Button_Click, which you'll see a bit later. The image button control I'm using in my example is:
<input type=image id="InputImage" src="grafix/button-submit.gif"
border="0" OnServerClick="Button_Click" runat="server">
The next and final if-then condition (on the second screen) displays a text summary of the appointment date the user selected on the first screen (the first if-then condition) and asks her to now select the date (using the Calendar again) on which she wishes to be notified. The second screen (see Figure 2) is displayed after the second if-then condition is triggered; you'll see the code for this in just a few minutes.
Notice the URL in Figure 2. The stat variable now contains a value of 2, which triggered the second if condition. You'll see how this all happened in the implementation section.
This concludes the interface screens. Your Web page code structure should now look like this:
The text controls I use for this condition, to get the appointment title and user e-mail, are:
After the user enters the last bit of information, he clicks the image control button. That fires off some code in a function called Button_Click2, which contains the bulk of the code. Here's the image button control:
Now let's see how this works, by looking at the Implementation portion of things.
The Implementation
All the code that does the work is contained between the HTML <head> tags of the page, in a C# script block. All the code discussed below should be placed in this block, with the following syntax:
<script language="C#" runat="server">
... all the functions here ...
</script>
In a moment, I'll show you each of the three functions that go in the syntax structure above.
Before doing so, make sure you include the following namespaces at the top of the page, so that all your e-mail and database objects work in the code:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Web.Mail" %>
<%@ Import Namespace="System.Data.SqlClient" %>
Inside the main script block, the C# page load function checks the database each time the page is hit for any reminders it needs to send that day. If it finds a day where a reminder needs to be sent, it routes it off to the corresponding e-mail address of the user that made the appointment. The code appears below in listing 2.
Listing 2: The code that checks to see if a remind needs to be sent, and if so, sends it to the user's e-mail ID
void Page_Load(Object sender, EventArgs e)
{
1 DateTime DateIs;
2 DateTime DateIsd;
3 DateIs = DateTime.Now;
4 string email = ";
5 string datein = ";
6 string aptdate = ";
7 string apttime = ";
8 string apttitle = ";
9 int stat = 0;
10 Int id = 0;
11 datein = DateIs.ToShortDateString();
12 SqlConnection conn =
new SqlConnection("Data Source=<your server>;
database=CalCon;uid=xxxx;pwd=xxxx");
SqlCommand cmd = new SqlCommand( "Select * from Appts_tbl", conn);
13 conn.Open();
14 SqlDataReader myReader = cmd.ExecuteReader();
15 while (myReader.Read())
16 {
17 id = myReader.GetInt32(0);
18 email = myReader.GetString(3);
19 DateIsd = myReader.GetDateTime(4);
20 stat = myReader.GetInt32(5);
21 aptdate = myReader.GetString(6);
22 apttime = myReader.GetString(7);
23 apttitle = myReader.GetString(8);
24 string dateisx = ";
25 dateisx = DateIsd.ToShortDateString();
26 if ((datein == dateisx) && (stat == 0)) {
27 SqlConnection conn2 = new SqlConnection(
"Data Source=<your server >;database=CalCon;uid=xxxxx;pwd=xxxxxx");
28 SqlCommand commandx =
new SqlCommand( "Update Appts_tbl Set stat = 1 Where id = " + id, conn2);
29 conn2.Open();
30 commandx.ExecuteNonQuery();
31 conn2.Close();
32 MailMessage msg = new MailMessage();
33 msg.To = email;
34 msg.From = "youremail@yourdomain.com";
35 msg.Subject = "Just a Reminder for " + apttitle;
36 msg.BodyFormat = MailFormat.Html;
37 msg.Body =
"Here is your reminder not to miss " + apttitle +
" on " + aptdate + " at " + apttime;
38 SmtpMail.Send(msg);
39 }
40 }
41 myReader.Close();
42 conn.Close();
43 }
In my example, I call the database CalCon. After declaring and initiating the main variables, I set up a connection to the database and loop through all the records of the Appointment table. It's looking for the condition to be met on line 26, which makes a comparison between today's date (datein) and the date when the user is supposed to be reminded, and it checks to see if the stat field is 0 (by default).
I put this stat field in the table so that all the appointments can remain in the database; if any have been toggled to one, the script knows these the reminders for these appointments have already been fulfilled. If this condition becomes true for the record, another database connection is set up and updates this record setting the stat field value to 1, meaning the reminder for this appointment will now be fulfilled or sent to the user (lines 27-31). Lines 32-38 finish up by using the .NET e-mail objects to route the message to the user.
The remaining functions all have to do with the application and getting the user information from the Web controls, and then storing this information to the database.
The next function, Button_Click, is triggered after the image button control in Figure 1 is clicked by the user. Its job is to record the appointment date and time into Session variables (or temporary memory) that are used in the last function. The code appears below in listing 3.
Listing 3: The Button_Click function used to record the first screen's information into temporary session variables
void Button_Click(object Source, ImageClickEventArgs e)
{
if (Appttime.Text != ") {
Session["apptdate"] = Calendar1.SelectedDate.ToShortDateString();
Session["appttime"] = Appttime.Text;
Response.Redirect("apptrem.aspx?stat=2");
}
}
The Calendar control's SelectedDate method gets the date from the control, while converting it to a short date string and storing into a Session variable (apptdate). The function also gets the appointment time from the Apptime TextBox control, and stores into a Session variable called Apptime. These values are stored in the user's session for later retrieval, because the user is not yet done entering information.
The last thing the function does is call the script page again (here called apptrem.aspx) and sets the URL variable stat to 2, triggering the second if condition to display the second screen (which we discussed in the implementation section). This will get the remaining information we need from the user, and then we can store it to the database with the final function, Button_Click2.
Button_Click2 adds all the information to the database after the user provides all the necessary details and is triggered after the image button control in Figure 2 is clicked by the user. The code for this function appears below in Listing 4.
Listing 4: The Button_Click2 function used stores the information to the database
1 void Button_Click2(object Source, ImageClickEventArgs e)
2 {
3 if (Email.Text != ") {
4 int id = 0;
5 DateTime DateIs;
6 DateIs = DateTime.Now;
7 SqlConnection conn = new SqlConnection("Data Source=<your server>;
database=CalCon;uid=xxxx;pwd=xxxx");
8 SqlCommand command =
new SqlCommand( "INSERT INTO Appts_tbl(email,daten, datea,apt_time,name)
VALUES (@email, @daten, @datea, @apt_time, @name)", conn);
9 conn.Open();
10 SqlParameter param1 = new SqlParameter( "@email", SqlDbType.VarChar,75);
11 Param1.Value = Email.Text;
12 command.Parameters.Add( param1 );
13 SqlParameter param2 = new SqlParameter( "@daten", SqlDbType.DateTime);
14 param2Value = Calendar1.SelectedDate.ToShortDateString();
15 command.Parameters.Add( param2 );
16 SqlParameter param3 = new SqlParameter( "@datea", SqlDbType.VarChar,25);
17 Param3.Value = (String) Session["apptdate"];
18 command.Parameters.Add( param3 );
19 SqlParameter param4 = new SqlParameter( "@apt_time", SqlDbType.VarChar,15);
20 Param4.Value = (String) Session["appttime"];
21 command.Parameters.Add( param4 );
22 SqlParameter param5 = new SqlParameter( "@name", SqlDbType.VarChar,100);
23 Param5.Value = Name.Text;
24 command.Parameters.Add( param5 );
25 int numRowsAffectedx = command.ExecuteNonQuery();
26 conn.Close();
27 Response.Redirect("confirm.aspx");
28 }
29 }
All this function does is add the information to the database. You can see where the Session variables from the Button_Click function are used to add the appointment date and time (lines 17 and 20) to their respective database fields. On line 14, we once again use the Calendar control's SelectedDate method; this time, it's to get the reminder date and store it directly a database. On line 27, after all the information is entered, we route the user to a confirmation page displaying a friendly message saying that all the information was received and the reminder will be sent on the day specified.
You can apply the code in this article to your page in the sequence that it is shown and explained; it should work fine as it is. You can certainly have more fun with this by adding additional features. If you have an account with a Web based e-mail server, such as Hotmail or Yahoo, this application is nice because you can get the reminders right on your cell-phone!
Solution Builder 
