Saving and Reloading Timesheets

[Previous] [Next]

The Timesheet solution allows users to save the timesheets they are currently working on and reload them later to make changes. The way I implemented timesheet saving is similar to how I saved reports in Chapter 7, so some of the discussion in this section should sound familiar.

How to Play a Sound in Reaction to an Event

When I first decided to play a sound when a user enters a new value into the timesheet, I was perplexed at how to accomplish this. There was no PlaySound method in Microsoft VBScript and nothing comparable in the Document Object Model (DOM). I asked around, and no one seemed to know how to do it. Then a helpful program manager from the Internet Explorer team gave me the clue.

I had seen the <bgsound> tag before, but Internet Explorer seemed to play the sound only when the page was loaded and not when I wanted it to. However, like any other HTML element, the <bgsound> tag can be modified using script. If you set the src property of the <bgsound> tag, Internet Explorer will download the sound file and play it immediately. Sometimes a slight delay occurs the first time you play it (while Internet Explorer downloads the file), but after that, the sound plays precisely when you want it to.

Although it looks like the Timesheet solution has only two frames, a hidden frame is sandwiched between the navigation bar area and the main frame. This hidden frame's source is SaveSubmit.asp, which emits a form with a number of hidden fields. When saving a timesheet, the main frame sets the values of these fields in the hidden frame and then submits the form, posting the timesheet data and other information to the web server. The most important piece of information the main frame passes to the hidden frame is the value returned from the Spreadsheet control's HTMLData property. This property returns a string—an entire HTML document with a table in it—that represents the control's state, including the values contained within the cells of the spreadsheet. The SaveSubmit.asp script saves the timesheet data in a database on the server and then returns another form. Figure 8-2 depicts the save architecture of this solution.

click to view at full size.

Figure 8-2. The Timesheet solution's save architecture.

Let's start by looking at the code in the Save button's onClick event handler:

 Sub btnSave_onClick()     ' Post the data as a save     SubmitForm "Save"     ssTimesheet.Dirty = False End Sub 'btnSave_onClick() 

This fairly simple routine calls the SubmitForm method (which I will describe shortly) and then sets the Spreadsheet control's Dirty property to False. The Dirty property indicates whether any changes have been made to the control, either by the user or by code. You can set this property to False if you want to reset the dirty state, which you would commonly do after saving the timesheet. Later, I'll explain how I use this property in the Window object's onBeforeUnload event to prompt users to save their timesheets if they forget to do so before attempting to leave the page.

Let's look at the SubmitForm method:

 Sub SubmitForm(Verb)     ' Local variables     Dim frm             ' Form in hidden frame              ' Get a reference to the form in the hidden frame     Set frm = window.parent.frames(1).frmTimesheet     ' Fill out the form     frm.TimesheetID.value = frmTimesheet.TimesheetID.value     frm.Verb.value = Verb     frm.Employee.value = lblEmployee.innerText     frm.StartDate.value = lblStartDate.innerText      frm.Timesheet.value = ssTimesheet.HTMLData          ' Submit the form     frm.submit      If LCase(verb) = "save" Then         window.parent.status = "Timesheet Saved!"     End If  End Sub 'SubmitForm() 

This code is similar to the code from the previous chapter. It first finds the form in the hidden frame; then fills out the various fields on that form, setting the Timesheet field to the value of the HTMLData property; and finally calls the submit method of the form. This function is used for both saving and submitting timesheets, and the Verb parameter distinguishes the action. If the Verb parameter is equal to "Save", the timesheet is simply saved. If it is equal to "Submit", the timesheet is first saved and then processed, which I will describe in the next section.

NOTE
In case you didn't know, you can set the Status property of the Window object to a string that you want to display in the status bar of Internet Explorer. This is a convenient way to provide modeless status information and feedback.

Now that the code in the main frame has submitted the form in the hidden frame, let's look at the form itself and the ASP code that processes its submission. First, the form:

 <!-- FORM FOR FILE SAVE/SUBMIT --> <form id=frmTimesheet action=SaveSubmit.asp method=Post> <input type=hidden name="Verb" id="Verb"> <input type=hidden name="TimesheetID" id="TimesheetID"> <input type=hidden name="Employee" id="Employee"> <input type=hidden name="StartDate" id="StartDate"> <input type=hidden name="Timesheet" id="Timesheet"> </form> 

As you will remember from the last chapter, the use of method=Post causes Internet Explorer to send the field values in the body of the HTTP request instead of in the URL itself. This is the technique we want to use in this chapter as well because the HTMLData property can be quite long. Also note that the action for this form is to call the SaveSubmit.asp script. Since this happens to be the same file in which this form resides, it is self-referential.

Now let's examine the ASP script that processes this form's submission. It begins by opening a connection to the database and getting the action verb value ("Save" or "Submit"):

 ' Open a connection to the database Set m_cn = GetConnection() ' Are we saving or submitting? m_sAction = Request.Form("Verb") m_sAction = LCase(m_sAction) 

The GetConnection method comes from an included file called GetConnection.inc. This file uses the page's current path to determine the location of the Timesheets.mdb file on the server. You can reimplement this function quite easily so that it uses Microsoft SQL Server, which I highly recommend doing if you use this solution in your company. I use Microsoft Jet databases in the samples only because of their easy deployment, but the Jet database engine was not designed for a multiuser, high-concurrency environment such as a web server. In your real solutions, I recommend using SQL Server or some other client/server, multiuser, truly transactional database engine.

The code continues by logging the visit to the page. (I will discuss this in more detail later.) After logging the visit, the code continues with the save operation:

 If Len(m_sAction) > 0 Then     ' Get the TimesheetID from the form     m_idTimesheet = Request.Form("TimesheetID")     ' Save the timesheet stream for both save and submit     Set m_cmd = server.CreateObject("ADODB.Command")     Set m_cmd.ActiveConnection = m_cn     ' If the TimesheetId is valid, then update; otherwise, insert     If Len(m_idTimesheet) > 0 Then         m_cmd.CommandText = "UPDATE Timesheets set Timesheet = ? " & _                             "Where TimesheetID = " & m_idTimesheet         m_cmd.Parameters.Append m_cmd.CreateParameter("p1", 203, _                                        1,2000000000)         m_cmd.Parameters(0).Value = Request.Form("Timesheet")         m_cmd.Execute      Else         m_cmd.CommandText = "InsertTimesheet p1,p2,p3"         m_cmd.CommandType = 4         m_cmd.Parameters.Append m_cmd.CreateParameter("p1", 202, 1,70)         m_cmd.Parameters.Append m_cmd.CreateParameter("p2", 135, 1)         m_cmd.Parameters.Append m_cmd.CreateParameter("p3", 203, _                                        1,2000000000)         m_cmd.Parameters(0).Value = Request.Form("Employee")         m_cmd.Parameters(1).Value = Request.Form("StartDate")         m_cmd.Parameters(2).Value = Request.Form("Timesheet")         m_cmd.Execute         Dim rs          ' Temporary Recordset         Set rs = m_cn.Execute("select @@IDENTITY")         m_idTimesheet = rs(0)     End If End If 'Len(m_sAction) > 0 

I first check whether the form submitted a timesheet ID along with the timesheet data. If so, I know to perform an update to the database; if not, I know to perform an insert. The code for updating and inserting data into a database is straightforward ADO code, but in this case, I wanted to show you how to use a parameterized SQL query instead of a stored query. In the last chapter, I showed you how to use stored queries or procedures, and I do use a stored query here to insert a new timesheet. However, the code for updating a timesheet shows you how to use parameter markers ("?") in a SQL statement instead of using a stored query.

The last section of this code block illustrates how to get the new primary key for a newly inserted timesheet. Because the Timesheets table uses an AutoNumber field for the primary key, you don't know which number will be assigned to a new row until after you have inserted it. The way you retrieve this new number is by immediately selecting @@IDENTITY after executing the Insert command. Although I don't actually use this value in the ASP script, I wanted to show you how to get the new ID in case you need it for your own solution.

After the ASP script saves the timesheet data to the database, it emits that same form we saw earlier so that the main frame can again save or submit the timesheet. Since the main frame is still in place, you do not need to reload the page.

Let's now look at what happens when an existing timesheet is opened. This occurs when the user clicks the name of a saved timesheet on the Open Past Timesheet page or when the Default.asp page automatically reloads the current week's timesheet. The main timesheet page (Timesheet.asp) is actually an ASP script, so it begins by checking whether it was called to load an existing timesheet or to display a new one:

 If m_sAction = "load" Then     ' Get the timesheet stream     ' Expect Request.QueryString("TimesheetID") to contain the      ' timesheet ID     m_idTimesheet = Request.QueryString("TimesheetID")          If m_idTimesheet >= 0 Then          Set m_rs = m_cn.Execute("select StartDate, Timesheet from " & _                                 "Timesheets where TimesheetID = " & _                                 m_idTimesheet)         ' Get the timesheet stream         m_dtStart = m_rs("StartDate")         m_sTimesheetStream = m_rs("Timesheet")         m_rs.Close         Set m_rs = Nothing     Else         ' Invalid timesheet ID         Response.Write "<h3>" & nTimesheet & _                        " is not a valid timesheet ID!</h3>"     End If End If 

The m_sAction variable is set earlier in the script and is equal to Request.QueryString("Action"). If it is equal to "load", the code in this script executes a query against the database to retrieve the timesheet identified by the m_idTimesheet variable. This query should return only one row. The code continues by copying the StartDate and Timesheet column values into variables and then closes and releases the Recordset object.

The page continues by emitting the HTML user interface above the Spreadsheet control and eventually comes to this section:

 <%  ' If we are creating a new timesheet, include the template object, ' which has all the formatting and initial settings in it If m_sAction = "new" Then  %> <!-- SPREADSHEET COMPONENT WITH SAVED TIMESHEET TEMPLATE --> <!--#INCLUDE FILE="Template.htm" --> <%  ' Otherwise, just include a blank spreadsheet with the same parameter ' settings. We do this because when we load an existing timesheet, ' all the data and formatting will come in with the loaded stream, ' and this keeps the spreadsheet from having to parse the HTML data ' twice (once from the template and once from the loaded stream). Else  %> <object classid="clsid:0002E510-0000-0000-C000-000000000046"  id=ssTimesheet style="width=600px">     <param name="HTMLData" value=" <%= Server.HTMLEncode     (m_sTimesheetStream) %> ">     <param name="DataType" value="HTMLDATA"> 

The script checks the m_sAction variable again, and if the variable is equal to "new", the script includes an HTML file that contains the timesheet template. This file contains only an <object> tag and <param> tags for the Spreadsheet control. The HTMLData <param> tag in the template contains the persisted state of the template spreadsheet.

However, if the m_sAction variable is not equal to "new", the page emits an <object> tag and a set of <param> tags for the Spreadsheet control, except that this time it emits the timesheet data as the value for the HTMLData <param> tag. I use the HTMLEncode method to encode the timesheet data so that double quotes are encoded as &quot; and therefore do not confuse Internet Explorer's parser. If I did not use this method, the page would emit a double-quote character, which Internet Explorer would think signified the end of the parameter value.

When Internet Explorer loads the Spreadsheet control on the client, the Spreadsheet control reads this persisted timesheet data from the HTMLData parameter and reloads the spreadsheet exactly as you saved it. Because the Spreadsheet control loads its state from the <param> tags, no extra flashing occurs as a result of loading a default spreadsheet and then reloading the saved timesheet data.

I promised that I would explain how to use the Spreadsheet control's Dirty property to prompt the user before he or she navigates away and loses unsaved changes. Let's look at that technique now:

 Sub window_onBeforeUnload()     If ssTimesheet.Dirty Then         window.event.returnValue = "You have changed your timesheet " & _             "but have not saved it!"     End If 'Spreadsheet is dirty End Sub 'window_onBeforeUnload() 

The Window object raises an event called onBeforeUnload. From this event, you can request that Internet Explorer prompt the user, giving him or her the option to not leave the page. However, the way you do this is rather odd. You set the event's returnValue property to a string that appears in the middle of a message box displayed by Internet Explorer. The message box allows the user to click OK to leave the page or Cancel to stay. This method also works when the user attempts to close the browser window itself and will stop the window from closing if the user clicks the Cancel button.



Programming Microsoft Office 2000 Web Components
Programming Microsoft Office 2000 Web Components (Microsoft Progamming Series)
ISBN: 073560794X
EAN: 2147483647
Year: 1999
Pages: 111
Authors: Dave Stearns

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net