Hello guys, a while back I posted something about what was needed to build an overtime app that stored data permanently on the server (here is the post https://www.daniweb.com/web-development/php/threads/467433/change-content-of-html-page-with-php) and I was told to use PHP. I have to say I've never got around that, but now I'm looking into asp.net and I was wondering whether this is possible to achieve using that and perhaps SQL (I don't know how well I have to know asp.net and SQL, I can only do small and simple applications as I 'm learning how to use.)
So, I already have the html code and the script that takes care of updating cells in a table and making the necessary calculations, but this is only at the front end:

<!DOCTYPE html>
<html>
    <head>
        <title>This is a test</title>
        <link rel="stylesheet" type="text/css" href="styles.css" >
        <script src="http://code.jquery.com/jquery.js"></script>
        <script type="text/javascript" src="script.js"></script>
    </head>
    <body>
        <table>
            <tr>
                <th>Week ending</th>
                <th>Total week hours worked</th>              
                <th>Week overtime available</th>
                <th>Total overtime</th>
                <th>Comments</th>             
            </tr>
            <tr class="editable">
                <td><input type="text" class="date"></td>
                <td> <input type="text" class="hoursWorked"></td>
                <td class="overtimeAvailable"> </td>              
                <td class="totalOvertime"></td>
                <td> <textarea type="text" class="comments"></textarea></td>                
            </tr>            
        </table>
        <button>Submit</button>
    </body>
</html>



$(document).ready(function(){
    var totalOvertime = 0;
    $("button").click(function(){
        var tableRow;
        var date;
        var hoursWorked;
        var overtimeAvailable;
        var comment;
        date = $("input.date").val();
        //console.log(date);
        hoursWorked = parseFloat($("input.hoursWorked").val());
        overtimeAvailable = hoursWorked - 37.5;
        totalOvertime += overtimeAvailable;
        comment = $(".comments").val();
        console.log(comment);
        console.log("hours " + typeof hoursWorked + " overtime " + typeof overtimeAvailable );
        tableRow = '<tr>' +
                        '<td class="date">' + date + '</td>' +
                        '<td class="hoursWorked">' + hoursWorked + '</td>' +
                        '<td class="overtimeAvailable">' + overtimeAvailable + '</td>' +
                        '<td class="totalOvertime">' + totalOvertime + '</td>' +
                        '<td class="comments">' + comment + '</td>' +
                    '</tr>';
        $(".editable").before(tableRow);
        /* $(".overtimeAvailable").each(function(){
            totalOvertime = totalOvertime + overtimeAvailable;
            $(".totalOvertime").html(totalOvertime + " hrs");
        }); */
        $("input, textarea").val("");//clear input
    });
});

In brief: currently I input the date and the amount of hours worked and then the script will calculate how much overtime I have accruited in the day, display it and add it to the total of the overtime accruited. Now, I want to build something similar which will allow me to input the date and the hours worked in a day and then do essentially the same thing except that I want all that data to be stored on a database, then calculate the daily overtime and the total overtime and display everything on a table. I have a screenshot of how things look like at the moment, and I'd like to keep it like that if possible with the addition of having input fields for user to add date a hours worked 1b72c97a40898139f3f74acdf56245c7
Needless to say the table has to remain populated everytime I open the page and if another records gets added, there will be another row available (I don't understand whether this bit needs to be done by jquery or not)
So, I was wondering whether this is feasible or not and perhaps any advice/suggestion will be much appreciated
thanks

You should be able to do this using any server side scripting language.

If you plan on using javascript/jQuery to add rows on the page client side then you will need to read the data server side from the input elements when you submit the form.

You can do this in your page load subroutine by using ...

 string str = Request.Form["input-elem-name"];

You probably have to use a for each type loop if your elements have the same name value.

Then just process the data by storing the information in a DB table.

When the page is called, you will have to read the data from your DB and build out your page. There are various ways to go about this.

It's too early to provide exact examples and sample code. You first need to be clear on what your code flow/process is going to look like.

Sometimes I first like to write out comments/pseudo code and when I have the logic correct, then start researching what the correct code will be.

Thanks, will this suffice as pseudocode?

-user to type date and total weekely hours worked in two separate input fields
- user click submit button
-jquery onclick function adds another row to the HTML table
-C# function calculates week overtime, add it to the total overtime, then push these values + the date and total weekly hours worked in a SQL database, and extract the values from the database populating the HTML table

So I kind of assume the addition of rows in the HTML table (not the SQL table) has to be taken care by jquery?

Not necessarily. For example, after the submit process occurs, you could have the logic to add a new row server side.

You may need both? Will there be a scenario where you need to add more rows client side prior to submitting the form?

I see this being done in phases...client side code, submission process, saving to the database, on page load reading the DB and generating the page will all of the data.

OK, I think I will add the row with jquery though, but I still need to find out how to add a js script to visual studio, lol! Anyway, I'm working out the server side of things, I thought I'd start from there and then when I sorted that out I will look into populating an html table with the right values. So far I get the c# code toget the values input by the user in the 2 input fields provided (date and hours worked) and pushed them into a SQL database. Now I am just writing the code - still c# - to calculate the daily overtime, add it to the overall overtime and push both onto the database. I should provide some code soon hopefully!

still need to find out how to add a js script to visual studio, lol!

in your aspx page, you add it like you would if it was an HTML page. ASP.NET pages still use HTML markup. What ASP.NET allows you to do is add ASP.NET controls and use server side scripting. Keep in mind that when the aspx page renders in the browser, its all 100% HTML markup.

cool, thanks! I think I got a little stuck with the code behind, not sure if it's because of poor logic or what. Basically once I push the data in the database, somehow I have to increment the overall overtime and I don't seem to be able to find out how. Let's have a look at some code, maybe you guys have a better idea:
HTML

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Overtime.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
    <link href="styles.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div class="wrapper">
        <h1>Overtime application</h1>
        <p>Insert week and hours worked</p>
        <div class="row">
            <label class="control-label" for="date">Enter date </label>
            <div class="controls">
                <input id="date" type="text" runat="server"/>
            </div>           
        </div>
        <div class="row">
            <label class="control-label" for="hours">Enter hours worked </label>
            <div class="controls">
                <input id="hours" type="text" runat="server"/>
            </div>
        </div>
        <div class="row">
            <asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" />
            <asp:Label ID="Label1" runat="server" Text="Label">dayOvertime</asp:Label><br />
            <asp:Label ID="Label2" runat="server" Text="Label">totalOvertime</asp:Label>
        </div>
    </div>
    </form>
</body>
</html>

C#

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    private const decimal workingHours = 37.5M;
    private string toInsert;
    private decimal dayOvertime;
  //  private string dayOvertimeString;
    private decimal totalOvertime;
    private SqlCommand sqlCmd;
    private SqlConnection hookUp;
    /*protected void Page_Load(object sender, EventArgs e)
    {

    }*/

    protected void Button1_Click(object sender, EventArgs e)
    {
        hookUp = new SqlConnection("Server=localhost\\SqlExpress;Database=Overtime;" + "Integrated Security=True");
        toInsert = "INSERT INTO OvertimeTable(Week,WeekHrs,WeekOvt,TotalOvt,Comment)VALUES(@Week,@WeekHrs,@WeekOvt,@TotalOvt,@Comment)";
        sqlCmd = new SqlCommand(toInsert, hookUp);
        dayOvertime = Convert.ToDecimal(hours.Value) - workingHours;
        totalOvertime += dayOvertime;

        //Console.WriteLine(hours.Value);
        sqlCmd.Parameters.Add("@Week", date.Value);
        sqlCmd.Parameters.Add("@WeekHrs", hours.Value);
        sqlCmd.Parameters.Add("@WeekOvt", dayOvertime);
        //SELECT TOP 1 TotalOvt OvertimeTable()
        sqlCmd.Parameters.Add("@TotalOvt", totalOvertime);
        sqlCmd.Parameters.Add("@Comment", "This is a comment");

        hookUp.Open();
        sqlCmd.ExecuteNonQuery();
        hookUp.Close();
       // Label1.Text = Convert.ToString(dayOvertime);
       /* dayOvertime = Convert.ToDecimal(hours.Value) - workingHours;
        totalOvertime += dayOvertime;
        Label1.Text = Convert.ToString(dayOvertime);
        Label2.Text = Convert.ToString(totalOvertime);*/
    }
}

So, here is a screenshot of the SQL table in visual studio:
2a5fc4182559d6198c2ce373a70da20d

Basically, the TotalOvt needs to increment and be the sum of all the overtime available. Now, in terms of how to do that, I had a few ideas but I don't know whether they are feasible or not. In my code you'll notice that I naively added totalOvertime += dayOvertime;, don't know what I was thinking!! What would be the best way to do this, would it be best to select the totalOvertime column, add all the values up and display them in the last cell, or select the second last cell, add it's value to the last one? We need to consider that the total value obtained, hoever we obtain it, will have to be then exported to the HTML table - which I haven't built yet, lol!
thanks

Actually, I had a good look and found a SQL SUM() method that seems to be doing what I need, well, at least on paper!
Now, I have changed my code-behind a little, and it is very clear to me that I still have a lot to learn in SQL and C#...

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    private const decimal workingHours = 37.5M;
    private string toInsert;   

    private decimal dayOvertime;
  //  private string dayOvertimeString;
    //private decimal totalOvertime;
    private string Ovt;
    private SqlCommand sqlCmd;
    private SqlConnection hookUp;    

    protected void Button1_Click(object sender, EventArgs e)
    {
        hookUp = new SqlConnection("Server=localhost\\SqlExpress;Database=Overtime;" + "Integrated Security=True");

       toInsert = "INSERT INTO OvertimeTable(Week,WeekHrs,WeekOvt,Comment)VALUES(@Week,@WeekHrs,@WeekOvt,@Comment)";        
      sqlCmd = new SqlCommand(toInsert, hookUp);

        dayOvertime = Convert.ToDecimal(hours.Value) - workingHours;

        Ovt = "SELECT SUM(WeekOvt) AS TotalOvt FROM OvertimeTable";


        sqlCmd.Parameters.Add("@Week", date.Value);
        sqlCmd.Parameters.Add("@WeekHrs", hours.Value);
        sqlCmd.Parameters.Add("@WeekOvt", dayOvertime);        
        //sqlCmd.Parameters.Add("@TotalOvt", totalOvertime);
        sqlCmd.Parameters.Add("@Comment", "This is a comment");

       // sqlCmd = new SqlCommand(Ovt, hookUp);
        sqlCmd = new SqlCommand(toRead, hookUp);
        hookUp.Open();
        sqlCmd.ExecuteNonQuery();

        hookUp.Close();

    }
}

So, I have changed the data I push in the database:
toInsert = "INSERT INTO OvertimeTable(Week,WeekHrs,WeekOvt,Comment)VALUES(@Week,@WeekHrs,@WeekOvt,@Comment)";
In the above I am not inserting any value for the TotalOvt column, I am leaving it blank and the plan is to use it to store the total overtime (the column is still available in the database I haven't deleted it).
This is insted the line that, I believe, should get the sum of the overtime and display it in the TotalOvt column:
Ovt = "SELECT SUM(WeekOvt) AS TotalOvt FROM OvertimeTable";
Now, I try to put things together but with little success. The above is the latest version that only push the content in the database withouth making any sum, and since I have now 2 queries (toInsert and Ovt )to the database do I need to add another statement that allow the Ovt query to run? What I mean is, at the moment I only have sqlCmd.ExecuteNonQuery(); which takes care of the insertion of data, but does this Ovt = "SELECT SUM(WeekOvt) AS TotalOvt FROM OvertimeTable"; require its own sqlCmd? Is it actualy possible to run two queries in the same program? Is there any special way? I looked this up but I have to admit that it was very confusing...
thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.