Step Creating an ASP Page

Now that the design of the user interface and the XML transformation are out of the way, you need to design the Active Server Page that will receive the XML data from the client and perform the required processing. 1. Open Notepad and type the ASP script shown on the following page.

2. Save the file as Timesheet.asp in the C:\Inetpub\wwwroot\timetrack folder.

TimeSheet.asp - Active Server Page

Dim clientXML Dim Conn Dim strConn Dim rst

Dim rstDetails Dim myDays Dim myDay Dim strStatus Dim blnProceed

Dim strTSheetId Dim strReceipt

XML Data received from the client

ADO Connection to the database

Database connection string

ADO Recordset for the table - tblTimesheets

ADO Recordset for the table - tblTimesheetDetails

Collection of Day nodes

Single day node in the collection of myDays

Timesheet processing status

Boolean value to indicate whether to continue processing

Id of the newly entered record in the tblTimesheets

XML string to send as receipt to the client

On Error Resume Next

'Load the XML data passed by client

Set clientXML = CreateObject("Microsoft.XMLDOM")

clientXML.Load(Request)

'save XML on the server (for practice only) clientXML.save(Server.MapPath("LastSubmit.xml")) blnProceed = True

'Open a connection to the TimeTrack.mdb Access database If blnProceed Then

Set Conn = CreateObject("ADODB.Connection")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

& Server.MapPath("TimeTrack.mdb") Conn.Open strConn If err.Number <> 0 Then strStatus = err.Description & " " & err.number blnProceed = False End If End If

'Open the tblTimesheets table to add new records If blnProceed Then If err.number = 0 Then

Set rst = CreateObject("ADODB.Recordset") rst.Open "SELECT * FROM tblTimesheets", Conn, 2, 3 rst.AddNew rst("EmpName").Value = _

clientXML.selectSingleNode("//EmployeeName").Text rst("EndingDate").Value = _

clientXML.selectSingleNode("//EndingDate").Text rst("TotalRegularHrs").value = _

clientXML.selectSingleNode("//TotalRegularHrs").Text rst("TotalOvertime").value = _

clientXML.selectSingleNode("//TotalOvertime").Text rst.Update strTSheetId = rst("TSheetId").Value

End If If err.number = 0 Then

'Open the tblTimesheetDetails table to add new records Set rstDetails = CreateObject("ADODB.Recordset") rstDetails.Open _

"SELECT * FROM tblTimesheetDetails", Conn, 2, 3 ' set up loop to read and write individual days Set myDays = clientXML.SelectNodes("Timesheet/Days/Day") For Each myDay In myDays rstDetails.AddNew rstDetails("TSheetId").Value = strTSheetId rstDetails("DayOfWeek").Value = myDay.childnodes(0).Text rstDetails("CalDate").Value = myDay.childnodes(1).Text rstDetails("RegularHrs").Value = myDay.childnodes(2).Text rstDetails("Overtime").Value = myDay.childnodes(3).text rstDetails.Update Next End If

If err.Number <> 0 Then strStatus = err.Description Else strStatus = "Success" End If

'Close the recordsets and connection rstDetails.Close Set rstDetails = Nothing rst.Close Set rst = Nothing Conn.Close Set Conn = Nothing End If

'Return to the client the Timesheet status in the XML format strReceipt = "<?xml version=""1.0""?>"

strReceipt = strReceipt & "<TimeSheetSubmitted>"

strReceipt = strReceipt & "<Status>" & strStatus & "</Status>"

strReceipt = strReceipt & "<TSheetId>" & strTSheetId & "</TSheetId>"

strReceipt = strReceipt & "</TimeSheetSubmitted>"

Response.ContentType = "text/xml"

Response.Write strReceipt

Response.End

The ASP page shown above begins with retrieving the XML data passed by the client. The ASP does not care who the client is (that is, which application has posted the data). All the ASP page on the server side needs to know is how to retrieve the passed data. This task is accomplished through the ASP Request object. Notice that to obtain data, the Request object is loaded into an instance of the XMLDOM, like this:

Set clientXML = CreateObject("Microsoft.XMLDOM")

clientXML.Load(Request)

After the XML data has been retrieved, the ASP page can do with the data whatever is required before sending the report back to the client. In this particular scenario, you first save the posted data to a disk file using the following statement:

clientXML.save(Server.MapPath("LastSubmit.xml"))

Note that saving the XML to a disk file is not required. It is presented here for demonstration purposes only. Next, open the connection to your custom Microsoft Access database that you prepared in the second step of this project. If the connection is successful (there are no errors—err.number <>0), open the recordset based on the tblTimeSheets table and add a new record using the AddNew method of the Recordset object. Then retrieve values of the appropriate XML elements using the Text property of the selectSingleNode method. When you've written values for each field, use the Update method of the Recordset object. After you've successfully added the new record with the employee timesheet, retrieve the ID of this record (TSheetId) and store it in the strTSheetId variable. If everything goes well (there are no errors), open the second recordset on the tblTimeSheet-Details table and proceed to add detail records.

You must loop through individual days of the week and make a record for each day. The easiest way to accomplish this is via the For Each...Next loop. Notice that each day record contains five fields named TSheetId, DayOfWeek, CalDate, RegularHrs, and Overtime. Except for the first field whose value you obtained from tblTimeSheets, you need to retrieve values for the remaining fields by using the Text property of the childnodes collection. For example, to write the name of the day of the week to the DayOfWeek field, use the following statement:

rstDetails("DayOfWeek").Value = myDay.childnodes(0).Text

Because collections are zero-based, the first element in the collection has an index value of 0.

Once you've added a detailed record for each day of the week, close both recordsets and break the connection to the database. When the processing is done by the ASP page, send some sort of notification to the client. The last fragment of the ASP script does that by first building a reply in the XML format and placing it in the strReceipt variable. Next, the content of the variable is written to the page using the statement:

Response.Write strReceipt Then it is sent to the client using Response.End.

0 0

Post a comment