Solved

Optimize ADO DataGrid Record Processing

Posted on 2002-05-22
8
256 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 43

Accepted Solution

by:
TimCottee earned 100 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

860 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