How to find wich time is next

Question

Hello, i have times from SQL  datebase, example:

 

 "09:00:00"  

 "09:45:00" 

 "10:00:00" 

 "10:45:00" 

 "11:00:00" 

 "11:45:00"

 "12:00:00"

 and system time is example: "11:15:00" , how to find that next time is  "11:45:00"?

 

 

 

Answer 1

Hello Lasha34,

Try this code with one button on a FORM.

Replace the string "11:45:00" with the time as a string from your database.


Happy coding from,

TattooedBloke

Option Strict On

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim DataBaseTimeString As String
        DataBaseTimeString = "11:15:00"
        Dim NextDateTime As DateTime = CDate(Now.ToLongDateString & " " & DataBaseTimeString)

        If Now.Minute < 45 Then
            NextDateTime = CDate(NextDateTime.ToLongDateString & " " & NextDateTime.Hour.ToString & ":" & "45" & ":" & NextDateTime.Second.ToString)
        Else
            NextDateTime = CDate(NextDateTime.ToLongDateString & " " & (NextDateTime.Hour + 1).ToString & ":" & "00" & ":" & NextDateTime.Second.ToString)
        End If

        MessageBox.Show(NextDateTime.ToLongTimeString)


    End Sub
End Class

Answer 2

In Else block it should be

& ":" & "00" & ":" instead of & ":" & "45" & ":" ?

---

Thanks

♦ My Blog ♦ My Facebook ♦ YOUR Place to have fun time ! ♦ Awesome RPG Action Game

Answer 3

In Else block it should be

& ":" & "00" & ":" instead of & ":" & "45" & ":" ?

---

Thanks

♦ My Blog ♦ My Facebook ♦ YOUR Place to have fun time ! ♦ Awesome RPG Action Game

Hi Omie,

Well spotted, I will edit the above post.
I marked your post helpful.


Regards from,

Tattooed Bloke

Answer 4

but if time is

"09:00:00"  

 "09:35:00" 

 "10:00:00" 

 "10:45:00" 

 "11:00:00" 

 "11:53:00"

 "12:05:00"

i want to make automatic school bell

Answer 5

i want to make automatic school bell

Well, let me throw my "for what it's worth" in, although I'd like to preface it by saying that what you'll see in this post is sure to be confusing to some extent because much of it was test and/or setup data that you won't need. I'll explain as I move through it though.

Let me also say that I do NOT think this is the best way! There's sure to be a shorter, easier, more direct way and I further suspect that it would be in a query statement, but that's definitely not a strong suit for me. At any rate, here goes my try with it:

The first thing that I had to do was to create a database. I have no idea how yours is structured but the only field I was interested in here was the school bell ring times. That field ("Bell_Time_Standard" in mine) is set up as a DateTime field. I next added a text field that I call "Bell_Time_Sortable" and that's the column that we'll sort things on, so obviously the formatting of the entries into this field is a crucial part of it.

I used Access (not recommended!) and created a new data table with the times you showed above. Not thinking about what Access does, it accepted the times but then set the date to something like December 1899 or some craziness. In the following method, you'll see that part of mine changes it to today's date but retains the times that are already entered there. I feel sure that's not the case with yours so please do be sure to read through this carefully to understand what I'm doing at each step:

Sub UpdateDataBaseWithSortableTimes()

        For i = 0 To SchoolBellDataSet.BellRingTimes.Rows.Count - 1

            Dim dbStandardTime As DateTime = FormatDateTime(SchoolBellDataSet.BellRingTimes(i).Bell_Time_Standard, DateFormat.ShortTime)
            Dim dbSortableTime As String = Format(SchoolBellDataSet.BellRingTimes(i).Bell_Time_Standard, "yyyy'-'MM'-'dd'T'HH':'mm':'ss")
            Dim thisDate As DateTime = Today.ToShortDateString
            Dim replaceStandardTime As DateTime = thisDate & " " & dbStandardTime

            SchoolBellDataSet.BellRingTimes(i).Bell_Time_Standard = replaceStandardTime
            SchoolBellDataSet.BellRingTimes(i).Bell_Time_Sortable = dbSortableTime

        Next

        Me.BellRingTimesTableAdapter.Update(Me.SchoolBellDataSet.BellRingTimes)
        Me.BellRingTimesTableAdapter.Fill(Me.SchoolBellDataSet.BellRingTimes)

End Sub

The part that makes this work is the formatting set up in the line above that start with "Dim dbSortableTime".

With that, it'll always be sortable but of course sorting the table is up to you. What I did was to do that part directly in the "fill" of the query:





You can see that I have it set as ascending based on that text field that's sortable.

To be sure it really was working - and not just showing the natural order which happens to already be in the correct order -  I next added a little routine to add some times before, during, and after the ones you showed:

Sub AddSomeNewTimes()

        SchoolBellDataSet.BellRingTimes.Rows.Add()
        Dim rowNum As Integer = SchoolBellDataSet.BellRingTimes.Rows.Count - 1
        SchoolBellDataSet.BellRingTimes(rowNum).Bell_Time_Standard = #5:34:00 AM#

        SchoolBellDataSet.BellRingTimes.Rows.Add()
        rowNum = SchoolBellDataSet.BellRingTimes.Rows.Count - 1
        SchoolBellDataSet.BellRingTimes(rowNum).Bell_Time_Standard = #10:39:00 AM#

        SchoolBellDataSet.BellRingTimes.Rows.Add()
        rowNum = SchoolBellDataSet.BellRingTimes.Rows.Count - 1
        SchoolBellDataSet.BellRingTimes(rowNum).Bell_Time_Standard = #8:16:00 PM#

        Me.BellRingTimesTableAdapter.Update(Me.SchoolBellDataSet.BellRingTimes)
        Me.BellRingTimesTableAdapter.Fill(Me.SchoolBellDataSet.BellRingTimes)

End Sub

I call both of these from the form load event in my example:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.BellRingTimesTableAdapter.Fill(Me.SchoolBellDataSet.BellRingTimes)

        AddSomeNewTimes()
        UpdateDataBaseWithSortableTimes()

End Sub

Finally now, the result that I got:



As you can see, it is sorted properly but like I said to start with, there's BOUND to be a better way than going through all this!




Anyway, for what it's worth. Good luck! :)

Answer 6

Thanks for helping :) i'll try

Answer 7

Thanks for helping :) i'll try

I woul creat timer code that runs and compare the time in your database, if the timenow is egual the time in the tabase, so , than the bell rings if not let know that in few minute the bell will ring. but test Frank code and see what happen. Al the best for you.

---

Don't judge me, just Upgrade me. Thanks!

Answer 8

Thanks for helping :) i'll try

Answer 9

I can comapre id with number example (10) and it shows 11, but with DateTime variables i have a problem

 with id, it works fine :)

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim sqlCnn As SqlConnection
        Dim sqlCmd As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim i As Integer
        Dim sql As String
        Dim bb As Integer
        Dim a(15) As Integer

        connetionString = "Data Source=.\sqlexpress;Initial Catalog=sk_db;Integrated Security=True"
        sql = "SELECT * FROM dbo.tb1"

        sqlCnn = New SqlConnection(connetionString)
        Try
            sqlCnn.Open()
            sqlCmd = New SqlCommand(sql, sqlCnn)
            adapter.SelectCommand = sqlCmd
            adapter.Fill(ds)

            For i = 0 To ds.Tables(0).Rows.Count - 1

                Do Until 10 < (ds.Tables(0).Rows(i).Item(0))
                    i = i + 1
                    bb = (ds.Tables(0).Rows(i).Item(0))
                Loop
            Next i

            TextBox1.Text = bb

            adapter.Dispose()
            sqlCmd.Dispose()
            sqlCnn.Close()
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try
    End Sub

with times it does not work

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim sqlCnn As SqlConnection
        Dim sqlCmd As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim i As Integer
        Dim sql As String
        Dim mm As DateTime
        Dim a(15) As DateTime

        mm = TimeOfDay.ToString("HH:mm:ss")

        connetionString = "Data Source=.\sqlexpress;Initial Catalog=sk_db;Integrated Security=True"
        sql = "SELECT id,CONVERT(varchar(35), tb1.Time1, 108) As Time1 FROM dbo.tb1 "

        sqlCnn = New SqlConnection(connetionString)
        Try
            sqlCnn.Open()
            sqlCmd = New SqlCommand(sql, sqlCnn)
            adapter.SelectCommand = sqlCmd
            adapter.Fill(ds)

            For i = 0 To ds.Tables(0).Rows.Count - 1


                Do Until mm.ToString("HH:mm:ss") < (ds.Tables(0).Rows(i).Item("Time1"))
                    mm = (ds.Tables(0).Rows(i).Item("Time1"))
                Loop

            Next i
            TextBox1.Text = mm.ToString("HH:mm:ss")

            adapter.Dispose()
            sqlCmd.Dispose()
            sqlCnn.Close()
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try
    End Sub
End Class

 Thenks for trying to help :)