Puzzling MSHFlexgrid behavior 1

[guitarzan]

I'm having a very weird problem with the MSHFlexGrid, unless I'm missing something fundamental here... I put together a minimum of code to reproduce the behavior;

Create and save a new project, drop in an MSHFlexGrid and three command buttons, add a refernce to Microsoft ADO Library, and use the following code:

Option Explicit
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cm As ADODB.Command

Private Sub Form_Load()
   Set Conn = New ADODB.Connection
   Conn.Open _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & App.Path & ";" & _
      "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
   
   Set cm = New ADODB.Command
   cm.ActiveConnection = Conn
   cm.CommandType = adCmdText
   cm.CommandText = "SELECT * FROM MyData.csv"
   
   Set rs = New ADODB.Recordset
   rs.Open cm, , adOpenKeyset, adLockOptimistic
   
   Set MSHFlexGrid1.DataSource = rs
   
End Sub

Private Sub Command1_Click()
   ShowCategory 1
End Sub

Private Sub Command2_Click()
   ShowCategory 2
End Sub

Private Sub Command3_Click()
   ShowCategory 3
End Sub

Private Sub ShowCategory(iCategoryID As Integer)
   cm.CommandText = "SELECT * FROM MyData.csv WHERE category = " & iCategoryID
   rs.Close
   rs.Open cm, , adOpenKeyset, adLockOptimistic
   Set MSHFlexGrid1.DataSource = rs

End Sub

For a datasource, save the following to MyData.csv in the application folder:

category,name
1,mike
1,joe
1,harry
1,tom
2,al
2,bob
2,chris

The behavior:
1. When you run the program, all the records are shown in the grid, and you can click on any cell in the grid.
2. Click "Command1", and only records with category=1 are shown, as expected... and you can still click anywhere you want in the grid.
3. Click "Command2", and only records with category=2 are shown, and everything works as expected.
4. Click "Command3"... now no records are returned, and you see a blank grid (except for the header row). Still, behaving as expected.
5. Click "Command1", and again, and only records with category=1 are shown.

BUT... now you can't click and select cells in the grid! Or at least, I can't! Using the keyboard to navigate the grid at this point is even stranger. Is this a bug, or am I doing something I shouldn't?

 

[SBerthold]

It does appear to be a bug, but I may be overlooking something. Try loading the data into a recordset created using Append.Fields and try using the SHAPE provider.
If these do not work either, then I will look further.

 

[SBerthold]

Ok. I couldn't resist (and may also benefit me someday).

It really is a BUG.

Try the following, by adding it to the end of your "ShowCategory" proceedure:

    Dim iFixedRows As Integer
    with MSHFlexGrid1
        iFixedRows = .FixedRows
        If .Rows > 1 Then
           .FixedRows = 0
           .FixedRows = iFixedRows
        End If
    End With

 

[guitarzan]

SBerthold,

Thanks for that, and the confirmation that maybe I'm not going crazy (maybe). I can't get to my development machine right now but will try later...

 

[Bobbles]

Personally I evolved away from binding years ago.
As Buffy would say 'that would be bad'.

Have one Sub for configuring your grid.
And another for Grid_AddData.
In the latter, you clear the grid, then navigate through your recordset, adding rows, and populating the cells.
This allows you to determine/control what goes into each cell, and allows you to format as you feel is appropriate.

This is the simplest, safest, cleanest approach, and gives you the maximum control.

'Now who could argue with that' (Blazing Saddles)

Regards,
Rob

 

[SBerthold]

The HFlex grid is not really bound here. It is just for the initial data. You can set the HFlexGrid's recordset/datasource to Nothing after the grid is filled.

 

[Bobbles]

I use the free SGrid2, but the concept should be the same.

If you keep it simple and do all the loading, clearing, loading, etc, with code
(no binding please)
it should be impossible to have the problem described above.

If you close the rs, and set it to nothing, and then send your next SQL query, as long as records are returned, the grid will show the data.

I will bet money on it.

Regards,
Rob

 

[SBerthold]

>If you close the rs, and set it to nothing, and then send your next SQL query, as long as records are returned, the grid will show the data.


Bob, that (the Data being there or not) is not the problem here, at all ...

 

[Bobbles]

Have you tried my suggestion ?
All I am saying is, if you have a rs with records, they will load , if you only use code (no binding).

Regards,
Rob

 

[SBerthold]

guitarzan, did you get it working?

Here is another solution:

1. Add the following to the declarations section:
Dim rsDummy As New ADODB.Recordset
'(For this set-up, using NEW here is no problem)

2. Change the last line in the ShowCategory proceedure to:
'Set MSHFlexGrid1.DataSource = rs
If rs.RecordCount = 0 Then
If rsDummy.State = adStateClosed Then Call CreateDummyRS
Set MSHFlexGrid1.DataSource = rsDummy
Else
Set MSHFlexGrid1.DataSource = rs
End If

3. Add the following proceedure

Private Function CreateDummyRS() As ADODB.Recordset
    Dim fld         As ADODB.Field
    With rsDummy
        For Each fld In rs.Fields
            .Fields.Append fld.Name, fld.Type, fld.DefinedSize, adFldIsNullable
        Next fld
        .Open:.AddNew
        For Each fld In rs.Fields
            .Fields(fld.Name) = Null
        Next fld
    End With
End Function

This assumes that the main Rs is initially opened.
This is dynamic enough to work for any set-up of this sort.

 

[guitarzan]

SBerthold,
Sorry for the delay. Your fix absolutely worked (the first one anyway, I haven't tried the second yet). I just had to tweak the Col and ColSel when there are no records returned in "rs" (so the header cells are not selected) and it seems to work just fine.

Bobbles,
I considered just using "AddItem" to add the rows to the grid as I needed them, but if there are no records returned, I can't seem to get the grid to show one FixedRow (the header) without at least one blank non-fixed row. This looked awkward to me, and possibly confusing to the user. Assigning the recordset to the grid's DataSource property DOES allow a single fixed header row with no other rows.

 

[ChewDoggie]

when I encounter an empty recordset, I don't show the column headers, I show a (fixed) row that says something "No Records Found".

flxPreview.Clear
flxPreview.Rows = 1
flxPreview.Cols = 1
flxPreview.ColWidth(0) = flxPreview.Width - 20
flxPreview.ColAlignment(0) = flexAlignCenterCenter
flxPreview.TextMatrix(0, 0) = "NO RECORDS FOUND"
flxPreview.BackColor = &H8000000F

HTH


The True measure of an individual is how he treats a person who can do him absolutely no good.

 

[Bobbles]

guitarzan.
I use the free SGrid2, so I don't have that problem (when there are no records).

What you could do is add a frame, with a nice central Label displaying this text - "NO RECORDS TO DISPLAY"
You can size and position the frame in the IDE, and set it's Visible property to false. Then when you have no records, just set it to Visible.

I would set the frame's horrible BorderStyle to None.
If you feel you need a border for the frame, you can use a shape (I do that often).

Regards,
Rob

 

[guitarzan]

Both fair points...

Bobbles, SGrid2 looks interesting; I will check it out when I have a chance, thanks