Using VO2ADO Filters
in [Visual Objects]
|
From: Willie Moore on 14 Jun 2010 09:12 Kevin, If I remember correctly, the filter can only use 1 field. Use the where clause. Regards, Willie "Kevin" <kdmurphy(a)eircom.net> wrote in message news:BLoRn.167$K4.148(a)news.indigo.ie: > Erik, > > Using the WHERE clause would, if I understand it correctly, require the > entire data set to be read again everytime it is changed. Using the > filter I was hoping would enable the user to show/hide the records. The > two parts of the filter (checking the State and checking the Station) > work on their own. When combined using AND they fail and the Server > Filter access returns the previous filter, not what I'm trying to > achieve. > > I did use it in the WHERE clause to see if it would work and it doesn't > there either. > > Kevin > > "ER!k \/!$$ER" <nospam(a)nospam.com> wrote in message > news:87kgqfF59iU1(a)mid.individual.net: > > > > Keven, > > > > You best use the the WHERE clause in the Sql statement. > > > > Erik > > > > "Kevin" <kdmurphy(a)eircom.net> schreef in bericht > > news:%VLQn.161$K4.159(a)news.indigo.ie... > > > > I have an ADOServer created from an MS SqL SERVER table using the > > > following statement: > > > SELECT ClaimNo, PurchaseID, PurDate, Station, FuelType, Litres, Price, > > > Cost, Notes, State FROM Fuel > > > oSrv := AdoServer{ cSQL, oConn, adOpenDynamic, adLockOptimistic, > > > adCmdText } > > > > > > There are some items which I want hidden by default, but the user can > > > select to view them, so I apply the following filter before showing the > > > window: > > > oSrv:SetFilter( [State = null or State <> 'R'] ) > > > > > > and this works fine. > > > > > > I now want to add another condition to the filter so build it as follows: > > > cFilter := [(State = null or State <> 'R') AND Station LIKE 'Topaz%'] > > > oSrv:SetFilter( cFilter ) > > > oSrv:Requery() > > > > > > When I apply this filter using oSrv:SetFilter it does not work. No change > > > is seen in the ADOServer. Checking oSrv:Filter before assigning the new > > > filter returns the correct string. After assigning the new filter it > > > returns 0. > > > > > > I have tried assigning the filter using the Filter assign but it never > > > seems to work, the access always returns 0 afterwards. > > > > > > Both the initial Filter and the additional one will work on their own but > > > not when combined as above. > > > > > > What am I doing wrong or missing? > > > > > > VO28 SP3 > > > Current version of VO2ADO > > > Connection cursor location: AdUseClient > > > > > > Thanks in advance. > > > > > > Kevin > > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 5194 (20100614) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
From: Ginny Caughey on 14 Jun 2010 09:27 Willie and Erik, Agreed. WHERE is your friend, and it should be fast with SQL if you have the right indexes. -- Ginny Caughey www.wasteworks.com
From: Willie Moore on 14 Jun 2010 10:29 Ginny, What I usually do on my indexes is put the ones in that I know I need. When I am at my beta sites, I use the performance and tuning engine to watch the users and then adjust the indexes based on usage. I have found that the users will sometimes use the app in ways that I had not considered. Regards, Willie "Ginny Caughey" <ginny.caughey.online(a)wasteworks.com> wrote in message news:4c162daa$0$1468$c3e8da3(a)news.astraweb.com: > Willie and Erik, > > Agreed. WHERE is your friend, and it should be fast with SQL if you have the > right indexes. > > -- > > Ginny Caughey > www.wasteworks.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 5195 (20100614) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
From: E®!k /!$$E® on 14 Jun 2010 10:02 > Using the WHERE clause would, if I understand it correctly, require the > entire data set to be read again everytime it is changed. Using the filter, you first retrieve ALL date to the local machine and there is filtered what is shown. Using the WHERE clause, you only retrieve the data matching the condition. And yes, you are right. If you change the WHERE clause, you'll need to fill a new recordset with the data and assign that to the dataserver. oAdoServer:SetRecordSet( oRs ). This works fine and I have been using this technique in all my apps Keep in mind you need to repaint the bBrowser grid if you change the columns. Another option would be to use a view. You could change the view with a new WHERE clause and then call the oAdoServer:Requery() method. Using the filter will be very slow on large data, hth. Erik > I did use it in the WHERE clause to see if it would work and it doesn't > there either. In that case you are doing something wrong. "Kevin" <kdmurphy(a)eircom.net> schreef in bericht news:BLoRn.167$K4.148(a)news.indigo.ie... > Erik, > > Using the WHERE clause would, if I understand it correctly, require the > entire data set to be read again everytime it is changed. Using the filter > I was hoping would enable the user to show/hide the records. The two parts > of the filter (checking the State and checking the Station) work on their > own. When combined using AND they fail and the Server Filter access > returns the previous filter, not what I'm trying to achieve. > > I did use it in the WHERE clause to see if it would work and it doesn't > there either. > > Kevin > > "ER!k \/!$$ER" <nospam(a)nospam.com> wrote in message > news:87kgqfF59iU1(a)mid.individual.net: > >> Keven, >> >> You best use the the WHERE clause in the Sql statement. >> >> Erik >> >> "Kevin" <kdmurphy(a)eircom.net> schreef in bericht >> news:%VLQn.161$K4.159(a)news.indigo.ie... >> > I have an ADOServer created from an MS SqL SERVER table using the >> > following statement: >> > SELECT ClaimNo, PurchaseID, PurDate, Station, FuelType, Litres, Price, >> > Cost, Notes, State FROM Fuel >> > oSrv := AdoServer{ cSQL, oConn, adOpenDynamic, adLockOptimistic, >> > adCmdText } >> > >> > There are some items which I want hidden by default, but the user can >> > select to view them, so I apply the following filter before showing the >> > window: >> > oSrv:SetFilter( [State = null or State <> 'R'] ) >> > >> > and this works fine. >> > >> > I now want to add another condition to the filter so build it as >> > follows: >> > cFilter := [(State = null or State <> 'R') AND Station LIKE 'Topaz%'] >> > oSrv:SetFilter( cFilter ) >> > oSrv:Requery() >> > >> > When I apply this filter using oSrv:SetFilter it does not work. No >> > change >> > is seen in the ADOServer. Checking oSrv:Filter before assigning the new >> > filter returns the correct string. After assigning the new filter it >> > returns 0. >> > >> > I have tried assigning the filter using the Filter assign but it never >> > seems to work, the access always returns 0 afterwards. >> > >> > Both the initial Filter and the additional one will work on their own >> > but >> > not when combined as above. >> > >> > What am I doing wrong or missing? >> > >> > VO28 SP3 >> > Current version of VO2ADO >> > Connection cursor location: AdUseClient >> > >> > Thanks in advance. >> > >> > Kevin >> > >
From: Kevin on 14 Jun 2010 11:41
Hi Willie, Thanks for the info, I had not come across that before. From the help file I had the impression that more than one field could be used, though it does state 'one or more individual clauses.' I tried swapping the condition around checking the Station first and then the State and this seems to work. Weird. As yet I have not started looking at the use of Indexes as I'm only really starting out with SQL and the current datasets are small. Thanks for the pointer. Thanks all. On a different question, I understand that using an SQL statement you can select a sub set of records to open. What is the best way of handling large collections of records? For example, if you have a table with one million records and the query returns one hundred thousand records. How would you open say the first thousand records, then if the user gets to the end of that set open the next block of 1000? The tables I am using are nowhere that big but I want to try that sort of thing out before they do, so that I know what's involved. Would you have some sample code you could share? Kevin "Willie Moore" <williem(a)wmconsulting.com> wrote in message news:hv59s2$puo$1(a)speranza.aioe.org: > Kevin, > > If I remember correctly, the filter can only use 1 field. Use the where > clause. > > Regards, > Willie > > "Kevin" <kdmurphy(a)eircom.net> wrote in message > news:BLoRn.167$K4.148(a)news.indigo.ie: > > > Erik, > > > > Using the WHERE clause would, if I understand it correctly, require the > > entire data set to be read again everytime it is changed. Using the > > filter I was hoping would enable the user to show/hide the records. The > > two parts of the filter (checking the State and checking the Station) > > work on their own. When combined using AND they fail and the Server > > Filter access returns the previous filter, not what I'm trying to > > achieve. > > > > I did use it in the WHERE clause to see if it would work and it doesn't > > there either. > > > > Kevin > > > > "ER!k \/!$$ER" <nospam(a)nospam.com> wrote in message > > news:87kgqfF59iU1(a)mid.individual.net: > > > > > > > Keven, > > > > > > You best use the the WHERE clause in the Sql statement. > > > > > > Erik > > > > > > "Kevin" <kdmurphy(a)eircom.net> schreef in bericht > > > news:%VLQn.161$K4.159(a)news.indigo.ie... > > > > > > I have an ADOServer created from an MS SqL SERVER table using the > > > > following statement: > > > > SELECT ClaimNo, PurchaseID, PurDate, Station, FuelType, Litres, Price, > > > > Cost, Notes, State FROM Fuel > > > > oSrv := AdoServer{ cSQL, oConn, adOpenDynamic, adLockOptimistic, > > > > adCmdText } > > > > > > > > There are some items which I want hidden by default, but the user can > > > > select to view them, so I apply the following filter before showing the > > > > window: > > > > oSrv:SetFilter( [State = null or State <> 'R'] ) > > > > > > > > and this works fine. > > > > > > > > I now want to add another condition to the filter so build it as follows: > > > > cFilter := [(State = null or State <> 'R') AND Station LIKE 'Topaz%'] > > > > oSrv:SetFilter( cFilter ) > > > > oSrv:Requery() > > > > > > > > When I apply this filter using oSrv:SetFilter it does not work. No change > > > > is seen in the ADOServer. Checking oSrv:Filter before assigning the new > > > > filter returns the correct string. After assigning the new filter it > > > > returns 0. > > > > > > > > I have tried assigning the filter using the Filter assign but it never > > > > seems to work, the access always returns 0 afterwards. > > > > > > > > Both the initial Filter and the additional one will work on their own but > > > > not when combined as above. > > > > > > > > What am I doing wrong or missing? > > > > > > > > VO28 SP3 > > > > Current version of VO2ADO > > > > Connection cursor location: AdUseClient > > > > > > > > Thanks in advance. > > > > > > > > Kevin > > > > > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 5194 (20100614) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com |