BUG: DataCombo/DataList Not Displaying Recordset with Sort/Filter (230167)


The information in this article applies to:

  • Microsoft Visual Basic Learning Edition for Windows 5.0
  • Microsoft Visual Basic Learning Edition for Windows 6.0
  • Microsoft Visual Basic Professional Edition for Windows 5.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.0
  • ActiveX Data Objects (ADO) 2.1
This article was previously published under Q230167

SYMPTOMS

DataCombo/DataList controls bound to an ADO.Recordset are not correctly reflecting data when Recordset.Sort or Recordset.Filter is applied.

CAUSE

The DataCombo/DataList controls don't use chapter handles when fetching rows from OLEDB rowsets. They do call IRowPosition::GetRowPosition and pass in a non-NULL chapter handle parameter, but apparently they don't use it. As a result, these controls behave incorrectly when bound to child commands from the DE (or manually to child recordsets) or when the Sort and Filter properties on the recordset are modified after binding to these controls. In certain cases, this results in run-time errors and, in other cases, the controls are populated with the entire rowset rather than the restricted set of rows. The Hierarchical Flex grid control appears to work properly. The DataList and DataCombo controls are OLEDB bindable controls, but they do not utilize chapter handles.

RESOLUTION

Essentially, the workaround is to use a temporary Recordset object (Recordset.Clone will not work) and copy the contents of the original and rebind the Recordset copy to the control(s).

Otherwise, if the Recordset is not a dynamic or manually built Recordset (as in this sample) then you could also work around the problem by using ORDER BY in the SQL statement. Similarly, you could work around the Filter option by placing a WHERE clause in the SQL statement.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a Standard EXE project and paste the following code in the General Declarations section:
    Option Explicit
Private rs As ADODB.Recordset
Private rsCopy As ADODB.Recordset

Private Sub Command1_Click()
    rs.Filter = "Name LIKE '%A%'"
    CopyRs
    DisplayRS
End Sub

Private Sub Command2_Click()
    Static sSort As String
    If sSort = "" Then sSort = "ASC"
    If sSort = "ASC" Then
        sSort = "DESC"
    Else
        sSort = "ASC"
    End If
    rs.Sort = "Name " & sSort
    CopyRs
    DisplayRS
End Sub

Private Sub Command3_Click()
    ReBindRs
End Sub

Private Sub Form_Load()
    Set rs = New ADODB.Recordset
    rs.Fields.Append "Name", adVarChar, 255
    rs.Open
    rs.AddNew Array("name"), Array("ABC")
    rs.AddNew Array("name"), Array("ABD")
    rs.AddNew Array("name"), Array("XYZ")
    rs.AddNew Array("name"), Array("AXY")
    rs.AddNew Array("name"), Array("123")
    rs.AddNew Array("name"), Array("890")
    rs.AddNew Array("name"), Array("190")
    rs.AddNew Array("name"), Array("A19")
    CopyRs
    Set DataList1.RowSource = rsCopy 
    DataList1.Listfield = "name"
    Set DataCombo1.RowSource = rsCopy 
    DataCombo1.ListField= "name"
    DisplayRS
End Sub

Public Sub DisplayRS()
    Text1.Text = ""
    If rs.RecordCount > 0 Then rs.MoveFirst
    Do While Not rs.EOF
        Text1.Text = Text1.Text & rs("Name").Value & vbCrLf
        rs.MoveNext
    Loop
End Sub

Public Sub CopyRs()
    Set rsCopy = Nothing
    Set rsCopy = New ADODB.Recordset
    
    rsCopy.Fields.Append "Name", adVarChar, 255
    rsCopy.Open
    
    If rs.RecordCount > 0 Then rs.MoveFirst
    Do While Not rs.EOF
        rsCopy.AddNew Array("name"), rs("name").Value
        rs.MoveNext
    Loop
    'ReBindRs
End Sub

Public Sub ReBindRs()
    Set DataList1.RowSource = Nothing
    Set DataCombo1.RowSource = Nothing
    Set DataList1.RowSource = rsCopy 
    DataList1.ListField = "name"
    Set DataCombo1.RowSource = rsCopy 
    DataCombo1.ListField = "name"
End Sub
  2. From the menu bar, navigate to Project-Components and select Microsoft DataList Controls 6.0 (OLEDB).
  3. From the menu bar, navigate to Projects-References and select Microsoft ActiveX Data Objects Library.
  4. Add three CommandButtons to the Form. Label these Filter, Sort, and Rebind, respectively.
  5. Add a Text box control to the Form, set the MultiLine property=True and set the Scrollbars=Vertical.
  6. Add a DataList and a DataCombo control to the Form.

    Now you should be able to run the sample application that demonstrates the problem. Note that when you click on the Sort or the Filter command, the DataList and DataCombo controls do not reflect the change in the Recordset. Click on the Rebind command and the DataList/DataCombo controls are rebound to the rsCopy Recordset so the sorted/filtered data displays correctly.

REFERENCES

189682 HOWTO: Bind a DataList or DataCombo to a Recordset at Run Time
Modification Type:MajorLast Reviewed:9/17/2003
Keywords:kbbug kbComboBox kbDatabase kbDataBinding kbListBox kbMDACNoSweep kbpending KB230167
©2003 Microsoft Corporation. All rights reserved.