asked on

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

Mike Tomlinson

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.

eezy2

ASKER

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?

ASKER CERTIFIED SOLUTION

Mike Tomlinson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eezy2

ASKER

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 Tomlinson

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 Tomlinson

Timer2.Interval = 25

What are you trying to do every 25 milliseconds? That is a very short interval...

Mike Tomlinson

Change Label1.Text line to:

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

eezy2

ASKER

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

or any reason why it's so slow?

Mike Tomlinson

"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?

eezy2

ASKER

yeh, actually, how do I suppose to put the db updating in another thread?