Elapsed time for retriving data from database

[wut-da]

Hye, im new here. Need some help on Vb6 coding.
I've look up some of the treads on timing in this site but i have problem implementing it.

Public CHours As Variant
Public CMins As Variant
Public CSecs As Variant
Public CMSecs As Variant

Dim StrNested(4) As String
------------------------------------------------------------
Private Sub cmdSQL_Click()
 
On Error Resume Next
Adodc1.RecordSource = txtSQL.Text
Adodc1.Refresh

End Sub
-----------------------------------------------------------
Private Sub Combo1_Click()
Label1.Caption = Combo1.List(Combo1.ListIndex)
txtSQL.Text = StrNested(Combo1.ListIndex)
txtSQL.Enabled = False

End Sub
------------------------------------------------------
Private Sub DataGrid1_Click()
Adodc1.Refresh
    Timer_1.Enabled = False
End Sub
-------------------------------------------------------

Private Sub Form_Load()

Text1.Text = "00:00:00:00"
Timer_1.Enabled = True

  
StrNested(0) = "SELECT * from products"
StrNested(1) = "SELECT * from suppliers"
StrNested(2) = "SELECT * from orders"
StrNested(3) = "SELECT * from customers "
StrNested(4) = "SELECT ProductID, ProductName From Products"

Combo1.AddItem "Nested Loop"
Combo1.AddItem "Nested Loop(with Option) "
Combo1.AddItem "Hash Join"
Combo1.AddItem "Merge Join"
Combo1.AddItem "NEW"

End Sub
-------------------------------------------------------
 
Private Sub Timer_1_Timer()

parts = Split(Text1.Text, ":")
CHours = CInt(parts(0))
CMins = CInt(parts(1))
CSecs = CInt(parts(2))
CMSecs = CInt(parts(3))

If CSecs >= 10 Then
    CMins = CMins + 1
    CSecs = 0
Else
    CSecs = CSecs + 1
End If

If CMins >= 60 Then
    CHours = CHours + 1
    CMins = 0
End If

If CHours < 10 Then CHours = "0" & CHours
If CMins < 10 Then CMins = "0" & CMins
If CSecs < 10 Then CSecs = "0" & CSecs

Text1.Text = CHours & ":" & CMins & ":" & CSecs & ":" CMSecs

End Sub

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

I want the timer to start when i press the EXECUTE button(cmdSQL) , and start counting from miliseconds and stop after the result being displayed.
We are able to start the timer after pressing the EXECUTE button but it won't stop even after the data has been retrieve from the database.

We have been using MS SQL Server2000.

Thank you in advanced. :)

[Comatose]

Well, I see you setting the timer to false in the datagrid click event. Probably not a good idea, unless you plan to click the grid every time it loads a query result. I suggest that you add the timer1.enabled = false to the form load, after your last combo1.additem.

[wut-da]

it still won't work, this is the new code i've tried, instead of datagrid_click i use _change:-

Dim StrNested(4) As String
 
Public CMins As Variant
Public CSecs As Variant
Public CMSecs As Variant
-----------------------------------------------------
 
Private Sub cmdSQL_Click()
cmdSQL.Caption = "EXECUTE"
Timer1.Enabled = True
 
On Error Resume Next
Adodc1.RecordSource = txtSQL.Text
Adodc1.Refresh
 
End Sub
 
-----------------------------------------------------
Private Sub Combo1_Click()
 
Label1.Caption = Combo1.List(Combo1.ListIndex)
txtSQL.Text = StrNested(Combo1.ListIndex)
txtSQL.Enabled = False
 
End Sub
----------------------------------------------------------------
Private Sub DataGrid1_AfterUpdate()
Timer1.Enabled = False
End Sub
----------------------------------------------------------------
Private Sub Form_Load()
 
Text1.Text = "00:00:00"
Timer1.Enabled = True
 
StrNested(0) = "SELECT pdN.ProductID, pdN.ProductName, spN.CompanyName, spN.ContactName FROM dbo.ProductsNew pdN INNER JOIN dbo.SuppliersNew spN ON pdN.SupplierId = spN.SupplierId"
StrNested(1) = "SELECT ProductID, ProductName, CompanyName, ContactName FROM dbo.ProductsNew JOIN dbo.SuppliersNew ON ProductsNew.SupplierId = SuppliersNew.SupplierId OPTION (LOOP JOIN)"
StrNested(2) = "SELECT ProductID, ProductName, CompanyName, ContactName From dbo.ProductsNew, dbo.SuppliersNew WHERE ProductsNew.SupplierId = SuppliersNew.SupplierId"
StrNested(3) = "SELECT ProductID, ProductName, CompanyName, ContactName From ProductsNew Join SuppliersNew ON ProductsNew.SupplierId = SuppliersNew.SupplierId OPTION (MERGE JOIN)"
StrNested(4) = "SELECT * From Suppliers WHERE SupplierID >=20"
 
Combo1.AddItem "Nested Loop"
Combo1.AddItem "Nested Loop(with Option) "
Combo1.AddItem "Hash Join"
Combo1.AddItem "Merge Join"
Combo1.AddItem "NEW"
 
Timer1.Enabled = False
End Sub
-------------------------------------------------------------------------
Private Sub Timer1_Timer()
parts = Split(Text1.Text, ":")
CMins = CInt(parts(0))
CSecs = CInt(parts(1))
CMSecs = CInt(parts(2))
 
If CMSecs >= 10 Then
CSecs = CSecs + 1
CMSecs = 0
Else
CMSecs = CMSecs + 1
End If
 
If CSecs >= 60 Then
CMins = CMins + 1
CSecs = 0
End If
 
If CMins < 10 Then CMins = "0" & CMins
If CSecs < 10 Then CSecs = "0" & CSecs
If CMSecs < 10 Then CMSecs = "0" & CMSecs
 
Text1.Text = CMins & ":" & CSecs & ":" & CMSecs
 
 
End Sub

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

and another problem i encountered is that, the mseconds suppose to run fast as in the example in this thread the stop watch, but after execution it moves like seconds. Do u know why is this happening? Or it will be great if u can suggest any other way to get the elapsed time while retrieving the data. Thank You! :)

[wut-da]

i am able to solve the problem now, i use the end time - start time. may i know the format for time in visual basic. this is what i found example:-
Format(dtmEnd, "hh:mm:ss"), if i want to display the miliseconds what should i put? thank u in advance ;)

[wut-da]

i post up my current code, so u could let me know on how i will be able to set the milliseconds.

Dim StrNested(4) As String
Dim dtmEnd As Date, dtmstart As Date, dtmTotalTime As Date

-----------------------------------------------------------------------------------
Private Sub cmdSQL_Click()
dtmstart = Time
Text2.Text = Format(dtmstart, "hh:mm:ss")
On Error Resume Next
Adodc1.RecordSource = txtSQL.Text
Adodc1.Refresh
dtmEnd = Time
Text3.Text = Format(dtmEnd, "hh:mm:ss")
dtmTotalTime = dtmEnd - dtmstart
Text1.Text = Format(dtmTotalTime, "hh:mm:ss")

End Sub

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

Private Sub Combo1_Click()

Label1.Caption = Combo1.List(Combo1.ListIndex)
txtSQL.Text = StrNested(Combo1.ListIndex)
txtSQL.Enabled = False

End Sub

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

Private Sub Form_Load()
dtmstart = Empty
dtmEnd = Empty
Text1.Text = "00:00:00:00"

  
StrNested(0) = "SELECT pdN.ProductID, pdN.ProductName, spN.CompanyName, spN.ContactName FROM dbo.ProductsNew pdN INNER JOIN dbo.SuppliersNew spN ON pdN.SupplierId = spN.SupplierId"
StrNested(1) = "SELECT ProductID, ProductName, CompanyName, ContactName FROM dbo.ProductsNew JOIN dbo.SuppliersNew ON ProductsNew.SupplierId = SuppliersNew.SupplierId OPTION (LOOP JOIN)"
StrNested(2) = "SELECT ProductID, ProductName, CompanyName, ContactName From dbo.ProductsNew, dbo.SuppliersNew WHERE ProductsNew.SupplierId = SuppliersNew.SupplierId"
StrNested(3) = "SELECT ProductID, ProductName, CompanyName, ContactName From ProductsNew Join SuppliersNew ON ProductsNew.SupplierId = SuppliersNew.SupplierId OPTION (MERGE JOIN)"
StrNested(4) = "SELECT * From Suppliers WHERE SupplierID >=20"
                
Combo1.AddItem "Nested Loop"
Combo1.AddItem "Nested Loop(with Option) "
Combo1.AddItem "Hash Join"
Combo1.AddItem "Merge Join"
Combo1.AddItem "NEW"


End Sub

[Comatose]

I think your best bet is to read this thread:
http://www.daniweb.com/techtalkforums/showthread.php?t=17352&highlight=Time

[wut-da]

i've read the thread u posted, and it seems like u've been using the GetSystemTime APi, function. i've tried implementing it, but the problem now is, how can i calculate the time difference. i managed to get both the start n end time. but it seems like, it is impossible to get the time difference. i've read somewhere that, GetSystemTime doesn't return value.

Private Declare Sub GetLocalTime Lib "kernel32.dll" (lpSystemTime As SYSTEMTIME)

Private Type SYSTEMTIME
  wYear As Integer
  wMonth As Integer
  wDayOfWeek As Integer
  wDay As Integer
  wHour As Integer
  wMinute As Integer
  wSecond As Integer
  wMilliseconds As Integer
End Type

Dim gtime As SYSTEMTIME
Dim gtime2 As SYSTEMTIME

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

Private Sub cmdSQL_Click()
GetLocalTime gtime

Text2.Text = gtime.wHour & ":" & gtime.wMinute & ":" & gtime.wSecond & ":" & gtime.wMilliseconds
    
On Error Resume Next
Adodc1.RecordSource = txtSQL.Text
Adodc1.Refresh


GetLocalTime gtime2
Text3.Text = gtime2.wHour & ":" & gtime2.wMinute & ":" & gtime2.wSecond & ":" & gtime2.wMilliseconds

'i wish to display the result of the time difference here
Text1.Text = dtmTotalTime
End Sub

hope u can help me :)