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:
You've got a couple of options. You can either use sql to convert, .net or both.

essentially you can use the sql, so you write a view or a stored procedure to alter the date as required.

Code: 
--Shows Week no.
select DATENAME(wk, Date) from tblHours
select DATEPART (wk, Date) from tblHours

--Show day of week
select DATEname(wk, Date) from tblHours

--Show numerical day of week
select DATEPART (dw, Date) from tblHours

or you can look at the .net datetime funtions. See below and the various date formatting methods.
Code: 
[INDENT]DateTime.Day
DateTime.DayOfWeek
Datetime.DayOfYear
[/INDENT]
 
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 :)
 
starting from Monday is a setting in sql server.

Code: 
SET DATEFIRST 1 --monday

You can put this at the start of a stored procedure to define any dates coming back.

Below I've got an example which you could play around with. If you turn this into a stored procedure turn the @WeekNo into a parameter which you can pass and comment out the DECLARE and SELECT. try playing with this sql (you may need to use group) to refine how you like the data to come back. Then you can define a gridview accordingly to display the data, but worry about the grid later, just work on how the data comes back first.
Code: 
DECLARE @WeekNo int
SELECT @WeekNo = 7

SET DATEFIRST 1 -- monday

SELECT DATEPART (dw, Date) from tblHours where Date is not null AND DATEPART (wk, Date) = @WeekNo
 
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 :]
 
ok, get rid of the set datefirst statement. worry about that later it's only to set the first day to monday.

and the declare statement, that is what I use when constructing a stored procedure, and is to emulate a parameter being passed (this is commented out in actual code)

if your doing a stored procedure straight pass in an integer for example ( @WeekNo int)

and then use with the stored procedure

Code: 
SELECT     tblHours.*
FROM         tblHours
WHERE DATEPART (dw, Date) is not null AND DATEPART (wk, Date) = @WeekNo
 
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?
 
I would recommend using it, especially as you're now working on increasingly more complex sql.

As you can then work with direct transact sql.
 
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 :(
 
Try creating the stored procedure on the db server.

Open SQL Server Management Studio.
Open the database, and select the Programmability folder.
Open Stored Procedures folder, right click and add a new procedure.

It will load a template to create the procedure. Replace the following:
Code: 
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

With this:
Code: 
CREATE PROCEDURE spcHoursByWeek 
    @WeekNo int
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
        SET DATEFIRST 1 -- Monday

    -- Insert statements for procedure here
	SELECT DATEPART (dw, Date) from tblHours where Date is not null AND DATEPART (wk, Date) = @WeekNo
END
GO

Make sure it parses correctly, and execute it.
It will create the stored procedure within the database.

Now go to your dataset then add a new query, but select use an existing stored procedure, it should list spcHoursByWeek.
 
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
??
 
Just create a new variable with the same data type as your function, then set the value of the variable to your function Weeknumber_Entire4DayWeekRule(inDate).
 
You must log in or register to reply here.
Back
Top Bottom