keeping record of time (shift work) in a database

[proKrastinate]

what I want to do is allow the user of my program to make a schedule for the employees she works with. i want it to look like an excel worksheet because that is what she uses now so I guess I'll be using a datagrid to connect to my database.

 

Is there a format in which .net and the database will store shift times...(like 9 to 6, 11 to 4, 6 to 3:30) but be able to print the information in a format such as 9 - 6 or 6 - 3:30 instead of 9 to 6 or 6 to 3:30?

[Robby]

Well the data should be stored in 2 fields (StartTime and EndTime), then in the grid or a report you would use the SQL to merge the 2.

 

ie.

"SELECT (StartTime & " - " & EndTime) AS TotalTime......

 

Then use TotalTime as the field to display.

[proKrastinate]

the thing is the information will be entered in the grid before in storing. so she will input 9 - 6 into a cell and than store it into the db. the reason i want it this way is because i want it to look exactly like she is doing now. i want my program to make it easier on her without having to relearn what she is doing. and she does it in excell with just plain cells/rows/columns/etc.

 

so should i, instead of having a datagrid connected to the db, make a simulated excell worksheet using textboxes and than read into memory the data until i get to the " - " part, and than store the ladder data in the second column? i checked in the access db and unfortunately the time format is only allowed as 9:00 and not 9 :(

[proKrastinate]

ok, figured out quick that this idea was a bad one.

 

is there a way to catch the data between the database and its printing on the screen and change its format? or rather to bind cells together to make one cell?

[Robby]

I prefer giving the user two cells(textboxes) instead of only one. It's up to you how you want to tackle this.

 

Of course you can parse the users' entry before you save it to the database, as well you can format it before displaying it back to the user.

 

I made this function a while back, it re-formats user entered time into a proper time format, ie.

the user enters 2:5 the function will adjust to 02:50 or if they enter 13:30 it re-format to 01:30.

 

It would be recommended to place two radio buttons for AM and PM...

Private Function ReFormatTime(ByVal strEntry As String) As String
       Try
           If IsDate(strEntry) And strEntry.IndexOf(":") >= 0 Then

               Dim sLeft As String = strEntry.Substring(0, 2) 'assign left 2 digits
               Dim sRight As String = strEntry.Substring(strEntry.Length - 2) 'assign right 2 digits

               If Not IsNumeric(sLeft) Then strEntry = "0" & strEntry 'pad left half with '0'. It must have been like this ... 2:36
               If Not IsNumeric(sRight) Then strEntry = strEntry & "0" 'pad the right half with '0'. It must have been like this ... 02:3 


               'If the user thinks it's a 24 hour clock (military)
               If CType(strEntry.Substring(0, 2), Short) >= 13 Then
                   'this will adjust a 13:30 to 01:30...or a 23:00 to 11:00
                   strEntry = Format(CType(strEntry.Substring(0, 2), Short) Mod 12, "00") & ":" & strEntry.Substring(strEntry.Length - 2)
               End If

               'if the user entered 00:nn 
               If strEntry.Substring(0, 2) = "00" Then
                   strEntry = "12:" & strEntry.Substring(strEntry.Length - 2)
               End If

               'verify that the right half is not 02:60 (resulted from 02:6 being adjusted earlier)
               sRight = strEntry.Substring(strEntry.Length - 2)
               If CType(sRight, Short) > 59 Then
                   'we are here because the user entered 02:6
                   Return ""
               Else
                   Return strEntry
               End If
           Else
               Return ""
           End If
       Catch
           Return "" 'in case of error we return a blank string
       End Try

   End Function

[proKrastinate]

cool thanx. i have to get a handle on how to interlink the datagrid and database first. i could do it in vb6 but now it doesn't seem as easy. perhpas ive forgetten some stuff over the past year.