[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Optimize ADO DataGrid Record Processing

Posted on 2002-05-22
8
Medium Priority
?
282 Views
Last Modified: 2008-02-01
I'm using VB6 against an sql server database.
Can I get some opinions or suggestions on my processing technique?
Am I performing this in the best possible way or is there a better way to populate and process the recordsets retrieved into my datagrid?
Currently, some of the more complex recordsets which are joins on two tables are slow to load into my datagrid.
Is there a better way than what I am doing here:
------------------------------------------------------
A form is launched which contains a datagrid.
In the Form_Activate() procedure, a function is called which populates the grid.
The function, is the following:
------------------------------------------------------
Public Sub DataInquire()
Dim ctr As Integer
    ucDataGrid1.Clear
    Select Case CurrentForm
        Case "ModifyCuts"
              SQLData = "SELECT * FROM Cuts ORDER BY RecordStatus" & SortBy
    End Select
    If ActiveRs Then RsData.Close
    RsData.Open SQLData, Cn, adOpenStatic, adLockBatchOptimistic
    ActiveRs = True
    If Not (RsData.EOF And RsData.BOF) Then
        RsData.MoveFirst
        ctr = 0
         ReDim DataArray(MaxCuts, 12)
        While Not RsData.EOF
            DataArray(ctr, 0) = RsData!ID
            DataArray(ctr, 1) = RsData!appropid
            DataArray(ctr, 2) = RsData!Description
            DataArray(ctr, 3) = RsData!recamt
            DataArray(ctr, 4) = RsData!cashamt
            DataArray(ctr, 5) = RsData!workforce
            DataArray(ctr, 6) = RsData!ftecutCode
            DataArray(ctr, 7) = RsData!subcommcode
            DataArray(ctr, 8) = RsData!NegCode
            DataArray(ctr, 9) = RsData!agencycode
            DataArray(ctr, 10) = RsData!fundtypecode
            DataArray(ctr, 11) = RsData!programcode
            DataArray(ctr, 12) = RsData!purposecode
 
            ucDataGrid1.LoadCutsData RsData!ID, RsData!appropid, RsData!Description, _
                FormatCurrency(RsData!recamt, 0), FormatCurrency(RsData!cashamt, 0), _
                RsData!workforce, RsData!ftecutCode, _
                RsData!subcommcode, RsData!NegCode, RsData!agencycode, RsData!fundtypecode, _
                RsData!programcode, RsData!purposecode
            RsData.MoveNext
            ctr = ctr + 1
        Wend
             ucDataGrid1.SelectFirst
---------------------------------------
In the above, I read the data into an array and then call the LoadCutsData function to populate the grid.
The LoadCutsData function is the following:
-----------------------------------------------------
Public Sub LoadCutsData(IdToAdd, AppropIdToAdd, DescriptionToAdd, _
    RecAmtToAdd, CashAmtToAdd, WorkforceToAdd, FTECutCodeToAdd, _
    SubCommCodeToAdd, NegCodeToAdd, AgencyCodeToAdd, FundTypeCodeToAdd, _
    ProgramCodeToAdd, PurposeCodeToAdd)
Dim NewItem As ListItem
    Set NewItem = LVDataGrid.ListItems.Add(, , IdToAdd)
    NewItem.ListSubItems.Add , , AppropIdToAdd
    NewItem.ListSubItems.Add , , DescriptionToAdd
    NewItem.ListSubItems.Add , , RecAmtToAdd
    NewItem.ListSubItems.Add , , CashAmtToAdd
    NewItem.ListSubItems.Add , , WorkforceToAdd
    NewItem.ListSubItems.Add , , FTECutCodeToAdd
    NewItem.ListSubItems.Add , , SubCommCodeToAdd
    NewItem.ListSubItems.Add , , NegCodeToAdd
    NewItem.ListSubItems.Add , , AgencyCodeToAdd
    NewItem.ListSubItems.Add , , FundTypeCodeToAdd
    NewItem.ListSubItems.Add , , ProgramCodeToAdd
    NewItem.ListSubItems.Add , , PurposeCodeToAdd

    LVDataGrid.ListItems(LVDataGrid.ListItems.Count).Selected = True
End Sub
-------------------------------------------------------
My question is this:
Is there a better or more optimal way to process the datagrid than what I am doing here?

Thanks in Advance
John
0
Comment
Question by:jtrapat1
  • 3
  • 3
  • 2
8 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 7027083
You can directly populate an array from a recordset using :

Dim DataArray()
DataArray = RsData.GetRows()

This array is of the size DataArray(0 to Fields.Count -1,1 To Records)

So DataArray(2,4) returns the third (zero-based remember) field from the fourth record

This method is much quicker than directly populating the array as you currently are.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7027099
To improve performance:  
You can also open the recordset as adOpenForwardOnly and adLockReadOnly

Anthony
0
 

Author Comment

by:jtrapat1
ID: 7027327
Tim,
Thanks for pointing me in  the right direction with the getrows() method.
However, I can't figure out how to reference the correct values.

Can you help me out with the logic on this.
I checked the MSDN and this discussion area but I couldn't find an example similar to mine.

I'm trying to loop thru the recordset and pass the correct values to my loadcutsdata function but the values are wrong.
I declared my DataArray() as Dim DataArray() as variant
Here's my code:
-----------------------------------------------------
    RsData.Open SQLData, Cn, adOpenForwardOnly, adLockReadOnly
    ActiveRs = True
    If Not (RsData.EOF And RsData.BOF) Then
        RsData.MoveFirst
        ctr = 0
 
        ReDim DataArray(0 To RsData.Fields.Count - 1, 1 To 12)
        While Not RsData.EOF
            DataArray = RsData.GetRows
           
            ucDataGrid1.LoadCutsData DataArray(ctr, 0), DataArray(ctr, 1), DataArray(ctr, 2), _
                FormatCurrency(DataArray(ctr, 3)), FormatCurrency(DataArray(ctr, 4)), _
                DataArray(ctr, 5), DataArray(ctr, 6), _
                DataArray(ctr, 7), DataArray(ctr, 8), DataArray(ctr, 9), DataArray(ctr, 10), _
                DataArray(ctr, 11), DataArray(ctr, 12)
            RsData.MoveNext
            ctr = ctr + 1
        Wend
-------------------------------------------------------

Thanks
John
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 43

Accepted Solution

by:
TimCottee earned 400 total points
ID: 7027344
Ok it should be something like this:

   RsData.Open SQLData, Cn, adOpenForwardOnly, adLockReadOnly
   ActiveRs = True
   If Not (RsData.EOF And RsData.BOF) Then
       RsData.MoveFirst

'       ReDim DataArray(0 To RsData.Fields.Count - 1, 1 To 12) - not necessary as the getrows call will redim the array anyway
       DataArray = RsData.GetRows
'       While Not RsData.EOF
       For ctr = 0 To Ubound(DataArray,0)
         
           ucDataGrid1.LoadCutsData DataArray( 0,ctr), DataArray(1,ctr), DataArray(2,ctr), _
               FormatCurrency(DataArray(3,ctr)), FormatCurrency(DataArray( 4,ctr)), _
               DataArray(5,ctr), DataArray(6,ctr), _
               DataArray(7,ctr), DataArray(8,ctr), DataArray(9,ctr), DataArray(10,ctr), _
               DataArray(11,ctr), DataArray(12,ctr)
       Next
-------------------------------------------------------
0
 

Author Comment

by:jtrapat1
ID: 7027653
Tim,
I tried your code and I'm getting an subscript out of range error on this line:
   For ctr = 0 To Ubound(DataArray,0)
I changed the line to:
   For ctr = 0 To Ubound(DataArray) and I get the correct values inserted into my grid but I know this isn't syntactically correct.
It calculates the correct upper bound as 12 and loops thru from 0 to 12.
It's like the upper bound function doesn't calculate the correct value.

Can you see what I'm doing wrong?
Do I have to use other parameters in my GetRows() function; such as:
DataArray = RsData.GetRows(Rows,Start,Fields)

Maybe I'm returning the wrong result.
Reply when you can.

Thanks
John
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7027668
I believe you need to change:
For ctr = 0 To Ubound(DataArray,0)

to:
For ctr = 0 To Ubound(DataArray, 2)

This will give you the number of rows.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7027679
I should have explained that the GetRows returns an array composed of Fields * Columns.  Therefore to loop through the rows, you need UBound(DataArray, 2)

Anthony
0
 

Author Comment

by:jtrapat1
ID: 7027721
Tim's code plus the change from Anthony was exactly the right code that helped me to populate the grid.

Thanks to Tim, Anthony, and everyone who helped out with this problem.

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month19 days, 18 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question