MSHFlexGrid Row Count Not Equal RecordCount

[bobbarker]

I have a MSHflexgrid whose datasource is an recordset set at form load

Set flxGrid.DataSource = rst1

I need to, at times, filter the recordset based on values keyed by the user.

If I filter the recordset before hand (e.g. rst1.Filter = "Field1 = 'Hello'") the number of rows in the flxGrid does not equal the number of records in my recordset and therefore some rows are not displayed.

Without a filter all rows are returned successfully.

Any suggestions would be appreciated.

 

[jebenson]

I have tested this scenario, and the MSHFlexGrid returns the correct number of rows and matches the number of records in the recordset both before and after a filter is applied to the recordset.

Could you show the code where you filter the recordset, including how it is assigned to the grid? Also, could you indicate how large is the difference between the grid's row count and the recordset's record count? Basically, a little more information is needed to determine the problem and a solution, if any.

Thanks.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[bobbarker]

Thanks for the response jebenson.

Extract of code

Private Sub Form_Load()
    Set mrstSections = New ADODB.Recordset
    mrstSections.Open "vw_section", gcnnConn, adOpenStatic, adLockReadOnly, _
     adCmdTable
    InitGrid
End Sub

Private Sub InitGrid()
    
    With flxGrid1
        .Redraw = False
        .Clear
        .Rows = 2
        .FixedRows = 1
        .RowHeight(0) = .RowHeight(1) * 2
        .Cols = mrstSections.Fields.Count
        Set .DataSource = mrstSections
        If .Rows = 1 Then .AddItem ("")
    End With 'flxGrid1

End Sub

Private Sub txtCustCode_Validate(Cancel As Boolean)
    FilterRst
End Sub

Private Sub FilterRst()
    Dim strFilter As String

    If Len(txtCustCode.Text) > 0 Then strFilter = "vcCustCode = '" _
     & txtCustCode.Text & "'"
    mrstSections.Filter = strFilter
    InitGrid
End Sub

The difference between row and recordcount varies. For example:
1. Without a filter. Number of rows (excluding header) = 4204. Recordcount = 4204
2. With a filter. Rows = 395, recordcount = 905
3. With a different filter. Rows = 158, recordcount = 302
4. With a different filter. Rows = 153, recordcount = 519

 

[jebenson]

Well, I'm using your code (adapted for my available data) and I still can't get the problem to occur. However, I would like to ask how you get the grid to display the records. Using your code in InitGrid, if I leave in the ".Redraw = False" line, the grid never changes and the records are not displayed. If I remove this line, the grid displays the records.

One thought...instead of reassigning the recordset to the grid after the filter, just do a refresh on the grid. Once the recordset is assigned to the grid's DataSource property, you don't need to reassign it to make the grid display filtered records. Also, the portion of the code where you set the number of columns is not necessary, as the MSHFlexGrid will automatically display the all of the columns in the data source. Try changing your code to this:

Private Sub Form_Load()
Set mrstSections = New ADODB.Recordset
mrstSections.Open "vw_section", gcnnConn, adOpenStatic, adLockReadOnly, _
adCmdTable

InitGrid

Set flxGrid1.DataSource = mrstSections
If flxGrid1.Rows = 1 Then flxGrid1.Rows = 2

End Sub

Private Sub InitGrid()

With flxGrid1
.Rows = 2
.FixedRows = 1
.RowHeight(0) = .RowHeight(1) * 2
End With 'flxGrid1

End Sub

Private Sub txtCustCode_Validate(Cancel As Boolean)
FilterRst
End Sub

Private Sub FilterRst()
Dim strFilter As String

If Len(txtCustCode.Text) > 0 Then strFilter = "vcCustCode = '" _
& txtCustCode.Text & "'"
mrstSections.Filter = strFilter
flxGrid1.Refresh
If flxGrid1.Rows = 1 Then flxGrid1.Rows = 2
End Sub

Basically, format the grid once, and assign the recordset to it once.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[bobbarker]

Thanks for your input again.

I have tried your suggestions but still have the mismatch

I have tried some further things out.

The recordset I am trying to open and filter is based on a SQL VIEW (Virtual) table.

If I create a recordset based on a table and filter the recordset the number of rows and records match OK.

Similar to the view, if I try and filter a recordset based on a stored procedure (commandtype = adCmdStoredProc) again the rows and recordcount do not match.

If I open as a commandtext type (i.e. mrstSections.Open "Select * from vw_sections", gcnnconn, adOpenStatic, adLockReadOnly, adCmdText) and then filter the recordset, again the row number and record count do not match.

A workaround seems to be to open the recordset as a commandtext type including the filter (e.g. "Select * from vw_section Where vcCustCode='" & txtCustCode.Text & "'"), and then not filter the recordset further. This seems to work fine, though would have the performance hit possibly (or possibly not?)

Regardless, would be interested in working out why setting the datasource to a 'recordset.filter' does not work on commandtext, stored procedures and views. It is particularly frustrating as the 'recordset.filter' does return the right records but, although it does not seem to be a problem with the data provider, it appears not to be compatible with a MSHFlexGrid. At this stage, though, I have yet to find anything that backs this up and so am a little sceptical suggesting this.