如何使用VB.NET计算存储在SQL数据库中的时差 [英] How to calculate time difference stored in SQL database using VB.NET

问题描述

我在sql数据库中有一个列名（Time_IN，Time_OUT，Total）。

在time_IN中，存储的值是String '07：00：00 AM和Time_OUT = Current时间或lblTime.text



我想添加两列并使用vb.net插入列总数。



提前致谢。



这是我的代码，但我不知道如何从表TimeLog中选择TimeIN

Hi, I have a column name (Time_IN, Time_OUT, Total) in sql database.
In time_IN, the stored value is String '07:00:00 AM" and in Time_OUT=Current Time or lblTime.text

I want to Add the two columns and insert into column total using vb.net.

Thanks in advance.

This is my code but I dont know how to select TimeIN from table TimeLog

Dim dFrom As DateTime  
                Dim dTo As DateTime  
                Dim sDateFrom As String = 'I want to select value from time_IN
                Dim sDateTo As String = lblTime.Text 'Current Time  
                If DateTime.TryParse(sDateFrom, dFrom) AndAlso DateTime.TryParse(sDateTo, dTo) Then  
                    Dim TS As TimeSpan = dTo - dFrom  
                    Dim hour As Integer = TS.Hours  
                    Dim mins As Integer = TS.Minutes  
                    Dim secs As Integer = TS.Seconds  
                    Dim timeDiff As String = ((hour.ToString("00") & ":") + mins.ToString("00") & ":") + secs.ToString("00")  
                    txttotal.Text = timeDiff  
                End If

我的尝试：

What I have tried:

Dim dFrom As DateTime  
                Dim dTo As DateTime  
                Dim sDateFrom As String = lbltime.text 'current time
                Dim sDateTo As String = lblTime.Text 'Current Time  
                If DateTime.TryParse(sDateFrom, dFrom) AndAlso DateTime.TryParse(sDateTo, dTo) Then  
                    Dim TS As TimeSpan = dTo - dFrom  
                    Dim hour As Integer = TS.Hours  
                    Dim mins As Integer = TS.Minutes  
                    Dim secs As Integer = TS.Seconds  
                    Dim timeDiff As String = ((hour.ToString("00") & ":") + mins.ToString("00") & ":") + secs.ToString("00")  
                    txttotal.Text = timeDiff  
                End If

'没有错误，但结果是0:00:00

'no error but the result is 0:00:00

推荐答案

您应该使用正确的数据类型来存储sql中的数据，但这将使您现在开始使用



只需更改自己的strConnectionString。

You should use the correct data types for storing the data in sql but this will get you going for now

just change the strConnectionString for your own.

Dim sDateFrom As String = Nothing
   Dim dFrom As DateTime
   Dim dTo As DateTime
   Dim sDateTo As String = lblTime.Text 'Current Time
   Dim connection As New SqlConnection(strConnectionString)
   Using connection
       Dim command As SqlCommand = New SqlCommand("select top 1 TimeIN from TimeLog;", connection)
       connection.Open()
       Dim reader As SqlDataReader = command.ExecuteReader()
       If reader.HasRows Then
           Do While reader.Read()
               sDateFrom = reader.GetValue(0) ' TimeIn value
           Loop
       End If
   End Using

   sDateFrom = DateTime.TryParse(sDateFrom, dFrom)
   sDateTo = DateTime.TryParse(sDateTo, dTo)
   Dim TS As TimeSpan = dFrom - dTo
   Dim hour As Integer = TS.Hours
   Dim mins As Integer = TS.Minutes
   Dim secs As Integer = TS.Seconds
   Dim timeDiff As String = ((hour.ToString("00") & ":") + mins.ToString("00") & ":") + secs.ToString("00")
   txttotal.Text = timeDiff

这篇关于如何使用VB.NET计算存储在SQL数据库中的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！