jtrapat1
asked on
Optimize ADO DataGrid Record Processing
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!reca
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(,
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(LVDat
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
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!reca
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(,
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(LVDat
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
To improve performance:
You can also open the recordset as adOpenForwardOnly and adLockReadOnly
Anthony
You can also open the recordset as adOpenForwardOnly and adLockReadOnly
Anthony
ASKER
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(c
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
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(c
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
Maybe I'm returning the wrong result.
Reply when you can.
Thanks
John
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,
Maybe I'm returning the wrong result.
Reply when you can.
Thanks
John
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
For ctr = 0 To Ubound(DataArray,0)
to:
For ctr = 0 To Ubound(DataArray, 2)
This will give you the number of rows.
Anthony
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
Anthony
ASKER
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.
Thanks to Tim, Anthony, and everyone who helped out with this problem.
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.