Visual Basic Concepts

Displaying a Resultset in an MSHFlexGrid Control

See Also

RDO

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 = False 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

ADO

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 = False Then
      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 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