Adding/Subtracting from text boxes

[dougancil]

I have two text boxes starttimeInput and endtimeInput that have regularexpressi on validators to only accept input of time variables (11:00 am, 12:15 pm, etc) and then a durationLabel where I want to calculate between the start time and end time and present it to the user as minutes. It was suggested that I first convert the text to date and so I think I did that with the following:

Public Sub DateTimeConvert()
        Dim starttime, endtime As DateTime
        starttime = CType(starttimeInput.Text, DateTime)
        endtime = CType(endtimeInput.Text, DateTime)
    End Sub

and then it was suggested that I use a function of Subtract to minus those values.

Here's my complete aspx.vb code as it stands right now. Can anyone verify that this is the correct way to do this and also how to
1. pass the result of endtime - starttime into my durationLabel.
2. pass the durationlabel. value to my sql query.

Imports System.Data.SqlClient
Imports System.Data

Partial Class _Default
    Inherits System.Web.UI.Page
    Public Sub DateTimeConvert()
        Dim starttime, endtime As DateTime
        starttime = CType(starttimeInput.Text, DateTime)
        endtime = CType(endtimeInput.Text, DateTime)
    End Sub
    Public Function Subtract(ByVal value As TimeSpan) As DateTime
        Dim starttime As TimeSpan
        Dim endtime As TimeSpan
        Dim diff1 As System.TimeSpan
        diff1 = starttime.Subtract(endtime)
    End Function
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        InsertRow()
    End Sub
    Public Sub ClearFields()
        OperatorDropdown.SelectedIndex = -1
        exceptiondateInput.Text = ""
        starttimeInput.Text = ""
        endtimeInput.Text = ""
        durationLabel.Text = ""
        code.SelectedIndex = -1
        Approvedby.SelectedIndex = -1
    End Sub
    Public Sub InsertRow()
        Dim myConnectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxxx;"

        Dim myConnection As New SqlConnection(myConnectionString)
        Dim commandText As String = _
        "insert into Exceptions (employeenumber, exceptiondate, starttime, endtime, duration, code, approvedby) " & _
        "values(@employeenumber, @exceptiondate, @starttime, @endtime, @duration, @code, @approved)"

        Using connection As New SqlConnection(myConnectionString)
            Dim command As New SqlCommand(commandText, connection)

            command.Parameters.Add("@employeenumber", SqlDbType.VarChar)
            command.Parameters("@employeenumber").Value = OperatorDropdown.SelectedItem.Text

            command.Parameters.Add("@exceptiondate", SqlDbType.DateTime)
            command.Parameters("@exceptiondate").Value = exceptiondateInput.Text

            command.Parameters.Add("@starttime", SqlDbType.DateTime)
            command.Parameters("@starttime").Value = starttimeInput.Text

            command.Parameters.Add("@endtime", SqlDbType.DateTime)
            command.Parameters("@endtime").Value = endtimeInput.Text

            command.Parameters.Add("@duration", SqlDbType.VarChar)
            command.Parameters("@duration").Value = durationLabel.Text

            command.Parameters.Add("@code", SqlDbType.VarChar)
            command.Parameters("@code").Value = code.SelectedItem.Text

            command.Parameters.Add("@approved", SqlDbType.VarChar)
            command.Parameters("@approved").Value = Approvedby.SelectedItem.Text

            Try

                connection.Open()
                Dim rowsAffected As Integer = command.ExecuteNonQuery()

            Catch ex As Exception
                myErrorMessageLabel.Text = ex.Message
            End Try
            thankyouLabel.Text = "Your Data Has Been Submitted"
            ClearFields()
        End Using

    End Sub


End Class

Thank you,

Doug

[Frinavale]

I'm really not sure why this is so mysterious to you.

What you need to do is retrieve the text entered in the TextBoxes that all users to enter the start time and the end time.

Then you convert these values into DateTime objects so that you can validate and do your calculation. I recommend that you use the DateTime.TryPar se() method to convert the string values into DateTime objects because it does not throw any Exception when/if it has a problem converting the text into a DateTime Object (if it has a problem this method returns false).

So...to calculate the difference between two time values you would do the following (where starttimeInput and endtimeInput are TextBoxes):

   Dim startTime As DateTime    
   Dim endTime As DateTime 
   If DateTime.TryParse(starttimeInput.Text, startTime) AndAlso _
      DateTime.TryParse(endtimeInput.Text, endTime) Then
        Dim ts As TimeSpan = endTime - startTime
   Else
       'Start Time or End Time value is invalid
   End If

If you need to store the difference between the start time and the end time in the database (even though you are storing the start-time and end-time values already)...then create a DateTime object that represents this value based on the TimeSpan that resulted in subtracting the start time from the end time:

  Dim startTime As DateTime    
   Dim endTime As DateTime 
   If DateTime.TryParse(starttimeInput.Text, startTime) AndAlso _
      DateTime.TryParse(endtimeInput.Text, endTime) Then
        Dim ts As TimeSpan = endTime - startTime
'This will be stored in the database....
         Dim timeDiff As DateTime = New DateTime(Today.Year, Today.Month, Today.Day, ts.Hours, ts.Minutes, ts.Seconds)
'Or if you are storing the time difference as a string then create a string based on the ts:
         Dim strTimeDiff As String = ts.Hours.ToString + ":" + ts.Minutes.ToString + ":" + ts.Seconds.ToString
   Else
       'Start Time or End Time value is invalid
   End If

Since you need the time-difference value in order to store it in the database.....it is plainly obvious that you need to calculate it first....

-Frinny