SQL & ASP.net Hours?

[Bakedgoods]

MS SQL

Just to confirm, I need to store the number of hours a user has spent doing something, am I correct using the smalldatetime field for the MS SQL database?

I will eventually need to be able to calculate totals using SQL Reporting Services so it's important that the hours add up correctly (to 60 minutes, not 100).

ASP.NET

I'm trying to find the correct sort of formatting to use? Short time stores the time as 9:50AM, which isn't what i'm after.

I need to store the hours as in 1 hour, or 01:00:00 (1 hour, 0 minutes, 0 seconds) or just 01:00 for 1 hour. I do need minutes but seconds are not required.

I can't find the right formatting for this, would Long time be more suited?

 

[Bakedgoods]

I'd store it as an integer in the database of minutes or seconds, if you need to go to by-the-second detail.

In .NET I'd use the TimeSpan class.

Doesn't that mean i'll have to use code to convert it from minutes to hours?

I thought there's be an easy way of storing hours so SQL would be able to do a sum on it

I could really do with storing it as hours rather than minutesm it'd make my life a lot easier and less editing elsewhere.

 

[Bakedgoods]

I need minutes, I said in the first post

 

[Bakedgoods]

Right... i'm going to store it as minutes (Integer) and they try to come up with a function that will convert it back to hours when it's being displayed.

Expect another post soon....

 

[Bakedgoods]

Dim time As Integer

time = 72

Dim mins As Integer = time Mod 60

Dim hours As Integer = (time - mins) / 60

Dim total As String = hours & ":" & mins

Does that seem ok?

I was going to convert it when i'd already pulled the data, otherwise I have to change loads of queries :x

I'm already sifting through the huge stored proc you did for me and changing field types

 

[Bakedgoods]

I’ve got the function working, but I keep getting a crappy little error and I can’t spot why…

Private Function ConvertMinutesToHours(ByVal Time As Integer) 

        Dim mins As Integer = Time Mod 60
        Dim hours As Integer = (Time - mins) / 60
        Dim total As String = hours & ":" & mins 

        Return total 

    End Function    

    Protected Sub UnapprovedHoursGridView_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles UnapprovedHoursGridView.DataBound

        Dim hoursAdapter As New ManHoursTableAdapters.tblHoursTableAdapter

        Dim hours As ManHours.tblHoursDataTable = hoursAdapter.GetUnapprovedLoggedHours(Me.User.Identity.Name.ToString())

        Dim Time As Integer

        Dim Total As String 

        For Each dr As System.Data.DataRow In hours.Rows()

            Time = dr("Hours")

            Total = ConvertMinutesToHours(Time) 

            Dim HoursLabel As Label

            HoursLabel = CType(UnapprovedHoursGridView.FindControl("HoursLabel"), Label) 

            HoursLabel.Text = Total  [B][COLOR="Red"][U]Object reference not set to an instance of an object.[/U][/COLOR][/B]
        Next

    End Sub

Any ideas?

The HoursLabel is the name of a Label in my GridView…. I can’t workout why it’s giving me that error everything looks spot on

 

[Bakedgoods]

The code was slightly wrong anyways, got it all sorted now, but can't workout how to set the text of the GridView, the name etc is spot on and someone else has even taken a look....

Protected Sub UnapprovedHoursGridView_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles UnapprovedHoursGridView.RowDataBound
        If e.Row.DataItemIndex <> -1 Then
            Dim Time As Integer = e.Row.DataItem("Hours")
            Dim Total As String

            Total = ConvertMinutesToHours(Time)

Can anyone tell me how to set a label in the GridView to Total?

 

[Bakedgoods]

 If e.Row.DataItemIndex <> -1 Then
            Dim Time As Integer
            Dim Total As String
            Dim i As Label = e.Row.FindControl("HoursLabel")

            Time = i.Text
            Total = ConvertMinutesToHours(Time)

            i.Text = Total