TMS WebPlanner Insert, Update and Delete Events [TMS Tut Part 3]

Following the 1st and 2nd tutorial for binding and setting up your TMS WebPlanner ASP page. I will show you now  how to initialize the insert, update and delete events in our project.

First we initiate our WebPlanner object to have the ff. events: ** Write this code before invoking the databind function in Page_Load

 WebPlanner1.EventInserting += new PlannerEventInsertingEventHandler(WebPlanner1_EventInserting);
 WebPlanner1.EventUpdating += new PlannerEventEventHandler(WebPlanner1_EventUpdating);
 WebPlanner1.EventDeleting += new PlannerEventDeletingEventHandler(WebPlanner1_EventDeleting);

Don't worry about the errors for missing events used in the lines above. We will create them soon enough.

Alright once that's done, we need to create a database connection for these events. In this tutorial I'm using an OleDB to create a connection and do the events.

Add the following references:

using System.Data.OleDb;
using System.Data;

Create a string variable named conn. Make sure it is a class variable to make it accessible for our events.

string conn = "Provider=Microsoft.ACE.OleDb.12.0;Data Source=|DataDirectory|Planner.accdb";

Now we're ready to create our events. Let start with insert. duh!

protected void WebPlanner1_EventInserting(object sender, PlannerEventInsertingEventArgs e)
        {

            string query = String.Format("INSERT INTO [Scheduler] ( [Subject], [Notes], [StartTime], [EndTime]) VALUES ({0}, {1}, '{2}', '{3}')", e.Event.Subject, e.Event.Notes, e.Event.StartTime.ToString(), e.Event.EndTime.ToString());

            
            
            
                using (OleDbConnection conx = new OleDbConnection(conn))
                {
                    using (OleDbCommand cmd = new OleDbCommand(query, conx))
                    {
                        conx.Open();
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "SELECT @@IDENTITY";
                        int id = (int) cmd.ExecuteScalar(); //get id of the inserted database record
                        e.Event.Key = id.ToString(); //after the event has been added to the database, we return the id to the current e.Event item.
                        conx.Close();
                        Response.Redirect(Request.RawUrl); //refresh the asp page so we can see the event added for both the planner and database
                    }
                }
            
          
        }

Then the update would be.

 protected void WebPlanner1_EventUpdating(Object sender, PlannerEventEventArgs e)
        {

           
            string query = String.Format("UPDATE [Scheduler] SET [Subject] = '{0}', [Notes] = '{1}', [StartTime] = '{2}', [EndTime] = '{3}' WHERE [ID] = {4}", e.Event.Subject, e.Event.Notes, e.Event.StartTime.ToString(), e.Event.EndTime.ToString(), e.Event.Key.ToString());

            
            
                using (OleDbConnection conx = new OleDbConnection(conn))
                {
                    using (OleDbCommand cmd = new OleDbCommand(query, conx))
                    {
                        conx.Open();
                        cmd.ExecuteNonQuery();
                        conx.Close();
                        Response.Redirect(Request.RawUrl);
                    }
                }
            
        }

Lastly the delete.

protected void WebPlanner1_EventDeleting(Object sender, PlannerEventDeletingEventArgs e)
        {

           
            string query = String.Format("DELETE FROM [Scheduler] WHERE [ID] = {0}", e.Event.Key.ToString());

            
            
                using (OleDbConnection conx = new OleDbConnection(conn))
                {
                    using (OleDbCommand cmd = new OleDbCommand(query, conx))
                    {
                        conx.Open();
                        cmd.ExecuteNonQuery();
                        conx.Close();
                        Response.Redirect(Request.RawUrl);
                    }
                }
            

        }

Done! Voila! Now you guys must be saying, "That was easy!". Heck! if I had read this tutorial from some other guy I would've said that too. But I went through a lot of tracing and time before I got this planner working :)) plus the TMS Planner manual was a bit off topic, just saying! Peace! :) On part 4 I will show you how to retrieve specific events(per user/per group) with record locking for security purposes. Ka-ciao!