Using VO2ADO Filters
in [Visual Objects]
|
From: Kevin on 14 Jun 2010 12:26 An index in this case the WHERE clause would not have worked, I think, as the SQL clauses in the filter were not working, I did try it on the WHERE clause. Swapping them around to work, weird. Thanks anyway. Kevin "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
From: Willie Moore on 14 Jun 2010 12:36 Kevin, In my apps, the only time I would bring in that large of a table would be in a batch job that doesn't have user interface. For my UI, I present the users with a form that they can fill out and it will bring back the records that match. I user a textbox where they can indicate the number of records to return. I use that in my SQL statement. Select top <n> <fields> from <table> Where <where clause> Order by <user selectable order> The result from this query is put into my browser. In VO it was bBrowser, in Vulcan I am using the Infragistics grid as I am running a total Winforms app, no vo compatible GUI. I do have bBrowser and ReportPro for Vulcan just in case I need them. Regards, Willie "Kevin" <kdmurphy(a)eircom.net> wrote in message news:g4sRn.170$K4.166(a)news.indigo.ie: > 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 __________ Information from ESET NOD32 Antivirus, version of virus signature database 5196 (20100614) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
From: Ginny Caughey on 14 Jun 2010 13:03 Willie, My experience is similar to yours except for us, we are at the point that we wait for users to report that some operation is slow for them because of their unique requirements, and then I add the index that's needed to make that faster. -- Ginny Caughey www.wasteworks.com
From: Ginny Caughey on 14 Jun 2010 13:05 Kevin, The WHERE clause only works on the SELECT statement and not on some filter you might subsequently add. I find I very seldom use filters at all with SQL. -- Ginny Caughey www.wasteworks.com
From: Kevin on 14 Jun 2010 13:33
Ginny, I think I was unclear in my earlier post. What I had meant was that using the conditions in the WHERE clause was not returning any records from the table, even though there is one that that does match. So from that perspective using the WHERE clause instead of the Filter would not have worked. As I mentioned earlier swapping the elements of the condition worked. I still don't understand why this is, but at least it works now. Sorry about any confusion I may have caused earlier. Thanks. Kevin "Ginny Caughey" <ginny.caughey.online(a)wasteworks.com> wrote in message news:4c1660b2$0$5553$c3e8da3(a)news.astraweb.com: > Kevin, > > The WHERE clause only works on the SELECT statement and not on some filter > you might subsequently add. I find I very seldom use filters at all with > SQL. > > -- > > Ginny Caughey > www.wasteworks.com |