Timer problem while updating DB

Hi everyone,

I got a problem with my timer and form here, what happened was, I'm trying to updating database from MYOB to MSSQL server, and I wanna a timer shown up while updating the database

here's my code in brief
   
-------------------------------------------------------------------------------------------------------------------------------------

    Public t = 0

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Timer1.Interval = 1000
        t = t + 1
        Label1.Text = gettime(t)    'gettime is a method to covert integer to time format
    End Sub

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

        AddHandler Timer1.Tick, AddressOf Timer1_Tick
        Timer1.Enabled = True

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        here is the code for updating database
        they should be alright

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       
        If error_str = "" Then
            result = "Qty Updated Successfully! Time used: " & gettime(t)
        Else
            result = error_str
        End If

        SendMail()

        Me.Close()

    End Sub

-------------------------------------------------------------------------------------------------------------------------------------

The code behaviour now is updating the database correctly, but no form nor timer shown up at all, and the email received says: Time used: 00:00:00.

Any ideas, guys?

Thanks

eezy2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike TomlinsonHigh School Computer Science, Computer Applications, and Mathematics TeachersCommented:
In your code:

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        here is the code for updating database
        they should be alright

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Is there a loop involved?

If so, your application cannot process the Timer events because it is "stuck" in your database looping code.

Add a:

    Application.DoEvents()

inside your loop.

If you don't have a loop then the code is a "blocking call" and you would need to place it in another thread for your Timer to work.
eezy2Author Commented:
yes, there's a loop inside,

and I tried to put application.doevents() inside the loop, here's the code

                Do Until myob_db.EOF
                    Application.DoEvents()
                    sql_db = New ADODB.Recordset
                    temp_productcode = myob_db.Fields("ItemNumber").Value
                    temp_qty = CDbl(myob_db.Fields("QuantityOnHand").Value - myob_db.Fields("SellOnOrder").Value)
                    sql_db.Open("Select qty_on_hand from products where code = '" & temp_productcode & "';", connsql, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)
                    If Not sql_db.EOF And Not sql_db.BOF Then
                        sql_db.Fields("qty_on_hand").Value = temp_qty
                        sql_db.Update()
                    End If
                    sql_db.Close()
                    sql_db = Nothing
                    myob_db.MoveNext()
                Loop

The good news is the timer is working now, cos the email says time used: 00:02:13,

but the form window is not shown up at all

why's that?

Mike TomlinsonHigh School Computer Science, Computer Applications, and Mathematics TeachersCommented:
Add these two lines as the first in your form load event:

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

        ' ... more code...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

eezy2Author Commented:
Thanks, Idle_mind

one more question if you can help,

i just added a another timer, so there are 2 timers now, and one got

Timer1.Interval = 1000

the other one is

Timer2.Interval = 25

Here's the begining of form_load

        Me.Show()
        Application.DoEvents()

        AddHandler Timer2.Tick, AddressOf Timer2_Tick
        Timer2.Enabled = True

        AddHandler Timer1.Tick, AddressOf Timer1_Tick
        Timer1.Enabled = True

It worked, and the only problem is the Timer2 seems to be much slower than it suppose to

Why's that?

Mike TomlinsonHigh School Computer Science, Computer Applications, and Mathematics TeachersCommented:
You can also change your Timer code to:

    Private startTime As DateTime

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Show()
        Application.DoEvents()
       
        startTime = DateTime.Now
        Timer1.Interval = 1000

        ' ... more code...

    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim ts As TimeSpan = DateTime.Now.Subtract(startTime)
        Label1.Text = ts.TotalMinutes.ToString("0") & ":" & ts.Seconds
    End Sub
Mike TomlinsonHigh School Computer Science, Computer Applications, and Mathematics TeachersCommented:
Timer2.Interval = 25

What are you trying to do every 25 milliseconds?  That is a very short interval...
Mike TomlinsonHigh School Computer Science, Computer Applications, and Mathematics TeachersCommented:
Change Label1.Text line to:

    Label1.Text = ts.TotalMinutes.ToString("0") & ":" & ts.Seconds.ToString("00")

eezy2Author Commented:

so is there anyway to fix the Timer2 with interval 25?

or any reason why it's so slow?

Mike TomlinsonHigh School Computer Science, Computer Applications, and Mathematics TeachersCommented:
"any reason why it's so slow?"

The Timer isn't really meant to fire that fast.  The running loop doesn't help matters since the Timer event cannot be processed until the DoEvents() call is hit and any messages in the pump before the timer event must get processed as well.

Why do you need such a fast interval?

Might I suggest you move your database update code into a different thread?
eezy2Author Commented:
yeh, actually, how do I suppose to put the db updating in another thread?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.