Solved

Optimize ADO DataGrid Record Processing

Posted on 2002-05-22
8
244 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
Comment Utility
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
Comment Utility
To improve performance:  
You can also open the recordset as adOpenForwardOnly and adLockReadOnly

Anthony
0
 

Author Comment

by:jtrapat1
Comment Utility
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
 
LVL 43

Accepted Solution

by:
TimCottee earned 100 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jtrapat1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now