The following code uses the ShowData method of a custom ActiveX control to display data from a resultset in an MSHFlexGrid control. The code sets up the grid based on the names in the rdoColumns property and initializes the grid, preparing it for the data. Note the use of the OrdinalPosition property to index the resultset's rdoColumns property.
There are two sets of code to extract data from the rdoResultset, one that uses GetClipString, and another that uses the GetRows method. This helps you compare different approaches to the situation.
Public Function ShowData(Resultset As rdoResultset) As Variant
Dim cl As rdoColumn
Static GridSetup As Boolean
Dim MaxL As Integer
Dim rsl As rdoResultset
Dim Rows As Variant
On Error GoTo ShowDataEH
Set rsl = Resultset
If GridSetup Then
FGrid1.Rows = 51
FGrid1.Cols = rsl.rdoColumns.Count
FGrid1.Row = 0
For Each cl In rsl.rdoColumns
FGrid1.Col = cl.OrdinalPosition - 1
FGrid1 = cl.Name
If rsl.rdoColumns(cl.OrdinalPosition - 1).ChunkRequired Then
MaxL = 1
Else
MaxL = rsl.rdoColumns(cl.OrdinalPosition - 1).Size + 4
End If
If MaxL > 20 Then MaxL = 20
FGrid1.ColWidth(FGrid1.Col) = TextWidth(String(MaxL, "n"))
Next cl
GridSetup = True
End If
FGrid1.Rows = 1 'Clear Grid of data (except titles)
FGrid1.Rows = 51
FGrid1.Row = 1
FGrid1.Col = 0
FGrid1.RowSel = FGrid1.Rows - 1
FGrid1.ColSel = FGrid1.Cols - 1
FGrid1.Clip = rsl.GetClipString(50, , , "-")
ExitShowData:
FGrid1.RowSel = 1
FGrid1.ColSel = 0
Exit Function
ShowDataEH:
Select Case Err
Case 40022:
FGrid1.Clear
Resume ExitShowData
Case 13
FGrid1.Text = "< >"
Resume Next
Case Else
MsgBox "Could not display data: " & Err & vbCrLf & Error$
Resume ' ExitShowData
End Select
End Function
The following code implements the ShowData method of a custom ActiveX control adapted from an RDO control. Note that the RDO GetClipString method is superseded in ADO by the GetString method. Since you then have to parse the resulting Variant array, the routine is noticeably slower. If you have problems getting the GetRows function to work, you should convert to a more conservative (and slower) approach that loops through the rows. However, you should avoid using this technique if possible because it is far less efficient than using GetRows or binding directly to a control.
Note how the OrdinalPosition property can no longer be used as an index on the Fields collection to pull out the column titles as you could in RDO. To handle this, substitute a new integer counter to address the column being manipulated. Use the DefinedSize and ActualSize properties to find the TEXT and IMAGE datatype fields that would not fit in a column. These new properties make it easier to determine the details of specific field values. Also added is code to deal with BLOB types if they're encountered while working through the data columns.
Public Function ShowData(Resultset As Recordset) As Variant
Dim cl As Field
Static GridSetup As Boolean
Dim MaxL As Integer
Dim Op As Integer
Dim rsl As Recordset
Dim rows As Variant
On Error GoTo ShowDataEH
Set rsl = Resultset
If GridSetup Then
Else
FGrid1.rows = 51
FGrid1.Cols = rsl.Fields.Count
FGrid1.Row = 0
Op = 0
For Each cl In rsl.Fields
FGrid1.Col = Op
FGrid1 = cl.Name
If rsl.Fields(Op).DefinedSize > 255 Then
MaxL = 1
Else
MaxL = rsl.Fields(Op).ActualSize + 4
End If
If MaxL > 20 Then MaxL = 20
FGrid1.ColWidth(FGrid1.Col) = TextWidth(String(MaxL, "n"))
Op = Op + 1
Next cl
GridSetup = True
End If
FGrid1.rows = 1
FGrid1.rows = 51
FGrid1.Row = 1
FGrid1.Col = 0
FGrid1.RowSel = FGrid1.rows - 1
FGrid1.ColSel = FGrid1.Cols - 1
With FGrid1
' You can also use the ADO2 GetString method here in lieu of the
' following.
FGrid1.Clip = rsl.GetString(adClipString, 50, , , "-")
End With
ExitShowData:
FGrid1.RowSel = 1
FGrid1.ColSel = 0
Exit Function
ShowDataEH:
Select Case Err
Case 40022, 3021:
FGrid1.Clear
Resume ExitShowData
Case 13, Is < 0
rows(j, i) = "< >"
Resume 'Next
Case Else
MsgBox "Could not display data: " & Err & vbCrLf & Error$
Resume ' ExitShowData
End Select
End Function