Hello All



Can anyone shed some light on the following problem 



I Have created a disconnected recordset in VB6, if I do a record count 

before applying a filter the record count is 1600 records. If I then apply 

a filter the record count is reduced to 116 records, which is what what I 

was expecting. However if I have a datacombo box bound to this recordset 

it still shows all of the records. I was under the impression that the 

point of the filter is to hide unwanted records, if this is the case why 

does the combo box still show all the records. Am I misinterpreting what 

the filter functions does ? or do I have someing else i must before this 

will work ?



Many thanks

Use Recordset.Clone to solve your problem



-----Original Message-----

From: Khalid Hussain [mailto:khalid@p...]

Sent: Wednesday, May 16, 2001 12:30 PM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] ADO Filtered Recordsets and bound

controls





Hello All



Can anyone shed some light on the following problem 



I Have created a disconnected recordset in VB6, if I do a record count 

before applying a filter the record count is 1600 records. If I then apply 

a filter the record count is reduced to 116 records, which is what what I 

was expecting. However if I have a datacombo box bound to this recordset 

it still shows all of the records. I was under the impression that the 

point of the filter is to hide unwanted records, if this is the case why 

does the combo box still show all the records. Am I misinterpreting what 

the filter functions does ? or do I have someing else i must before this 

will work ?

I have that you need to reset the datacombo; set the datasource(or recordsource) to

nothing then set it to the filtered recordset.



Khalid Hussain wrote:



> Hello All

>

> Can anyone shed some light on the following problem

>

> I Have created a disconnected recordset in VB6, if I do a record count

> before applying a filter the record count is 1600 records. If I then apply

> a filter the record count is reduced to 116 records, which is what what I

> was expecting. However if I have a datacombo box bound to this recordset

> it still shows all of the records. I was under the impression that the

> point of the filter is to hide unwanted records, if this is the case why

> does the combo box still show all the records. Am I misinterpreting what

> the filter functions does ? or do I have someing else i must before this

> will work ?

>

> Many thanks

It sounds like a bug in the data combo. Which data combo are you using? Some

MS combo, protoview, etc???



Pete

----- Original Message -----

From: "Khalid Hussain" <khalid@p...>

To: "ActiveX_Data_Objects" <activex_data_objects@p...>

Sent: Wednesday, May 16, 2001 4:29 PM

Subject: [activex_data_objects] ADO Filtered Recordsets and bound controls





> Hello All

>

> Can anyone shed some light on the following problem

>

> I Have created a disconnected recordset in VB6, if I do a record count

> before applying a filter the record count is 1600 records. If I then apply

> a filter the record count is reduced to 116 records, which is what what I

> was expecting. However if I have a datacombo box bound to this recordset

> it still shows all of the records. I was under the impression that the

> point of the filter is to hide unwanted records, if this is the case why

> does the combo box still show all the records. Am I misinterpreting what

> the filter functions does ? or do I have someing else i must before this

> will work ?

>

> Many thanks




$subst('Email.Unsub')

Or have you tried a refill on the datacombo after applying the filter?



-- Tomm



> -----Original Message-----

> From: Seth Jaffe [mailto:sjaffe@d...]

> Sent: Wednesday, May 16, 2001 11:47 AM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] Re: ADO Filtered Recordsets and bound

> controls

>

>

> I have that you need to reset the datacombo; set the

> datasource(or recordsource) to

> nothing then set it to the filtered recordset.

>

> Khalid Hussain wrote:

>

> > Hello All

> >

> > Can anyone shed some light on the following problem

> >

> > I Have created a disconnected recordset in VB6, if I do a record 

count

> > before applying a filter the record count is 1600 records. If I

> then apply

> > a filter the record count is reduced to 116 records, which is

> what what I

> > was expecting. However if I have a datacombo box bound to this 

recordset

> > it still shows all of the records. I was under the impression that 

the

> > point of the filter is to hide unwanted records, if this is the case 

why

> > does the combo box still show all the records. Am I misinterpreting 

what

> > the filter functions does ? or do I have someing else i must before 

this

> > will work ?

> >

> > Many thanks



> sjaffe@d...


> $subst('Email.Unsub')

>

>



> matthis@b...


> $subst('Email.Unsub')

>

>

Thanks for the suggestions



Using .clone did not help



I have tried setting the datacombobox datasource to nothing before re-

binding to the recordset



the datacombo is the standard one that comes with VB6 enterprise edition



I have tried this on NT4 VB6 SP4 and W2K VB6 SP5 both do that same thing



If you apply the filter and click on an item in datacombobox that should 

not be there I get an error indicating that the record does not exist, 

this appears to be correct. If I click on an item that is part of the 

filtered recordset it works okay. This would suggest that the filtering 

part of recordset is working fine, only, why a record that is not part of 

the filtered recordset is showing up at all in the datacombobox in the 

first place is a mystery to me. It looks like the datacombobox is not 

refreshing correctly to filtered recordset. 



Any more ideas greatly recieved

Sorry, left the refill step out. You might need to clear first.  I need to reset the

datasource because I send a new sql statement. The new statement changes the where

clause.



Tomm Matthis wrote:



> Or have you tried a refill on the datacombo after applying the filter?

>

> -- Tomm

>

> > -----Original Message-----

> > From: Seth Jaffe [mailto:sjaffe@d...]

> > Sent: Wednesday, May 16, 2001 11:47 AM

> > To: ActiveX_Data_Objects

> > Subject: [activex_data_objects] Re: ADO Filtered Recordsets and bound

> > controls

> >

> >

> > I have that you need to reset the datacombo; set the

> > datasource(or recordsource) to

> > nothing then set it to the filtered recordset.

> >

> > Khalid Hussain wrote:

> >

> > > Hello All

> > >

> > > Can anyone shed some light on the following problem

> > >

> > > I Have created a disconnected recordset in VB6, if I do a record count

> > > before applying a filter the record count is 1600 records. If I

> > then apply

> > > a filter the record count is reduced to 116 records, which is

> > what what I

> > > was expecting. However if I have a datacombo box bound to this recordset

> > > it still shows all of the records. I was under the impression that the

> > > point of the filter is to hide unwanted records, if this is the case why

> > > does the combo box still show all the records. Am I misinterpreting what

> > > the filter functions does ? or do I have someing else i must before this

> > > will work ?

> > >

> > > Many thanks



> > sjaffe@d...


> > $subst('Email.Unsub')

> >

> >



> > matthis@b...


> > $subst('Email.Unsub')

> >

> >

>

I not sure I follow your comment, are you saying that I should re-issue 

the SQL statement so that I get a new recordset that reflects the filter I 

am trying to apply. If so, that is exactly what I am trying to avoid, I do 

not want a new recordset to be created every time the client program 

browses the data. I am trying to get the data to come down once and allow 

different views dependant on the filter applied.



Thank you for you help so far







> Sorry, left the refill step out. You might need to clear first.  I need 

to reset the

> datasource because I send a new sql statement. The new statement changes 

the where

> clause.

> 

> Tomm Matthis wrote:

> 

> > Or have you tried a refill on the datacombo after applying the filter?

> >

Thank you to all who offered advice



I have since found out that this is a known bug as documented in the MSDN 

library Q230167.



Basically the work around is to recreate the recordset with a new SQL 

query ( which I don't want to do ) or cycle through the original recordset 

for the filtered records and copy only those into another recordset which 

then used for binding to the control. Not very good, but at least its a 

solution.

That is the reason for the Clone method. Clone the recordset. Have the

original recordset. Do filters on your Clone.



-----Original Message-----

From: Khalid Hussain [mailto:khalid@p...]

Sent: Thursday, May 17, 2001 2:07 PM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] Re: ADO Filtered Recordsets and bound

controls





Thank you to all who offered advice



I have since found out that this is a known bug as documented in the MSDN 

library Q230167.



Basically the work around is to recreate the recordset with a new SQL 

query ( which I don't want to do ) or cycle through the original recordset 

for the filtered records and copy only those into another recordset which 

then used for binding to the control. Not very good, but at least its a 

solution.

> That is the reason for the Clone method. Clone the recordset. Have the

> original recordset. Do filters on your Clone.

 



I tried that and I could not make it work.  The article I mentioned has 

something to say on the clone method ( see below ) are you suggesting 

something different, please excuse me if I am not seeing the blindingly 

obvious



MSDN Extract



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.