Find Code:
All Words
Any of the Words
Exact Phrase
Home
:
Code
:
Forums
:
Submit
:
Mailing List
:
About
:
Contact
Code
All
VB.NET
ASP.NET
C#
VB Classic
ASP Classic
Snippets
Popular
Resources
Submit Code
Forums
Articles
Tips
Links
Books
Contest
Link to us
ADO Recorset - optimizing
Author:
NBS Solutions
E-mail:
Click to e-mail author
Website:
http://www.netnet.ca/vbcode.htm
Submitted:
4/16/2004
Version:
VB6
Compatibility:
VB6
Category:
Databases
Views:
10718
Using the hidden Collect method and record count to optimize the way you retrieve data with ado.
Declarations:
'none
Code:
'OPTIMIZING Private Sub LoadMyData() Dim my_Recs as Long, my_Counter as Long With rs .Open "Select Name, Surname, Age from tblCust" If Not .EOF then my_Recs = .RecordCount For my_Counter = 1 To my_Recs If ISNULL(.Collect(0))=False Then Combo1.AddItem .Collect(0) Endif .MoveNext Next my_Counter EndIf If .State = adStateOpen Then .Close End With End Sub 'Few Notes 'This will not work with a Keyset cursor type 'The ADO Recorset object exposes 'a 'hidden, 'undocumented 'member: 'the 'Collect 'property. This 'property acts similar as the Field's Value property, but it's faster 'You do not need a reference to the Field object. 'You can use this 'property by passing a numeric index or a field's name, as in: 'Example: Combo1.AddItem .Collect(0) instead of Combo1.AddItem .Collect("Name") 'Remeber that the field index starts at 0 for the first field 'ISNULL will filter any invalid data - Null is the same as Empty 'You can also use the .MoveLast before t_Recs = .RecordCount and .MoveFirst there after 'to ensure that you get the recordcount 'THIS CODE GIVE YOU PERFORMANCE ADVANTAGES ON THE FOLLOWING FRONTS 'Usage of the with block - decrease refrences 'Using the .Collect Method 'Using the for Loop aposed to the Do loop 'Note: Is also nice to use this code when you work with a Progress Bar. 'Adjust the code like this to accomidate the progress bar Private Sub LoadMyData() Dim my_Recs as Long, my_Counter as Long With rs .Open "Select Name, Surname, Age from tblCust" If Not .EOF then my_Recs = .RecordCount ProgressBar1.Max = my_Recs 'set the maximum value of progressbar ProgressBar1.Visable = False For my_Counter = 1 To my_Recs ProgressBar1.Value = my_Counter If ISNULL(.Collect(0))=False Then Combo1.AddItem .Collect(0) Endif .MoveNext Next my_Counter EndIf ProgressBar1.Visable = False If .State = adStateOpen Then .Close End With End Sub
Home
|
Forums
|
Submit
|
Books
|
Mailing List
|
Advertising
|
About
|
Contact
© 2024 A1VBCode. All rights reserved.
Legal disclaimer & terms of use
Privacy statement