944,066 Members | Top Members by Rank

Software Development > Visual Basic 4 / 5 / 6 > Elapsed time for retriving data from database
Ad:
Permalink
Apr 27th, 2006
0

Elapsed time for retriving data from database

Expand Post »
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.
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. Public CHours As Variant
  2. Public CMins As Variant
  3. Public CSecs As Variant
  4. Public CMSecs As Variant
  5.  
  6. Dim StrNested(4) As String
  7. ------------------------------------------------------------
  8. Private Sub cmdSQL_Click()
  9.  
  10. On Error Resume Next
  11. Adodc1.RecordSource = txtSQL.Text
  12. Adodc1.Refresh
  13.  
  14. End Sub
  15. -----------------------------------------------------------
  16. Private Sub Combo1_Click()
  17. Label1.Caption = Combo1.List(Combo1.ListIndex)
  18. txtSQL.Text = StrNested(Combo1.ListIndex)
  19. txtSQL.Enabled = False
  20.  
  21. End Sub
  22. ------------------------------------------------------
  23. Private Sub DataGrid1_Click()
  24. Adodc1.Refresh
  25.     Timer_1.Enabled = False
  26. End Sub
  27. -------------------------------------------------------
  28.  
  29. Private Sub Form_Load()
  30.  
  31. Text1.Text = "00:00:00:00"
  32. Timer_1.Enabled = True
  33.  
  34.  
  35. StrNested(0) = "SELECT * from products"
  36. StrNested(1) = "SELECT * from suppliers"
  37. StrNested(2) = "SELECT * from orders"
  38. StrNested(3) = "SELECT * from customers "
  39. StrNested(4) = "SELECT ProductID, ProductName From Products"
  40.  
  41. Combo1.AddItem "Nested Loop"
  42. Combo1.AddItem "Nested Loop(with Option) "
  43. Combo1.AddItem "Hash Join"
  44. Combo1.AddItem "Merge Join"
  45. Combo1.AddItem "NEW"
  46.  
  47. End Sub
  48. -------------------------------------------------------
  49.  
  50. Private Sub Timer_1_Timer()
  51.  
  52. parts = Split(Text1.Text, ":")
  53. CHours = CInt(parts(0))
  54. CMins = CInt(parts(1))
  55. CSecs = CInt(parts(2))
  56. CMSecs = CInt(parts(3))
  57.  
  58. If CSecs >= 10 Then
  59.     CMins = CMins + 1
  60.     CSecs = 0
  61. Else
  62.     CSecs = CSecs + 1
  63. End If
  64.  
  65. If CMins >= 60 Then
  66.     CHours = CHours + 1
  67.     CMins = 0
  68. End If
  69.  
  70. If CHours < 10 Then CHours = "0" & CHours
  71. If CMins < 10 Then CMins = "0" & CMins
  72. If CSecs < 10 Then CSecs = "0" & CSecs
  73.  
  74. Text1.Text = CHours & ":" & CMins & ":" & CSecs & ":" CMSecs
  75.  
  76. 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.
Last edited by Comatose; Apr 27th, 2006 at 7:16 am.
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
wut-da is offline Offline
5 posts
since Jul 2004
Permalink
Apr 27th, 2006
0

Re: Elapsed time for retriving data from database

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.
Team Colleague
Reputation Points: 361
Solved Threads: 214
Taboo Programmer
Comatose is offline Offline
2,413 posts
since Dec 2004
Permalink
Apr 28th, 2006
0

Re: Elapsed time for retriving data from database

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

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. Dim StrNested(4) As String
  2.  
  3. Public CMins As Variant
  4. Public CSecs As Variant
  5. Public CMSecs As Variant
  6. -----------------------------------------------------
  7.  
  8. Private Sub cmdSQL_Click()
  9. cmdSQL.Caption = "EXECUTE"
  10. Timer1.Enabled = True
  11.  
  12. On Error Resume Next
  13. Adodc1.RecordSource = txtSQL.Text
  14. Adodc1.Refresh
  15.  
  16. End Sub
  17.  
  18. -----------------------------------------------------
  19. Private Sub Combo1_Click()
  20.  
  21. Label1.Caption = Combo1.List(Combo1.ListIndex)
  22. txtSQL.Text = StrNested(Combo1.ListIndex)
  23. txtSQL.Enabled = False
  24.  
  25. End Sub
  26. ----------------------------------------------------------------
  27. Private Sub DataGrid1_AfterUpdate()
  28. Timer1.Enabled = False
  29. End Sub
  30. ----------------------------------------------------------------
  31. Private Sub Form_Load()
  32.  
  33. Text1.Text = "00:00:00"
  34. Timer1.Enabled = True
  35.  
  36. 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"
  37. StrNested(1) = "SELECT ProductID, ProductName, CompanyName, ContactName FROM dbo.ProductsNew JOIN dbo.SuppliersNew ON ProductsNew.SupplierId = SuppliersNew.SupplierId OPTION (LOOP JOIN)"
  38. StrNested(2) = "SELECT ProductID, ProductName, CompanyName, ContactName From dbo.ProductsNew, dbo.SuppliersNew WHERE ProductsNew.SupplierId = SuppliersNew.SupplierId"
  39. StrNested(3) = "SELECT ProductID, ProductName, CompanyName, ContactName From ProductsNew Join SuppliersNew ON ProductsNew.SupplierId = SuppliersNew.SupplierId OPTION (MERGE JOIN)"
  40. StrNested(4) = "SELECT * From Suppliers WHERE SupplierID >=20"
  41.  
  42. Combo1.AddItem "Nested Loop"
  43. Combo1.AddItem "Nested Loop(with Option) "
  44. Combo1.AddItem "Hash Join"
  45. Combo1.AddItem "Merge Join"
  46. Combo1.AddItem "NEW"
  47.  
  48. Timer1.Enabled = False
  49. End Sub
  50. -------------------------------------------------------------------------
  51. Private Sub Timer1_Timer()
  52. parts = Split(Text1.Text, ":")
  53. CMins = CInt(parts(0))
  54. CSecs = CInt(parts(1))
  55. CMSecs = CInt(parts(2))
  56.  
  57. If CMSecs >= 10 Then
  58. CSecs = CSecs + 1
  59. CMSecs = 0
  60. Else
  61. CMSecs = CMSecs + 1
  62. End If
  63.  
  64. If CSecs >= 60 Then
  65. CMins = CMins + 1
  66. CSecs = 0
  67. End If
  68.  
  69. If CMins < 10 Then CMins = "0" & CMins
  70. If CSecs < 10 Then CSecs = "0" & CSecs
  71. If CMSecs < 10 Then CMSecs = "0" & CMSecs
  72.  
  73. Text1.Text = CMins & ":" & CSecs & ":" & CMSecs
  74.  
  75.  
  76. 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!
Last edited by cscgal; Apr 29th, 2006 at 11:06 am.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
wut-da is offline Offline
5 posts
since Jul 2004
Permalink
Apr 28th, 2006
0

Re: Elapsed time for retriving data from database

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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
wut-da is offline Offline
5 posts
since Jul 2004
Permalink
Apr 28th, 2006
0

Re: Elapsed time for retriving data from database

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

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. Dim StrNested(4) As String
  2. Dim dtmEnd As Date, dtmstart As Date, dtmTotalTime As Date
  3.  
  4. -----------------------------------------------------------------------------------
  5. Private Sub cmdSQL_Click()
  6. dtmstart = Time
  7. Text2.Text = Format(dtmstart, "hh:mm:ss")
  8. On Error Resume Next
  9. Adodc1.RecordSource = txtSQL.Text
  10. Adodc1.Refresh
  11. dtmEnd = Time
  12. Text3.Text = Format(dtmEnd, "hh:mm:ss")
  13. dtmTotalTime = dtmEnd - dtmstart
  14. Text1.Text = Format(dtmTotalTime, "hh:mm:ss")
  15.  
  16. End Sub
  17.  
  18. ----------------------------------------------------------------------------------
  19.  
  20. Private Sub Combo1_Click()
  21.  
  22. Label1.Caption = Combo1.List(Combo1.ListIndex)
  23. txtSQL.Text = StrNested(Combo1.ListIndex)
  24. txtSQL.Enabled = False
  25.  
  26. End Sub
  27.  
  28. -------------------------------------------------------------------------------
  29.  
  30. Private Sub Form_Load()
  31. dtmstart = Empty
  32. dtmEnd = Empty
  33. Text1.Text = "00:00:00:00"
  34.  
  35.  
  36. 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"
  37. StrNested(1) = "SELECT ProductID, ProductName, CompanyName, ContactName FROM dbo.ProductsNew JOIN dbo.SuppliersNew ON ProductsNew.SupplierId = SuppliersNew.SupplierId OPTION (LOOP JOIN)"
  38. StrNested(2) = "SELECT ProductID, ProductName, CompanyName, ContactName From dbo.ProductsNew, dbo.SuppliersNew WHERE ProductsNew.SupplierId = SuppliersNew.SupplierId"
  39. StrNested(3) = "SELECT ProductID, ProductName, CompanyName, ContactName From ProductsNew Join SuppliersNew ON ProductsNew.SupplierId = SuppliersNew.SupplierId OPTION (MERGE JOIN)"
  40. StrNested(4) = "SELECT * From Suppliers WHERE SupplierID >=20"
  41.  
  42. Combo1.AddItem "Nested Loop"
  43. Combo1.AddItem "Nested Loop(with Option) "
  44. Combo1.AddItem "Hash Join"
  45. Combo1.AddItem "Merge Join"
  46. Combo1.AddItem "NEW"
  47.  
  48.  
  49. End Sub
Reputation Points: 10
Solved Threads: 0
Newbie Poster
wut-da is offline Offline
5 posts
since Jul 2004
Permalink
Apr 28th, 2006
0

Re: Elapsed time for retriving data from database

I think your best bet is to read this thread:
http://www.daniweb.com/techtalkforum...highlight=Time
Team Colleague
Reputation Points: 361
Solved Threads: 214
Taboo Programmer
Comatose is offline Offline
2,413 posts
since Dec 2004
Permalink
May 1st, 2006
0

Re: Elapsed time for retriving data from database

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.

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. Private Declare Sub GetLocalTime Lib "kernel32.dll" (lpSystemTime As SYSTEMTIME)
  2.  
  3. Private Type SYSTEMTIME
  4.   wYear As Integer
  5.   wMonth As Integer
  6.   wDayOfWeek As Integer
  7.   wDay As Integer
  8.   wHour As Integer
  9.   wMinute As Integer
  10.   wSecond As Integer
  11.   wMilliseconds As Integer
  12. End Type
  13.  
  14. Dim gtime As SYSTEMTIME
  15. Dim gtime2 As SYSTEMTIME
  16.  
  17. ---------------------------------------------------------------------------------
  18.  
  19. Private Sub cmdSQL_Click()
  20. GetLocalTime gtime
  21.  
  22. Text2.Text = gtime.wHour & ":" & gtime.wMinute & ":" & gtime.wSecond & ":" & gtime.wMilliseconds
  23.  
  24. On Error Resume Next
  25. Adodc1.RecordSource = txtSQL.Text
  26. Adodc1.Refresh
  27.  
  28.  
  29. GetLocalTime gtime2
  30. Text3.Text = gtime2.wHour & ":" & gtime2.wMinute & ":" & gtime2.wSecond & ":" & gtime2.wMilliseconds
  31.  
  32. 'i wish to display the result of the time difference here
  33. Text1.Text = dtmTotalTime
  34. End Sub

hope u can help me
Reputation Points: 10
Solved Threads: 0
Newbie Poster
wut-da is offline Offline
5 posts
since Jul 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: How Grid works with code
Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Macro for deleting Page & Section breaks





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC