VB - Fill flexgrid with ADO Recordset

[mendhak]

Let's say you have an MSFlexgrid1 on your form, and an ADODB.Recordset as well. This code will get the column names, put them on the top-"gray" row, and fill in the rest of the flexgrid with values from the recordset.

Also, it'll adjust the width of the columns and the flexgrid itself. Modify it as you see fit.

To use this function, call it and pass your recordset to it.

e.g. fillflexgrid(objrs)

Public Function fillflexgrid(objtemp As ADODB.Recordset)
 
Dim i As Integer, j As Integer
Dim z As Integer
 
If Not (objtemp.BOF And objtemp.EOF) Then
    MSFlexGrid1.Cols = objtemp.Fields.Count
    'number of columns is the number of fields
    i = 0
 
        Do While Not (objtemp.EOF)
        objtemp.MoveNext
        i = i + 1
        Loop
        'this was to get number of rows
 
    objtemp.MoveFirst
    'get cursor back to original position
 
    MSFlexGrid1.Rows = i + 1
    'number of rows is i plus 1
 
    z = 1
 
        For j = 0 To objtemp.Fields.Count - 1
        MSFlexGrid1.TextMatrix(0, j) = objtemp.Fields(j).Name
        Next
        'here we're getting the column names which are the field names
 
        Do While Not (objtemp.EOF)
        For j = 0 To objtemp.Fields.Count - 1
            If Not (IsNull(objtemp.Fields(j).Value)) Then
            MSFlexGrid1.TextMatrix(z, j) = objtemp.Fields(j).Value
            Else
            MSFlexGrid1.TextMatrix(z, j) = ""
            End If
 
        Next
 
        z = z + 1
        objtemp.MoveNext
 
        Loop
 
    objtemp.MoveFirst
    'back to the first position...
 
 
'This part is to adjust the width of the columns so you
'can see the ENTIRE text!!
With MSFlexGrid1
For intColCounter = 1 To .Cols - 1
                sngLongestWidth = 0
                For intCounter = 0 To .Rows - 1
                    If sngLongestWidth < Me.TextWidth(.TextMatrix(intCounter, intColCounter)) Then
                        sngLongestWidth = Me.TextWidth(.TextMatrix(intCounter, intColCounter))
                    End If
                Next intCounter
                .ColWidth(intColCounter) = CLng(sngLongestWidth + 200)
               Next intColCounter
 
'now set the flexgrids width to the total width
 
Dim intwidthcounter As Integer, colcounter As Integer
 
 
intwidthcounter = 0
 
For colcounter = 0 To MSFlexGrid1.Cols - 1
intwidthcounter = intwidthcounter + MSFlexGrid1.ColWidth(colcounter)
Next
 
MSFlexGrid1.Width = intwidthcounter + 120
 
 
 
End With
 
Else
MSFlexGrid1.Clear
 
 
 
End If
 
 
End Function

PS: I know it doesn't follow proper coding conventions in terms of naming, etc, but hey, it gets the job done

[NicoNel2000]

Good code.

Tip I got the other day (from a real good developer, so will go with it)

Nerver say:
Do While Not(rs.EOF)
blah blah blah
rs.MoveNext
Loop

Use:
Do Until rs.EOF
blah blah blah
rs.MoveNext
Loop

Apearantly the first do one unecessary check in the background. Allthoug this take about as long as a ray of light to travel from your head to your arse, it can make a difference with large recordsets.
Also it is a good programming technique and less code (yeah, one word i know)

Cheers

Keep up the good work

Nico