Microsoft KB Archive/230167

= BUG: DataCombo/DataList Not Displaying Recordset with Sort/Filter =

Article ID: 230167

Article Last Modified on 9/17/2003

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 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.



Steps to Reproduce Behavior
  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  From the menu bar, navigate to Project-Components and select Microsoft DataList Controls 6.0 (OLEDB). From the menu bar, navigate to Projects-References and select Microsoft ActiveX Data Objects Library. Add three CommandButtons to the Form. Label these Filter, Sort, and Rebind, respectively. Add a Text box control to the Form, set the MultiLine property=True and set the Scrollbars=Vertical.</li> 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.</li></ol>

<div class="references_section">