ASP.net Help solve problem

Bakedgoods

Bakedgoods

Bakedgoods

Capodecina
Permabanned
Joined
31 Dec 2003
Posts
5,172
Location
Barrow-In-Furness
I'm creating a timesheet system for tracking man hours, but i'm unsure on how I can display what hours have been logged how I want to.

I would like users to be able to see the number of hours they have booked like this...

----- Mon Tue Wed Thu Fri Sat Sun

Task1 4 --- 3 --- 6 --- 4
Task2 1 --- 2 --- 1 --- 4
Task3 1 --- 1
Task4 3 --- 1

Total: 9 --- 7 --- 7 --- 8

I apologise if that's not very clear, but i'm sure you can workout what i'm after. It's basically showing the hours for a week in a table.

I'm not sure how I can get the days of the week to reflect the correct date, all I store in my database in the date and I don't know how to convert the date to a paticular day or whatever, or how to show a week.

I'm worried that I may need to add a Week field to the table because I need users to be able to be able to view the data for last week in a similar fashion, through a back button or similar.

If i've not been very clear just ask me to elaborate. In essence i'm just not sure how to display the data like i've shown above.

tblHours
ID
TimeCode (The task number)
Date
Hours (Number of hours logged)
Username
Comments
Approved (Used by managers for approving)

Thanks for any help :)
 
Last edited:
Thanks.

I'm just not sure how to get the data for one week starting from a monday, then to be able to press a button and get data starting from Monday the previous week.

I'm not sure what controls to use to get it to display how I want. I don't think a GridView would work because I want each day of the week across the top, rather than just the column 'Date'.

Thanks for any input, it's really appreciated :)
 
I've tried adding it as a SQL Statement and also Stored Procedure to my Dataset but when I try to complete it I get the errors:

The Set SQL construct or statement is not supported
The DECLARE SQL construct or statement is not supported

Not sure if i've got this right, but I was trying to use the query

Code: 
DECLARE @WeekNo int
SELECT @WeekNo = 7

SET DATEFIRST 1 -- monday

SELECT     tblHours.*
FROM         tblHours

WHERE DATEPART (dw, Date) is not null AND DATEPART (wk, Date) = @WeekNo

I also tried the code below but I got the same problem with the SET command as shown above.

Code: 
SET DATEFIRST 1 -- monday

SELECT     tblHours.*
FROM         tblHours

WHERE DATEPART (dw, Date) is not null AND DATEPART (wk, Date) = @WeekNo

I'm not entirely sure what's going on here with the code so iut's hard to try fix it, i'm guessing you're trying to use some kind of built in date handling functionality that SQL has?

Thanks :]
 
I couldn't get it to add to the DataSet as a stored procedure, so I just used a normal SQL Query.

I've been sat here with a very puzzled face, I entered the number 3 for the @WeekNo value when testing the query, and got results back obviously for the 3rd week of 2008.

I'm guessing dw and wk are built in SQL commands?

That's working properly and retrieves the results for the correct week of the year. I guess i'll worry about only fetching data for the current year a little later? But i'm guessing there's some built in SQL variable that I don't know about to solve that?
 
Ultimately i'll have to add the query to the Dataset though to use it? Or do you mean use SQL Management Studio for testing etc

I can't workout how to get the results to start from a monday, or how I get the @WeekNo parameter in a web page, all the Date. commands can't do it.

Grr ;[
 
Would it be possible to somehow use a Calendar control to allow users to browse previous weeks data?

For example, if they select Thursday, it'll show all the data for that week starting from Monday.

Google is getting me no further with this :(
 
What's the advantage of using a stored procedure? Is it quicker/more efficient or do you just have access to more sql commands?
 
Ok i've talked to someone about when you use stored procedures etc and I understand that :)

How do I pass a value into a function, and then get the value out? I need to pass the current date into a function Weeknumber_Entire4DayWeekRule using a variable inDate. The function will then return the value WeekNumber, how do I get this into a textbox or label?

Code: 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim inDate As DateTime
        inDate = Date.Now.Date

        Weeknumber_Entire4DayWeekRule(inDate)

    End Sub
??
 
Thanks.

I've tested the query and it's working. Can't get anything to display now i've also added the UserName parameter though.

Code: 
 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim inDate As DateTime
        Dim WeekNumber As Integer
        Dim UserName As String

        inDate = Date.Today.Date

        WeekNumber = Weeknumber_Entire4DayWeekRule(inDate)
        UserName = Me.User.Identity.Name

        Dim hoursAdapter As New ManHoursTableAdapters.tblHoursTableAdapter()
        Dim weeksHours As ManHours.tblHoursDataTable = hoursAdapter.GetWeekly(WeekNumber, UserName)

        GridView1.DataSource = weeksHours
        GridView1.DataBind()


    End Sub

I really need to start thinking how I can get data to display like I said in the original post though...

SQL Query

Code: 
SELECT     tblHours.*
FROM         tblHours
WHERE DATEPART (dw, Date) is not null AND DATEPART (wk, Date) = @WeekNo 

AND UserName = @UserName
 
Still got the same problem, i've tried doing this another way as well..

Code: 
 Protected Sub ObjectDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) Handles ObjectDataSource1.Selecting
        Dim inDate As DateTime
        Dim WeekNumber As Integer
        Dim UserName As String

        inDate = Date.Today.Date

        WeekNumber = Weeknumber_Entire4DayWeekRule(inDate)
        UserName = Me.User.Identity.Name

        e.InputParameters("UserName") = UserName
        e.InputParameters("WeekNo") = WeekNumber
    End Sub[/B]

When I load the page using this method instead I get the error:

Code: 
Object of type 'System.Int32' cannot be converted to type 'System.Decimal'.

Thanks for any help :)
 
The UserName variable is getting the correct user name.

I've tested the query in the dataset by typing the current week number and the user name i'm logging in with and getting results.

I can't see any problem with my coding either, although something is obviously wrong with the second attempt because of the error i'm getting.
 
Function outputs an Integer, here's the code:

Code: 
 Private Function Weeknumber_Entire4DayWeekRule(ByVal inDate As DateTime) As Integer
        Const JAN As Integer = 1
        Const DEC As Integer = 12
        Const LASTDAYOFDEC As Integer = 31
        Const FIRSTDAYOFJAN As Integer = 1
        Const THURSDAY As Integer = 4
        Dim ThursdayFlag As Boolean = False

        ' Get the day number since the beginning of the year
        Dim DayOfYear As Integer = inDate.DayOfYear

        ' Get the numeric weekday of the first day of the
        ' year (using sunday as FirstDay)
        Dim StartWeekDayOfYear As Integer = _
           DirectCast(New DateTime(inDate.Year, JAN, FIRSTDAYOFJAN).DayOfWeek, Integer)
        Dim EndWeekDayOfYear As Integer = _
            DirectCast(New DateTime(inDate.Year, DEC, LASTDAYOFDEC).DayOfWeek, Integer)

        ' Compensate for the fact that we are using monday
        ' as the first day of the week
        If StartWeekDayOfYear = 0 Then
            StartWeekDayOfYear = 7
        End If
        If EndWeekDayOfYear = 0 Then
            EndWeekDayOfYear = 7
        End If

        ' Calculate the number of days in the first and last week
        Dim DaysInFirstWeek As Integer = 8 - StartWeekDayOfYear
        Dim DaysInLastWeek As Integer = 8 - EndWeekDayOfYear

        ' If the year either starts or ends on a thursday it will have a 53rd week
        If StartWeekDayOfYear = THURSDAY OrElse EndWeekDayOfYear = THURSDAY Then
            ThursdayFlag = True
        End If

        ' We begin by calculating the number of FULL weeks between the start of the year and
        ' our date. The number is rounded up, so the smallest possible value is 0.
        Dim FullWeeks As Integer = _
            CType(Math.Ceiling((DayOfYear - DaysInFirstWeek) / 7), Integer)

        Dim WeekNumber As Integer = FullWeeks

        ' If the first week of the year has at least four days, then the actual week number for our date
        ' can be incremented by one.
        If DaysInFirstWeek >= THURSDAY Then
            WeekNumber = WeekNumber + 1
        End If

        ' If week number is larger than week 52 (and the year doesn't either start or end on a thursday)
        ' then the correct week number is 1.
        If WeekNumber > 52 AndAlso Not ThursdayFlag Then
            WeekNumber = 1
        End If

        'If week number is still 0, it means that we are trying to evaluate the week number for a
        'week that belongs in the previous year (since that week has 3 days or less in our date's year).
        'We therefore make a recursive call using the last day of the previous year.
        If WeekNumber = 0 Then
            WeekNumber = Weeknumber_Entire4DayWeekRule( _
                New DateTime(inDate.Year - 1, DEC, LASTDAYOFDEC))
        End If
        Return WeekNumber
    End Function
 
I can't tell. It doesn't break on a line it just fails to load the page.

I tried debugging by stepping over each line but the page doesn't fail when I do that. It just fails when I try load it without debugging.
 
Ok i've got that part working, now for the difficult part that i've got no idea about.

How can I get the results to display like

----- Mon Tue Wed Thu Fri Sat Sun TimeCode

---------6----3---4----3---1-------------321--
---------4----2---1----4---3-------------890--
---------6----3---2----5---2-------------321--

Any ideas? It's a lot easier for the user if I can display the data in a weekly breakdown like that.

All the numbers represent the number of hours spend on a TimeCode.
 
You must log in or register to reply here.
Back
Top Bottom