Results 1 to 9 of 9

Thread: Access DB and Timer not running tasks when its time - Conversion from string too

  1. #1

    Thread Starter
    Member snowcool776's Avatar
    Join Date
    Aug 2018
    Posts
    63

    Access DB and Timer not running tasks when its time - Conversion from string too

    All,
    I have a problem with my reminder app that will NOT run as scheduled when the program is open.
    It appears to only run once when started up and will not run when its time for a reminder.
    Can somebody please help me fix this in order to run in background when the scheduled reminder's time is the current time?
    The timer seems to be the problematic component, as seen with Tmrrmd.
    Here is my code for people who want to know. Pay close attention to tmrrmd.tick as this is where the problem originated, especially in where "If dt1 <= dt" is.
    The Reminder shows upon startup but NOT when the program is running. I want it to show when the reminder time is the current time, NOT when the program is starting up.
    Code:
      Private Sub tmrrmd_Tick(sender As Object, e As EventArgs) Handles tmrrmd.Tick
            Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\rmd.mdb")
    
                Dim command As New OleDbCommand("select * from rmd1", connection)
                Dim dt, dt1 As Date
                Dim recurr As Boolean
                Dim recurrweek As String
                Dim recurrdow As String
                Dim recurrby As String
                Dim reccurrmonth As String
                Dim active As Boolean
                Dim recurrmonthly As Boolean
                Dim recurrweekly As Boolean
                Dim recurrdaily As Boolean
                Dim monthofday As Integer
                dt = Format(DateTime.Now, "hh:mm:ss tt").ToString
    
                connection.Open()
    
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
                    recurr = reader(3)
    
                    reccurrmonth = reader(2).ToString
    
                    recurrby = reader(4).ToString
                    recurrweek = reader(5).ToString
                    active = reader(6)
                    dt1 = reader(7).ToString()
                    If dt >= dt1 Then
    
                        If recurr = True Then
    
    
    
                            If active = True Then
                            Else
                                Exit Sub
                            End If
                            Select Case reccurrmonth
                                Case 0
                                    monthofday = 1
                                Case 1
                                    monthofday = 2
                                Case 2
                                    monthofday = 3
                                Case 3
                                    monthofday = 4
                                Case 4
                                    monthofday = 5
                                Case 5
                                    monthofday = 6
                                Case 6
                                    monthofday = 7
                                Case 7
                                    monthofday = 8
                                Case 8
                                    monthofday = 9
                                Case 9
                                    monthofday = 10
                                Case 10
                                    monthofday = 11
                                Case 11
                                    monthofday = 12
                            End Select
                            Select Case recurrweek
                                Case 0
                                    recurrdow = DayOfWeek.Sunday
                                Case 1
                                    recurrdow = DayOfWeek.Monday
                                Case 2
                                    recurrdow = DayOfWeek.Tuesday
                                Case 3
                                    recurrdow = DayOfWeek.Wednesday
                                Case 4
                                    recurrdow = DayOfWeek.Thursday
                                Case 5
                                    recurrdow = DayOfWeek.Friday
                                Case 6
                                    recurrdow = DayOfWeek.Saturday
                                Case 7
                                    recurrdow = Nothing
    
    
                            End Select
                            Select Case recurrby
                                Case 0
                                    recurrdaily = True
                                    recurrmonthly = False
                                    recurrweekly = False
                                Case 1
                                    recurrweekly = True
                                    recurrdaily = False
                                    recurrmonthly = False
                                Case 2
                                    recurrmonthly = True
                                    recurrdaily = False
                                    recurrweekly = False
                            End Select
    
                            If recurrdaily = True Then
                                Tmrrmda.Start()
                            ElseIf recurrweekly = True Then
                                If Now.DayOfWeek = recurrdow Then
                                    Tmrrmda.Start()
                                End If
                            ElseIf recurrmonthly = True Then
                                If Now.Month = monthofday Then
                                    Tmrrmda.Start()
                                End If
                            End If
    
                        ElseIf recurr = False Then
                            Tmrrmda.Start()
                        End If
    
                        Tmrrmda.Start()
    
                    End If
    
    
    
    
                End While
    
            End Using
    
    
    
    
        End Sub
    
        Private Sub Tmrrmda_Tick(sender As Object, e As EventArgs) Handles Tmrrmda.Tick
            Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\rmd.mdb")
    
                Dim command As New OleDbCommand("select * from rmd1", connection)
                Dim dt, dt1 As Date
    
                dt = Format(DateTime.Now, "hh:mm:ss tt").ToString
    
                connection.Open()
    
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
    
                    dt1 = reader(7).ToString()
    
                    Me.TextBox1.Text += "Task:" + reader(1).ToString() + "Date:" + dt1 + vbNewLine
    
                        ReminderForm.PictureBox1.Image = Image.FromFile(reader(8).ToString)
                        ReminderForm.lbltime.Text = dt1
                        ReminderForm.Label1.Text = reader(1).ToString
                        ReminderForm.Show()
                        Tmrrmda.Stop()
    
    
    
    
    
    
    
    
                End While
    
            End Using
        End Sub
    Thanks
    Last edited by snowcool776; Jul 24th, 2021 at 12:36 PM. Reason: Added more info on where people should look based on JMC comment.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Access DB and Timer not running tasks when its time - Conversion from string too

    There's a lot of code there and you've given us no indication of where we should be looking. That suggests that you have made no attempt to debug your code for yourself, which you should always do before posting. You should be able to point out to use EXACTLY where and how the behaviour of that code differs from what you expect. If you don't know that, find out. That's what the debugger is for. If you use the debugger then you may even find that you can solve the problem yourself but, if you can't, you can provide us with far more specific information.

  3. #3

    Thread Starter
    Member snowcool776's Avatar
    Join Date
    Aug 2018
    Posts
    63

    Re: Access DB and Timer not running tasks when its time - Conversion from string too

    Quote Originally Posted by jmcilhinney View Post
    There's a lot of code there and you've given us no indication of where we should be looking. That suggests that you have made no attempt to debug your code for yourself, which you should always do before posting. You should be able to point out to use EXACTLY where and how the behaviour of that code differs from what you expect. If you don't know that, find out. That's what the debugger is for. If you use the debugger then you may even find that you can solve the problem yourself but, if you can't, you can provide us with far more specific information.
    Sorry about that.
    You should be looking at the tmrrmd sub as this is the problematic file.
    thanks

  4. #4

    Thread Starter
    Member snowcool776's Avatar
    Join Date
    Aug 2018
    Posts
    63

    Re: Access DB and Timer not running tasks when its time - Conversion from string too

    Quote Originally Posted by jmcilhinney View Post
    There's a lot of code there and you've given us no indication of where we should be looking. That suggests that you have made no attempt to debug your code for yourself, which you should always do before posting. You should be able to point out to use EXACTLY where and how the behaviour of that code differs from what you expect. If you don't know that, find out. That's what the debugger is for. If you use the debugger then you may even find that you can solve the problem yourself but, if you can't, you can provide us with far more specific information.
    Updated post to reflect this.
    Thanks JMC for notifying me on this!

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Access DB and Timer not running tasks when its time - Conversion from string too

    Don't EVER convert anything that is not a String into a String unless you specifically need a String, e.g. for display. If you have DateTime values and you want to use the time portion, there's a TimeOfDay property that returns a TimeSpan. That is what you use. If you are getting DateTime values from the database then you use the TimeOfDay property of that too, e.g.
    vb.net Code:
    1. Dim currentTime As TimeSpan = Date.Now.TimeOfDay
    2.  
    3. While myDataReader.Read()
    4.     Dim reminderTime As TimeSpan = myDataReader.GetDateTime(myDataReader.GetOrdinal("ReminderTime")).TimeOfDay
    5.  
    6.     If reminderTime < currentTime Then
    7.         'Reminder has passed.
    8.     Else
    9.         'Reminder in the future.
    10.     End If
    11. End While
    If you are using a database data type that returns TimeSpan values then you get them as that type:
    vb.net Code:
    1. Dim currentTime As TimeSpan = Date.Now.TimeOfDay
    2.  
    3. While myDataReader.Read()
    4.     Dim reminderTime As TimeSpan = myDataReader.GetTimeSpan(myDataReader.GetOrdinal("ReminderTime"))
    5.  
    6.     If reminderTime < currentTime Then
    7.         'Reminder has passed.
    8.     Else
    9.         'Reminder in the future.
    10.     End If
    11. End While
    There are a number of points to note here:

    1. Sensible, descriptive variable names.
    2. Appropriate data types.
    3. Variables declared where needed, with narrowest scope possible.
    4. Data reader methods used to get data as its actual type rather than as an Object reference.
    5. Data retrieved by column name rather than index.

    All this makes your code more readable, less error-prone and kore robust. You should fix those points throughout your code, including changing database data types if appropriate. Access has a data type dedicated to dates and times and, if I'm not mistaken, you can configure columns of that type to store just times. If it's only times you care about, that's what you should use.

    Once you have made those changes, debug your code properly, i.e. by setting a breakpoint and stepping through code line by line, examining the state at each step. You should know EXACTLY what you expect to happen at every step, including what the values of all variables should be. You need to compare your expectations to reality at every step. You will then be able to identify EXACTLY where and how reality differs from those expectations and, if you still need help, describe that to use. That means something like "on the third iteration of the loop, variable X should be value Y but is actually value Z". Etc.

  6. #6

    Thread Starter
    Member snowcool776's Avatar
    Join Date
    Aug 2018
    Posts
    63

    Re: Access DB and Timer not running tasks when its time - Conversion from string too

    Quote Originally Posted by jmcilhinney View Post
    Don't EVER convert anything that is not a String into a String unless you specifically need a String, e.g. for display. If you have DateTime values and you want to use the time portion, there's a TimeOfDay property that returns a TimeSpan. That is what you use. If you are getting DateTime values from the database then you use the TimeOfDay property of that too, e.g.
    vb.net Code:
    1. Dim currentTime As TimeSpan = Date.Now.TimeOfDay
    2.  
    3. While myDataReader.Read()
    4.     Dim reminderTime As TimeSpan = myDataReader.GetDateTime(myDataReader.GetOrdinal("ReminderTime")).TimeOfDay
    5.  
    6.     If reminderTime < currentTime Then
    7.         'Reminder has passed.
    8.     Else
    9.         'Reminder in the future.
    10.     End If
    11. End While
    If you are using a database data type that returns TimeSpan values then you get them as that type:
    vb.net Code:
    1. Dim currentTime As TimeSpan = Date.Now.TimeOfDay
    2.  
    3. While myDataReader.Read()
    4.     Dim reminderTime As TimeSpan = myDataReader.GetTimeSpan(myDataReader.GetOrdinal("ReminderTime"))
    5.  
    6.     If reminderTime < currentTime Then
    7.         'Reminder has passed.
    8.     Else
    9.         'Reminder in the future.
    10.     End If
    11. End While
    There are a number of points to note here:

    1. Sensible, descriptive variable names.
    2. Appropriate data types.
    3. Variables declared where needed, with narrowest scope possible.
    4. Data reader methods used to get data as its actual type rather than as an Object reference.
    5. Data retrieved by column name rather than index.

    All this makes your code more readable, less error-prone and kore robust. You should fix those points throughout your code, including changing database data types if appropriate. Access has a data type dedicated to dates and times and, if I'm not mistaken, you can configure columns of that type to store just times. If it's only times you care about, that's what you should use.

    Once you have made those changes, debug your code properly, i.e. by setting a breakpoint and stepping through code line by line, examining the state at each step. You should know EXACTLY what you expect to happen at every step, including what the values of all variables should be. You need to compare your expectations to reality at every step. You will then be able to identify EXACTLY where and how reality differs from those expectations and, if you still need help, describe that to use. That means something like "on the third iteration of the loop, variable X should be value Y but is actually value Z". Etc.
    Sadly, it does not work.
    I debugged and it only repeated once, and it will not run when the time is the same as the reminder time.
    I want this to repeat every 1000 milliseconds to check this, not one time.
    Code:
      Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\rmd.mdb")
    
                Dim command As New OleDbCommand("select * from rmd1", connection)
                Dim dt, dt1 As Date
                Dim recurr As Boolean
                Dim recurrweek As String
                Dim recurrdow As String
                Dim recurrby As String
                Dim reccurrmonth As String
                Dim active As Boolean
                Dim recurrmonthly As Boolean
                Dim recurrweekly As Boolean
                Dim recurrdaily As Boolean
                Dim monthofday As Integer
                dt = Format(DateTime.Now, "hh:mm:ss tt").ToString
    
                connection.Open()
    
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
                    recurr = reader(3)
    
                reccurrmonth = reader(2).ToString
    
                    recurrby = reader(4).ToString
                    recurrweek = reader(5).ToString
                    active = reader(6)
    
                    Dim currentTime As TimeSpan = Date.Now.TimeOfDay
    
    
                    Dim reminderTime As TimeSpan = reader.GetDateTime(reader.GetOrdinal("time")).TimeOfDay
    
                    If reminderTime < currentTime Then
                            If recurr = True Then
    
    
    
                                If active = True Then
                                Else
                                    Exit Sub
                                End If
                                Select Case reccurrmonth
                                    Case 0
                                        monthofday = 1
                                    Case 1
                                        monthofday = 2
                                    Case 2
                                        monthofday = 3
                                    Case 3
                                        monthofday = 4
                                    Case 4
                                        monthofday = 5
                                    Case 5
                                        monthofday = 6
                                    Case 6
                                        monthofday = 7
                                    Case 7
                                        monthofday = 8
                                    Case 8
                                        monthofday = 9
                                    Case 9
                                        monthofday = 10
                                    Case 10
                                        monthofday = 11
                                    Case 11
                                        monthofday = 12
                                End Select
                                Select Case recurrweek
                                    Case 0
                                        recurrdow = DayOfWeek.Sunday
                                    Case 1
                                        recurrdow = DayOfWeek.Monday
                                    Case 2
                                        recurrdow = DayOfWeek.Tuesday
                                    Case 3
                                        recurrdow = DayOfWeek.Wednesday
                                    Case 4
                                        recurrdow = DayOfWeek.Thursday
                                    Case 5
                                        recurrdow = DayOfWeek.Friday
                                    Case 6
                                        recurrdow = DayOfWeek.Saturday
                                    Case 7
                                        recurrdow = Nothing
    
    
                                End Select
                                Select Case recurrby
                                    Case 0
                                        recurrdaily = True
                                        recurrmonthly = False
                                        recurrweekly = False
                                    Case 1
                                        recurrweekly = True
                                        recurrdaily = False
                                        recurrmonthly = False
                                    Case 2
                                        recurrmonthly = True
                                        recurrdaily = False
                                        recurrweekly = False
                                End Select
    
                            If recurrdaily = True Then
    
                            ElseIf recurrweekly = True Then
                                If Now.DayOfWeek = recurrdow Then
    
                                End If
                            ElseIf recurrmonthly = True Then
                                If Now.Month = monthofday Then
    
                                End If
                            Else
                                Exit Sub
                            End If
    
                            ElseIf recurr = False Then
                            Exit Sub
                        End If
                            Me.TextBox1.Text += "Task:" + reader(1).ToString() + "Date:" + dt1 + vbNewLine
    
                            ReminderForm.PictureBox1.Image = Image.FromFile(reader(8).ToString)
                            ReminderForm.lbltime.Text = dt1
                            ReminderForm.Label1.Text = reader(1).ToString
                            ReminderForm.Show()
    
                    Else
                        'Reminder in the future.
                        Exit Sub
                    End If
                    End While
    
            End Using

  7. #7

    Thread Starter
    Member snowcool776's Avatar
    Join Date
    Aug 2018
    Posts
    63

    Re: Access DB and Timer not running tasks when its time - Conversion from string too

    Quote Originally Posted by snowcool776 View Post
    Sadly, it does not work.
    I debugged and it only repeated once, and it will not run when the time is the same as the reminder time.
    I want this to repeat every 1000 milliseconds to check this, not one time.
    Code:
      Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\rmd.mdb")
    
                Dim command As New OleDbCommand("select * from rmd1", connection)
                Dim dt, dt1 As Date
                Dim recurr As Boolean
                Dim recurrweek As String
                Dim recurrdow As String
                Dim recurrby As String
                Dim reccurrmonth As String
                Dim active As Boolean
                Dim recurrmonthly As Boolean
                Dim recurrweekly As Boolean
                Dim recurrdaily As Boolean
                Dim monthofday As Integer
                dt = Format(DateTime.Now, "hh:mm:ss tt").ToString
    
                connection.Open()
    
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
                    recurr = reader(3)
    
                reccurrmonth = reader(2).ToString
    
                    recurrby = reader(4).ToString
                    recurrweek = reader(5).ToString
                    active = reader(6)
    
                    Dim currentTime As TimeSpan = Date.Now.TimeOfDay
    
    
                    Dim reminderTime As TimeSpan = reader.GetDateTime(reader.GetOrdinal("time")).TimeOfDay
    
                    If reminderTime < currentTime Then
                            If recurr = True Then
    
    
    
                                If active = True Then
                                Else
                                    Exit Sub
                                End If
                                Select Case reccurrmonth
                                    Case 0
                                        monthofday = 1
                                    Case 1
                                        monthofday = 2
                                    Case 2
                                        monthofday = 3
                                    Case 3
                                        monthofday = 4
                                    Case 4
                                        monthofday = 5
                                    Case 5
                                        monthofday = 6
                                    Case 6
                                        monthofday = 7
                                    Case 7
                                        monthofday = 8
                                    Case 8
                                        monthofday = 9
                                    Case 9
                                        monthofday = 10
                                    Case 10
                                        monthofday = 11
                                    Case 11
                                        monthofday = 12
                                End Select
                                Select Case recurrweek
                                    Case 0
                                        recurrdow = DayOfWeek.Sunday
                                    Case 1
                                        recurrdow = DayOfWeek.Monday
                                    Case 2
                                        recurrdow = DayOfWeek.Tuesday
                                    Case 3
                                        recurrdow = DayOfWeek.Wednesday
                                    Case 4
                                        recurrdow = DayOfWeek.Thursday
                                    Case 5
                                        recurrdow = DayOfWeek.Friday
                                    Case 6
                                        recurrdow = DayOfWeek.Saturday
                                    Case 7
                                        recurrdow = Nothing
    
    
                                End Select
                                Select Case recurrby
                                    Case 0
                                        recurrdaily = True
                                        recurrmonthly = False
                                        recurrweekly = False
                                    Case 1
                                        recurrweekly = True
                                        recurrdaily = False
                                        recurrmonthly = False
                                    Case 2
                                        recurrmonthly = True
                                        recurrdaily = False
                                        recurrweekly = False
                                End Select
    
                            If recurrdaily = True Then
    
                            ElseIf recurrweekly = True Then
                                If Now.DayOfWeek = recurrdow Then
    
                                End If
                            ElseIf recurrmonthly = True Then
                                If Now.Month = monthofday Then
    
                                End If
                            Else
                                Exit Sub
                            End If
    
                            ElseIf recurr = False Then
                            Exit Sub
                        End If
                            Me.TextBox1.Text += "Task:" + reader(1).ToString() + "Date:" + dt1 + vbNewLine
    
                            ReminderForm.PictureBox1.Image = Image.FromFile(reader(8).ToString)
                            ReminderForm.lbltime.Text = dt1
                            ReminderForm.Label1.Text = reader(1).ToString
                            ReminderForm.Show()
    
                    Else
                        'Reminder in the future.
                        Exit Sub
                    End If
                    End While
    
            End Using
    Attachment 181918
    The reminder windows does not show when the time is currently the same as the reminder.

  8. #8
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Access DB and Timer not running tasks when its time - Conversion from string too

    It all seems to be keying off of data from your database, which obviously none of us have access to to see if things look "proper".

    Debugging - ok, you think it should do x, it is doing y. At what line of code does reality stray from your expectation? THAT is the key to debugging. We don't have your data, and therefore mentally stepping through your code is problematic.

    I can't offer any further assistance, if you can even call what I've said in this post assistance.

    Good luck.

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Access DB and Timer not running tasks when its time - Conversion from string too

    use a query and narrow it down to an hour perhaps?

    this is what the query could look like
    Code:
    SELECT tbl_Ticket.TicketBought, Format([tbl_Ticket].[TicketBought],"hh:nn:ss") AS MyTime, *
    FROM tbl_Ticket
    WHERE (((tbl_Ticket.TicketBought) Between DateAdd("h",-1,Now()) And DateAdd("h",+1,Now())));
    and a Image of the Table and the result of the query after execute, which returns 1 Record because it is in that Hour
    Name:  Ticket.jpg
Views: 168
Size:  32.2 KB

    checking every 1000ms is crazy
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width