Home

Networking: Time Sheet
   

Introduction

A time sheet is a list of time values worked by an employee in a time frame, such as a week, two weeks, or a month. To provide a time sheet to your users, you have various options. First you must create a form that holds the controls a user would use to enter the time worked. That aspect is obvious.

One of the real aspects you should think of is how the users would access the time sheet. You can simply let them open the form, but the user must be identified. Once againg you have many options. If you are using an application for which a user must be authenticated before using, you can catch the employee number or the name of the employee. Another way is to ask a user to enter his or her employee number in a text box and look for that employee's record in the company.

Another aspect you should (must) take care of is to find out if an employee is creating a new time sheet or updating an existing time sheet:

  • If the employee is creating a new time sheet, you would get the values and create a new record
  • If the employee is updating his or her time sheet, you must first look for that time sheet in the records. If the record is found, grab the values the user entered and replace those in the existing time sheet

A question is, who will determine whether the user is creating a new time sheet or updating an existing time sheet? The answer to this depends on how you decide to implement the time sheet.

Practical LearningPractical Learning: Creating a Time Sheet Table

This article continues from that of Authentication. This means that you must have created a folder named Fun Department Store, shared it, and created a database named FunDS in that folder. The database contains a table named Employees.

  1. Start  Microsoft Access
  2. Open the FunDS database
  3. On the Ribbon, click Create
  4. In the Tables section, click Table Design
  5. Create the following fields
     
    Field Name Data Type Field Size Format Caption Other Properties
    TimeSheetID AutoNumber     Time Sheet ID Primary Key
    EmployeeNumber   20   Employee #  
    StartDate Date/Time     Start Date  
    TimeSheetCode   40   Time Sheet Code Required: Yes
    Week1Monday Number Double Fixed Monday Default Value = 0
    Week1Tuesday Number Double Fixed Tuesday Default Value = 0
    Week1Wednesday Number Double Fixed Wednesday Default Value = 0
    Week1Thursday Number Double Fixed Thursday Default Value = 0
    Week1Friday Number Double Fixed Friday Default Value = 0
    Week1Saturday Number Double Fixed Saturday Default Value = 0
    Week1Sunday Number Double Fixed Sunday Default Value = 0
    Week2Monday Number Double Fixed Monday Default Value = 0
    Week2Tuesday Number Double Fixed Tuesday Default Value = 0
    Week2Wednesday Number Double Fixed Wednesday Default Value = 0
    Week2Thursday Number Double Fixed Thursday Default Value = 0
    Week2Friday Number Double Fixed Friday Default Value = 0
    Week2Saturday Number Double Fixed Saturday Default Value = 0
    Week2Sunday Number Double Fixed Sunday Default Value = 0
    Notes Memo        
  6. To change the view, right-click the tab of the table and click Datasheet View
  7. When asked to save it, set its name to TimeSheets
  8. Close the Time Sheets table
  9. Close Microsoft Access

Practical LearningPractical Learning: Implementing a Time Sheet
  1. Start Microsoft Visual Studio
  2. To create a new application, on the main menu, click File -> New Project...
  3. If you are using Microsoft Visual Stuio, in the left frame, click Visual C# Projects.
    In the middle list, click Empty Project
  4. Set the Name to FunDepartmentStore2
  5. Click OK
  6. On the main menu, click Project -> FunDepartmentStore2 Properties...
  7. In the Output Type combo box, select Windows Application
  8. Close the Properties window
  9. On the main menu, click Project -> Add Reference...
  10. In the Add Reference dialog box, click .NET
  11. In the list view, click System.Data
  12. Press and hold Ctrl
  13. Click System
  14. Click System.Drawing
  15. Click System.Windows.Forms
  16. Click System.Xml
  17. Release Ctrl
  18. Click OK
  19. To create a file for the project, on the main menu, click Project -> Add New Item...
  20. In the middle list, click Code File
  21. Set the Name to CreateTimeSheet
  22. Click Add
  23. In the empty document, type the following: 
    using System;
using System.Data;
using System.Drawing;
using System.Data.OleDb;
using System.Windows.Forms;
using System.ComponentModel;

public class TimeSheet : Form
{
    private Label lblIdentification;
    private Panel pnlTopLine;
    private Label lblEmployeeNumber;
    private MaskedTextBox txtEmployeeNumber;
    private TextBox txtEmployeeName;
    private Label lblStartDate;
    private DateTimePicker dtpStartDate;
    private Label lblEndDate;
    private DateTimePicker dtpEndDate;
    private TextBox txtTimeSheetCode;
    private Panel pnlSecondLine;
    private Label lblTimeRecording;
    private Label lblMonday;
    private Label lblTuesday;
    private Label lblWednesday;
    private Label lblSaturday;
    private Label lblFriday;
    private Label lblThursday;
    private Label lblSunday;
    private Label lblWeek1;
    private TextBox txtWeek1Monday;
    private TextBox txtWeek1Tuesday;
    private TextBox txtWeek1Wednesday;
    private TextBox txtWeek1Thursday;
    private TextBox txtWeek1Friday;
    private TextBox txtWeek1Saturday;
    private TextBox txtWeek1Sunday;
    private Label lblWeek2;
    private TextBox txtWeek2Monday;
    private TextBox txtWeek2Tuesday;
    private TextBox txtWeek2Wednesday;
    private TextBox txtWeek2Thursday;
    private TextBox txtWeek2Friday;
    private TextBox txtWeek2Saturday;
    private TextBox txtWeek2Sunday;
    private Label lblNotes;
    private TextBox txtNotes;
    private Panel pnlBottomLine;
    private Button btnReset;
    private Button btnSubmit;
    private Button btnClose;

    public TimeSheet()
    {
        InitializeComponent();
    }

    private void InitializeComponent()
    {
        lblIdentification = new Label();
        pnlTopLine = new Panel();
        lblEmployeeNumber = new Label();
        txtEmployeeNumber = new MaskedTextBox();
        txtEmployeeName = new TextBox();
        lblStartDate = new Label();
        dtpStartDate = new DateTimePicker();
        lblEndDate = new Label();
        dtpEndDate = new DateTimePicker();
        txtTimeSheetCode = new TextBox();
        pnlSecondLine = new Panel();
        lblTimeRecording = new Label();
        lblMonday = new Label();
        lblTuesday = new Label();
        lblWednesday = new Label();
        lblSaturday = new Label();
        lblFriday = new Label();
        lblThursday = new Label();
        lblSunday = new Label();
        lblWeek1 = new Label();
        txtWeek1Monday = new TextBox();
        txtWeek1Tuesday = new TextBox();
        txtWeek1Wednesday = new TextBox();
        txtWeek1Thursday = new TextBox();
        txtWeek1Friday = new TextBox();
        txtWeek1Saturday = new TextBox();
        txtWeek1Sunday = new TextBox();
        lblWeek2 = new Label();
        txtWeek2Monday = new TextBox();
        txtWeek2Tuesday = new TextBox();
        txtWeek2Wednesday = new TextBox();
        txtWeek2Thursday = new TextBox();
        txtWeek2Friday = new TextBox();
        txtWeek2Saturday = new TextBox();
        txtWeek2Sunday = new TextBox();
        lblNotes = new Label();
        txtNotes = new TextBox();
        pnlBottomLine = new Panel();
        btnReset = new Button();
        btnSubmit = new Button();
        btnClose = new Button();
        SuspendLayout();

        lblIdentification.BackColor = Color.FromArgb(64, 64, 64);
        lblIdentification.Font = new Font("Tiffany Lt BT", 12F, FontStyle.Bold, GraphicsUnit.Point,0);
        lblIdentification.ForeColor = Color.White;
        lblIdentification.Location = new Point(12, 11);
        lblIdentification.Size = new Size(141, 19);
        lblIdentification.TabIndex = 0;
        lblIdentification.Text = "Identification";

        pnlTopLine.BackColor = Color.FromArgb(64, 64, 64);
        pnlTopLine.Location = new Point(124, 28);
        pnlTopLine.Size = new Size(444, 2);
        pnlTopLine.TabIndex = 1;

        lblEmployeeNumber.AutoSize = true;
        lblEmployeeNumber.Location = new Point(130, 42);
        lblEmployeeNumber.Size = new Size(66, 13);
        lblEmployeeNumber.TabIndex = 2;
        lblEmployeeNumber.Text = "Employee #:";

        txtEmployeeNumber.Location = new Point(211, 39);
        txtEmployeeNumber.Mask = "00-000";
        txtEmployeeNumber.Size = new Size(52, 20);
        txtEmployeeNumber.TabIndex = 3;
        txtEmployeeNumber.Leave += new System.EventHandler(txtEmployeeNumberLeave);
 
        txtEmployeeName.Location = new Point(269, 39);
        txtEmployeeName.Size = new Size(142, 20);
        txtEmployeeName.TabIndex = 37;

        dtpEndDate.Location = new Point(211, 101);
        dtpEndDate.Size = new Size(200, 20);
        dtpEndDate.TabIndex = 39;

        lblEndDate.AutoSize = true;
        lblEndDate.Location = new Point(130, 107);
        lblEndDate.Size = new Size(55, 13);
        lblEndDate.TabIndex = 38;
        lblEndDate.Text = "End Date:";

        lblStartDate.AutoSize = true;
        lblStartDate.Location = new Point(130, 73);
        lblStartDate.Size = new Size(58, 13);
        lblStartDate.TabIndex = 4;
        lblStartDate.Text = "Start Date:";

        dtpStartDate.Location = new Point(211, 70);
        dtpStartDate.Size = new Size(200, 20);
        dtpStartDate.TabIndex = 5;
        dtpStartDate.CloseUp += new System.EventHandler(dtpStartDateClosedUp);

        txtTimeSheetCode.Enabled = false;
        txtTimeSheetCode.Location = new Point(420, 100);
        txtTimeSheetCode.Size = new Size(132, 20);
        txtTimeSheetCode.TabIndex = 40;


        pnlSecondLine.BackColor = Color.FromArgb(64, 64, 64);
        pnlSecondLine.Location = new Point(150, 154);
        pnlSecondLine.Size = new Size(415, 2);
        pnlSecondLine.TabIndex = 7;

        lblTimeRecording.BackColor = Color.FromArgb(64, 64, 64);
        lblTimeRecording.Font = new Font("Tiffany Lt BT", 12F, FontStyle.Bold, GraphicsUnit.Point,0);
        lblTimeRecording.ForeColor = Color.White;
        lblTimeRecording.Location = new Point(12, 134);
        lblTimeRecording.Size = new Size(141, 22);
        lblTimeRecording.TabIndex = 6;
        lblTimeRecording.Text = "Time Recording";

        lblMonday.AutoSize = true;
        lblMonday.Location = new Point(72, 174);
        lblMonday.Size = new Size(45, 13);
        lblMonday.TabIndex = 8;
        lblMonday.Text = "Monday";

        lblTuesday.AutoSize = true;
        lblTuesday.Location = new Point(141, 174);
        lblTuesday.Size = new Size(48, 13);
        lblTuesday.TabIndex = 9;
        lblTuesday.Text = "Tuesday";
 
        lblWednesday.AutoSize = true;
        lblWednesday.Location = new Point(212, 174);
        lblWednesday.Size = new Size(64, 13);
        lblWednesday.TabIndex = 10;
        lblWednesday.Text = "Wednesday";

        lblThursday.AutoSize = true;
        lblThursday.Location = new Point(279, 174);
        lblThursday.Name = "lblThursday";
        lblThursday.Size = new Size(51, 13);
        lblThursday.TabIndex = 11;
        lblThursday.Text = "Thursday";

        lblFriday.AutoSize = true;
        lblFriday.Location = new Point(348, 174);
        lblFriday.Size = new Size(35, 13);
        lblFriday.TabIndex = 12;
        lblFriday.Text = "Friday";

        lblSaturday.AutoSize = true;
        lblSaturday.Location = new Point(417, 174);
        lblSaturday.Size = new Size(49, 13);
        lblSaturday.TabIndex = 13;
        lblSaturday.Text = "Saturday";
 
        lblSunday.AutoSize = true;
        lblSunday.Location = new Point(495, 174);
        lblSunday.Size = new Size(43, 13);
        lblSunday.TabIndex = 14;
        lblSunday.Text = "Sunday";
 
        lblWeek1.AutoSize = true;
        lblWeek1.Location = new Point(12, 197);
        lblWeek1.Size = new Size(48, 13);
        lblWeek1.TabIndex = 15;
        lblWeek1.Text = "Week 1:";

        txtWeek1Monday.Location = new Point(75, 194);
        txtWeek1Monday.Size = new Size(63, 20);
        txtWeek1Monday.TabIndex = 16;
        txtWeek1Monday.Text = "0.00";
        txtWeek1Monday.TextAlign = HorizontalAlignment.Right;

        txtWeek1Tuesday.Location = new Point(144, 194);
        txtWeek1Tuesday.Size = new Size(63, 20);
        txtWeek1Tuesday.TabIndex = 17;
        txtWeek1Tuesday.Text = "0.00";
        txtWeek1Tuesday.TextAlign = HorizontalAlignment.Right;

        txtWeek1Wednesday.Location = new Point(213, 194);
        txtWeek1Wednesday.Size = new Size(63, 20);
        txtWeek1Wednesday.TabIndex = 18;
        txtWeek1Wednesday.Text = "0.00";
        txtWeek1Wednesday.TextAlign = HorizontalAlignment.Right;

        txtWeek1Thursday.Location = new Point(282, 194);
        txtWeek1Thursday.Size = new Size(63, 20);
        txtWeek1Thursday.TabIndex = 19;
        txtWeek1Thursday.Text = "0.00";
        txtWeek1Thursday.TextAlign = HorizontalAlignment.Right;

        txtWeek1Friday.Location = new Point(351, 194);
        txtWeek1Friday.Size = new Size(63, 20);
        txtWeek1Friday.TabIndex = 20;
        txtWeek1Friday.Text = "0.00";
        txtWeek1Friday.TextAlign = HorizontalAlignment.Right;

        txtWeek1Saturday.Location = new Point(420, 194);
        txtWeek1Saturday.Size = new Size(63, 20);
        txtWeek1Saturday.TabIndex = 21;
        txtWeek1Saturday.Text = "0.00";
        txtWeek1Saturday.TextAlign = HorizontalAlignment.Right;

        txtWeek1Sunday.Location = new Point(489, 194);
        txtWeek1Sunday.Size = new Size(63, 20);
        txtWeek1Sunday.TabIndex = 22;
        txtWeek1Sunday.Text = "0.00";
        txtWeek1Sunday.TextAlign = HorizontalAlignment.Right;

        txtWeek2Monday.Location = new Point(75, 220);
        txtWeek2Monday.Size = new Size(63, 20);
        txtWeek2Monday.TabIndex = 24;
        txtWeek2Monday.Text = "0.00";
        txtWeek2Monday.TextAlign = HorizontalAlignment.Right;

        txtWeek2Tuesday.Location = new Point(144, 220);
        txtWeek2Tuesday.Size = new Size(63, 20);
        txtWeek2Tuesday.TabIndex = 25;
        txtWeek2Tuesday.Text = "0.00";
        txtWeek2Tuesday.TextAlign = HorizontalAlignment.Right;

        txtWeek2Wednesday.Location = new Point(213, 220);
        txtWeek2Wednesday.Size = new Size(63, 20);
        txtWeek2Wednesday.TabIndex = 26;
        txtWeek2Wednesday.Text = "0.00";
        txtWeek2Wednesday.TextAlign = HorizontalAlignment.Right;

        txtWeek2Thursday.Location = new Point(282, 220);
        txtWeek2Thursday.Size = new Size(63, 20);
        txtWeek2Thursday.TabIndex = 27;
        txtWeek2Thursday.Text = "0.00";
        txtWeek2Thursday.TextAlign = HorizontalAlignment.Right;
 
        txtWeek2Friday.Location = new Point(351, 220);
        txtWeek2Friday.Size = new Size(63, 20);
        txtWeek2Friday.TabIndex = 28;
        txtWeek2Friday.Text = "0.00";
        txtWeek2Friday.TextAlign = HorizontalAlignment.Right;
 
        txtWeek2Saturday.Location = new Point(420, 220);
        txtWeek2Saturday.Size = new Size(63, 20);
        txtWeek2Saturday.TabIndex = 29;
        txtWeek2Saturday.Text = "0.00";
        txtWeek2Saturday.TextAlign = HorizontalAlignment.Right;

        txtWeek2Sunday.Location = new Point(489, 220);
        txtWeek2Sunday.Size = new Size(63, 20);
        txtWeek2Sunday.TabIndex = 30;
        txtWeek2Sunday.Text = "0.00";
        txtWeek2Sunday.TextAlign = HorizontalAlignment.Right;

        lblWeek2.AutoSize = true;
        lblWeek2.Location = new Point(12, 223);
        lblWeek2.Size = new Size(48, 13);
        lblWeek2.TabIndex = 23;
        lblWeek2.Text = "Week 2:";

        lblNotes.AutoSize = true;
        lblNotes.Location = new Point(12, 258);
        lblNotes.Name = "lblNotes";
        lblNotes.Size = new Size(35, 13);
        lblNotes.TabIndex = 31;
        lblNotes.Text = "Notes";
 
        txtNotes.Location = new Point(75, 255);
        txtNotes.Multiline = true;
        txtNotes.ScrollBars = ScrollBars.Vertical;
        txtNotes.Size = new Size(477, 64);
        txtNotes.TabIndex = 32;

        pnlBottomLine.BackColor = Color.FromArgb(64, 64, 64);
        pnlBottomLine.Location = new Point(16, 326);
        pnlBottomLine.Size = new Size(552, 2);
        pnlBottomLine.TabIndex = 33;

        btnReset.Location = new Point(255, 337);
        btnReset.Size = new Size(75, 23);
        btnReset.TabIndex = 36;
        btnReset.Text = "Reset";
        btnReset.UseVisualStyleBackColor = true;
        btnReset.Click += new System.EventHandler(btnResetClicked);

        btnSubmit.Location = new Point(338, 337);
        btnSubmit.Size = new Size(130, 23);
        btnSubmit.TabIndex = 34;
        btnSubmit.Text = "Submit Time Sheet";
        btnSubmit.UseVisualStyleBackColor = true;
        btnSubmit.Click += new System.EventHandler(btnSubmitClicked);
 
        btnClose.Location = new Point(476, 337);
        btnClose.Name = "btnClose";
        btnClose.Size = new Size(75, 23);
        btnClose.TabIndex = 35;
        btnClose.Text = "Close";
        btnClose.UseVisualStyleBackColor = true;
        btnClose.Click += new System.EventHandler(btnCloseClicked);

        ClientSize = new Size(580, 368);
        Controls.Add(txtTimeSheetCode);
        Controls.Add(btnClose);
        Controls.Add(btnSubmit);
        Controls.Add(btnReset);
        Controls.Add(pnlBottomLine);
        Controls.Add(txtNotes);
        Controls.Add(lblNotes);
        Controls.Add(txtWeek2Sunday);
        Controls.Add(txtWeek2Saturday);
        Controls.Add(txtWeek2Friday);
        Controls.Add(txtWeek2Thursday);
        Controls.Add(txtWeek2Wednesday);
        Controls.Add(txtWeek2Tuesday);
        Controls.Add(txtWeek2Monday);
        Controls.Add(lblWeek2);
        Controls.Add(txtWeek1Sunday);
        Controls.Add(txtWeek1Saturday);
        Controls.Add(txtWeek1Friday);
        Controls.Add(txtWeek1Thursday);
        Controls.Add(txtWeek1Wednesday);
        Controls.Add(txtWeek1Tuesday);
        Controls.Add(txtWeek1Monday);
        Controls.Add(lblWeek1);
        Controls.Add(lblSunday);
        Controls.Add(lblSaturday);
        Controls.Add(lblFriday);
        Controls.Add(lblThursday);
        Controls.Add(lblWednesday);
        Controls.Add(lblTuesday);
        Controls.Add(lblMonday);
        Controls.Add(pnlSecondLine);
        Controls.Add(lblTimeRecording);
        Controls.Add(dtpEndDate);
        Controls.Add(lblEndDate);
        Controls.Add(dtpStartDate);
        Controls.Add(lblStartDate);
        Controls.Add(txtEmployeeNumber);
        Controls.Add(txtEmployeeName);
        Controls.Add(lblEmployeeNumber);
        Controls.Add(pnlTopLine);
        Controls.Add(lblIdentification);
        FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;
        MaximizeBox = false;
        MinimizeBox = false;
        StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
        Text = "Fun Department Store - Employees Time Sheet";
        ResumeLayout(false);
        PerformLayout();
    }

    private void txtEmployeeNumberLeave(object sender, EventArgs e)
    {
        // This variable will be used to look for an employee
        bool employeeFound = false;

        // Connect to the database
        // To connect to the database, after the following Data Source= expression,
        // type \\, followed by the name of the server (ours is named Expression),
        // followed by \\, followed by the name of the folder where the database 
        // is installed (ours is named Fun Department Store) (of course, you must
        // have shared that folder), followed by \\, and followed by the name of
        // the database, in this case FunDS
        using (OleDbConnection conFunDS =
                      new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                        @"Data Source=\\Expression\\Fun Department Store\\FunDS.accdb"))
        {
            // Get the records of employees
            OleDbCommand cmdFunDS = new OleDbCommand("SELECT * FROM Employees;", conFunDS);

            OleDbDataAdapter odaFunDS = new OleDbDataAdapter(cmdFunDS);
            DataSet dsEmployees = new DataSet("EmployeeSet");
            odaFunDS.Fill(dsEmployees);

            conFunDS.Open();

            // Visit the record of each employee
            for (int i = 0; i < dsEmployees.Tables[0].Rows.Count; i++)
            {
                // Get a reference to the current record
                DataRow rcdEmployee = dsEmployees.Tables[0].Rows[i];

                // If you find a record whose employee number is the same as 
                // the employee number entered in the form, we have found a match
                if (rcdEmployee["EmployeeNumber"].ToString().Equals(txtEmployeeNumber.Text))
                {
                    // Display that employee's name
                    txtEmployeeName.Text = rcdEmployee["LastName"].ToString() + ", " + rcdEmployee["FirstName"].ToString();
                    // Update the flag that we have found a match
                    employeeFound = true;
                    // Look no further
                    break;
                }
            }

            // If there was no match, let the user know
            if (employeeFound == false)
            {
                MessageBox.Show("There is no employee with that employee number",
                                "Fun Department Store",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                btnResetClicked(sender, e);
            }
        }
    }

    private void btnResetClicked(object sender, EventArgs e)
    {
        txtEmployeeNumber.Text = "";
        txtEmployeeName.Text = "";
        dtpStartDate.Value = DateTime.Today;
        dtpEndDate.Value = dtpStartDate.Value;

        txtWeek1Monday.Text = "0.00";
        txtWeek1Tuesday.Text = "0.00";
        txtWeek1Wednesday.Text = "0.00";
        txtWeek1Thursday.Text = "0.00";
        txtWeek1Friday.Text = "0.00";
        txtWeek1Saturday.Text = "0.00";
        txtWeek1Sunday.Text = "0.00";

        txtWeek2Monday.Text = "0.00";
        txtWeek2Tuesday.Text = "0.00";
        txtWeek2Wednesday.Text = "0.00";
        txtWeek2Thursday.Text = "0.00";
        txtWeek2Friday.Text = "0.00";
        txtWeek2Saturday.Text = "0.00";
        txtWeek2Sunday.Text = "0.00";
        txtNotes.Text = "";
    }

    /// This function is used to create a number we will name TimeSheetCode
    /// This number is used to uniquely identify each timesheet record
    /// This number holds the employee number (00-000 )
    /// and the start date (yyyymmdd) of the time sheet
    /// It is useful as it allows us to find out whether the employee
    /// had previously filled out a time sheet or not
    private string CreateTimeSheetCode(string emplNbr, DateTime DateStart)
    {
        string strMonth, strDay;
        int iMonth, iDay;

        iMonth = DateStart.Month;
        iDay = DateStart.Day;

        if (iMonth < 10)
            strMonth = "0" + iMonth.ToString();
        else
            strMonth = iMonth.ToString();

        if (iDay < 10)
            strDay = strMonth + "0" + iDay.ToString() + DateStart.Year;
        else
            strDay = strMonth + iDay.ToString() + DateStart.Year;

        return emplNbr + strDay;
    }

    private void dtpStartDateClosedUp(object sender, EventArgs e)
    {
        DateTime dtStartDate, dtEndDate;
        // If the employee name is empty, probably the 
        // employee number is not recognixed
        if (txtEmployeeName.Text == "")
            return;

        dtStartDate = dtpStartDate.Value;
        TimeSpan tsStart = new TimeSpan(13, 0, 0, 0);
        dtEndDate = dtStartDate + tsStart;
        dtpEndDate.Value = dtEndDate;

        txtTimeSheetCode.Text = CreateTimeSheetCode(txtEmployeeNumber.Text, dtpStartDate.Value);

        // To connect to the database, after the following Data Source= expression,
        // type \\, followed by the name of the server (ours is named Expression),
        // followed by \\, followed by the name of the folder where the database 
        // is installed (ours is named Fun Department Store) (of course, you must
        // have shared that folder), followed by \\, and followed by the name of
        // the database, in this case FunDS
        using (OleDbConnection conFunDS =
                      new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                        @"Data Source=\\Expression\\Fun Department Store\\FunDS.accdb"))
        {
            OleDbCommand cmdFunDS = new OleDbCommand("SELECT * FROM TimeSheets;", conFunDS);

            OleDbDataAdapter odaFunDS = new OleDbDataAdapter(cmdFunDS);
            DataSet dsTimeSheets = new DataSet("TimeSheetSet");
            odaFunDS.Fill(dsTimeSheets);

            conFunDS.Open();

            // Visit each record
            for (int i = 0; i < dsTimeSheets.Tables[0].Rows.Count; i++)
            {
                // Get a reference to the current record
                DataRow rcdTimeSheet = dsTimeSheets.Tables[0].Rows[i];

                if (txtTimeSheetCode.Text == rcdTimeSheet["TimeSheetCode"].ToString())
                {
                    // ... it means the employee had previously created this time sheet.
                    // Get the values and display them in the text boxes on the form
                    txtWeek1Monday.Text = double.Parse(rcdTimeSheet["Week1Monday"].ToString()).ToString("F");
                    txtWeek1Tuesday.Text = double.Parse(rcdTimeSheet["Week1Tuesday"].ToString()).ToString("F");
                    txtWeek1Wednesday.Text = double.Parse(rcdTimeSheet["Week1Wednesday"].ToString()).ToString("F");
                    txtWeek1Thursday.Text = double.Parse(rcdTimeSheet["Week1Thursday"].ToString()).ToString("F");
                    txtWeek1Friday.Text = double.Parse(rcdTimeSheet["Week1Friday"].ToString()).ToString("F");
                    txtWeek1Saturday.Text = double.Parse(rcdTimeSheet["Week1Saturday"].ToString()).ToString("F");
                    txtWeek1Sunday.Text = double.Parse(rcdTimeSheet["Week1Sunday"].ToString()).ToString("F");

                    txtWeek2Monday.Text = double.Parse(rcdTimeSheet["Week2Monday"].ToString()).ToString("F");
                    txtWeek2Tuesday.Text = double.Parse(rcdTimeSheet["Week2Tuesday"].ToString()).ToString("F");
                    txtWeek2Wednesday.Text = double.Parse(rcdTimeSheet["Week2Wednesday"].ToString()).ToString("F");
                    txtWeek2Thursday.Text = double.Parse(rcdTimeSheet["Week2Thursday"].ToString()).ToString("F");
                    txtWeek2Friday.Text = double.Parse(rcdTimeSheet["Week2Friday"].ToString()).ToString("F");
                    txtWeek2Saturday.Text = double.Parse(rcdTimeSheet["Week2Saturday"].ToString()).ToString("F");
                    txtWeek2Sunday.Text = double.Parse(rcdTimeSheet["Week2Sunday"].ToString()).ToString("F");

                    // Since the record was found, look no further
                    break;
                }
                else
                {
                    // If no such record was found, reset the days text boxes
                    txtWeek1Monday.Text = "0.00";
                    txtWeek1Tuesday.Text = "0.00";
                    txtWeek1Wednesday.Text = "0.00";
                    txtWeek1Thursday.Text = "0.00";
                    txtWeek1Friday.Text = "0.00";
                    txtWeek1Saturday.Text = "0.00";
                    txtWeek1Sunday.Text = "0.00";

                    txtWeek2Monday.Text = "0.00";
                    txtWeek2Tuesday.Text = "0.00";
                    txtWeek2Wednesday.Text = "0.00";
                    txtWeek2Thursday.Text = "0.00";
                    txtWeek2Friday.Text = "0.00";
                    txtWeek2Saturday.Text = "0.00";
                    txtWeek2Sunday.Text = "0.00";
                    txtNotes.Text = "";
                }
            }
        }
    }

    private void btnSubmitClicked(object sender, EventArgs e)
    {
        bool timeSheetExistsAlready = false;
        // If the employee name is empty, probably the 
        // employee number is not recognixed
        if (txtEmployeeName.Text == "")
            return;

        // To connect to the database, after the following Data Source= expression,
        // type \\, followed by the name of the server (ours is named Expression),
        // followed by \\, followed by the name of the folder where the database 
        // is installed (ours is named Fun Department Store) (of course, you must
        // have shared that folder), followed by \\, and followed by the name of
        // the database, in this case FunDS
        using (OleDbConnection conFunDS =
                      new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                        @"Data Source=\\Expression\\Fun Department Store\\FunDS.accdb"))
        {
            OleDbCommand cmdFunDS = new OleDbCommand("SELECT * FROM TimeSheets;", conFunDS);

            OleDbDataAdapter odaFunDS = new OleDbDataAdapter(cmdFunDS);
            DataSet dsTimeSheets = new DataSet("TimeSheetSet");
            odaFunDS.Fill(dsTimeSheets);

            conFunDS.Open();

            // Visit each record (time sheet)
            for (int i = 0; i < dsTimeSheets.Tables[0].Rows.Count; i++)
            {
                // Get a reference to the current record
                DataRow rcdTimeSheet = dsTimeSheets.Tables[0].Rows[i];

                // If an employee number in the time sheet is the same as the the employee number on the form,
                // and if the corresponding start date in the time sheet is the same as the start date on the form...
                if ((rcdTimeSheet["EmployeeNumber"].ToString().Equals(txtEmployeeNumber.Text)) &&
                    (DateTime.Parse(rcdTimeSheet["StartDate"].ToString()).ToShortDateString().Equals(dtpStartDate.Value.ToShortDateString())))
                {
                    // that means the record exists already. Get ready to update it
                    timeSheetExistsAlready = true;

                    break;
                }
                else
                    timeSheetExistsAlready = false;
            }
        }

        if (timeSheetExistsAlready == true)
        {
            using (OleDbConnection conFunDS =
                      new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                        @"Data Source=\\Expression\\Fun Department Store\\FunDS.accdb"))
            {
                string strSQLUpdate = "UPDATE TimeSheets " +
                    "SET Week1Monday = " + txtWeek1Monday.Text +
                    ", Week1Tuesday = " + txtWeek1Tuesday.Text +
                    ", Week1Wednesday = " + txtWeek1Wednesday.Text +
                    ", Week1Thursday = " + txtWeek1Thursday.Text +
                    ", Week1Friday = " + txtWeek1Friday.Text +
                    ", Week1Saturday = " + txtWeek1Saturday.Text +
                    ", Week1Sunday = " + txtWeek1Sunday.Text +
                    ", Week2Monday = " + txtWeek2Monday.Text +
                    ", Week2Tuesday = " + txtWeek2Tuesday.Text +
                    ", Week2Wednesday = " + txtWeek2Wednesday.Text +
                    ", Week2Thursday = " + txtWeek2Thursday.Text +
                    ", Week2Friday = " + txtWeek2Friday.Text +
                    ", Week2Saturday = " + txtWeek2Saturday.Text +
                    ", Week2Sunday = " + txtWeek2Sunday.Text +
                    ", Notes = '" + txtNotes.Text + "' " +
                    "WHERE TimeSheetCode = '" + txtTimeSheetCode.Text + "';";
                OleDbCommand cmdTimeSheet = new OleDbCommand(strSQLUpdate, conFunDS);
                conFunDS.Open();
                cmdTimeSheet.ExecuteNonQuery();

                MessageBox.Show("The time sheet has been updated.",
                        "Fun Department Store",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        else
        {
            using (OleDbConnection conFunDS =
                      new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                        @"Data Source=\\Expression\\Fun Department Store\\FunDS.accdb"))
            {
                string strSQLInsert = "INSERT INTO TimeSheets(EmployeeNumber, StartDate, TimeSheetCode, Week1Monday, " +
                                      "Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday, " +
                                      "Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, " +
                                      "Week2Wednesday, Week2Thursday, Week2Friday, Week2Saturday, " +
                                      "Week2Sunday, Notes) VALUES('" +
                                       txtEmployeeNumber.Text + "', '" + dtpStartDate.Value.ToShortDateString() + "', '" +
                                       txtTimeSheetCode.Text + "', " +
                                       double.Parse(txtWeek1Monday.Text) + ", " +
                                       double.Parse(txtWeek1Tuesday.Text) + ", " +
                                       double.Parse(txtWeek1Wednesday.Text) + ", " +
                                       double.Parse(txtWeek1Thursday.Text) + ", " +
                                       double.Parse(txtWeek1Friday.Text) + ", " +
                                       double.Parse(txtWeek1Saturday.Text) + ", " +
                                       double.Parse(txtWeek1Sunday.Text) + ", " +
                                       double.Parse(txtWeek2Monday.Text) + ", " +
                                       double.Parse(txtWeek2Tuesday.Text) + ", " +
                                       double.Parse(txtWeek2Wednesday.Text) + ", " +
                                       double.Parse(txtWeek2Thursday.Text) + ", " +
                                       double.Parse(txtWeek2Friday.Text) + ", " +
                                       double.Parse(txtWeek2Saturday.Text) + ", " +
                                       double.Parse(txtWeek2Sunday.Text) + ", '" +
                                       txtNotes.Text + "');";

                // Generate a command to create a record
                OleDbCommand cmdEmployees = new OleDbCommand(strSQLInsert, conFunDS);

                conFunDS.Open();
                cmdEmployees.ExecuteNonQuery();

                MessageBox.Show("The time sheet has been created.",
                                "Fun Department Store",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);

                btnResetClicked(sender, e);
            }
        }
    }

    private void btnCloseClicked(object sender, EventArgs e)
    {
        Close();
    }
}

public class Exercise
{
    public static int Main()
    {
        Application.Run(new TimeSheet());
        return 0;
    }
}
    Time Sheet
 
 
 

Practical LearningPractical Learning: Testing the Time Sheet
  1. To execute the application, press F5
  2. Enter the values as follows:
     
    Employee # 17-090   Start Date January 2, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 8.00 8.50 8.00 9.50 8.50 0 0
    Week 2 8.00 8.00 6.50 6.00 6.00 0 0
     
    Time Sheet
  3. Click Submit Time Sheet
  4. Create other time sheets as follows and click Submit Time Sheet after each:
     
    Employee # 39-581   Start Date January 2, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 8 7.5 7 7.5 6 0 0
    Week 2 6 6.5 6 7.5 6 0 0

     
    Employee # 40-715   Start Date January 2, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 0 0 0 0 8  8 0
    Week 2 0 0 0 0 8 8 0
     
    Employee # 44-509   Start Date January 2, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 8 8 6 8 8 0 0
    Week 2 8 8 8.5 7 8.5 0 0
     
    Employee # 44-666   Start Date January 2, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 8 7 8.5 9.5 8 0 0
    Week 2 8 8.5 8.5 7.5 8 0 0
  5. Close the form
  6. Press F5 to execute again
  7. In the employee number, type 39-581
  8. Set the Start Date to January 2, 2011
  9. In the Week 1 row, change the Wednesday value to 8.5
  10. In the Week 2 row, change the Monday value to 8.00
  11. Click Submit Time Sheet
  12. Close the form
  13. To execute again, press F5
  14. Create new time sheets as follows:
     
    Employee # 17-090   Start Date January 16, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 8 8 8 8.5 8.5 0 0
    Week 2 8.5 8 6 7.5 0 0 0

     
    Employee # 39-581   Start Date January 16, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 8.5 8.5 6.5 9.5 8 0 0
    Week 2 6.5 8.5 6 6.5 8.5 0 0

     
    Employee # 75-937   Start Date January 16, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 0 0 0 0 8.5 6 0
    Week 2 0 0 0 0 9.5 8 0
     
    Employee # 84-715   Start Date January 16, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 0 0 0 8 8 8 0
    Week 2 0 0 0 7.50 8.5 8 0
     
    Employee # 44-666   Start Date January 16, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 8.5 7.5 6.5 10.5 8 0 0
    Week 2 9.5 6.5 7.5 8.5 8.5 0 0
     
     
    Employee # 44-509   Start Date January 16, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 8 8.0 8.5 8.5 8 0 0
    Week 2 7.5 8.5 9.5 8 9 0 0

     
    Employee # 40-715   Start Date January 16, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 0 0 0 0 9.5 10 0
    Week 2 0 0 0 0 10.5 8 0

     
    Employee # 17-090   Start Date January 30, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 8 8.5 8 8 8 0 0
    Week 2 8 8 8 8.5 8 0 0

     
    Employee # 75-937   Start Date January 30, 2011      
      Mon Tue Web Thu Fri Sat Sun
    Week 1 8.5 9.5 8.5 9 8 0 0
    Week 2 8 9 9 10 8.5 0 0
  15. Close the form
  16. Press F5 to execute again
  17. Press F5 to execute again
  18. In the employee number, type 40-715
  19. Set the Start Date to January 16, 2011
  20. In the Week 1 row, change the Thursday value to 10.5
  21. In the Week 2 row, change the Thursday value to 8.00
  22. Click Submit Time Sheet
  23. Close the application
 
 
   
 
Home Copyright © 2010-2016, FunctionX