Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Optimize ADO DataGrid Record Processing

Posted on 2002-05-22
8
Medium Priority
?
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

610 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