Using VO2ADO Filters
in [Visual Objects]
|
From: Kevin on 14 Jun 2010 13:35 Willie, Thanks for that. I have a lot to learn yet I think. But getting there. Kevin "Willie Moore" <williem(a)wmconsulting.com> wrote in message news:hv5lqf$egv$1(a)speranza.aioe.org: > 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 14:38 Sounds like your SQL was incorrect then if WHERE didn't find a row that should have been there. What did you get with SQL Management Studio? -- Ginny Caughey www.wasteworks.com
From: Willie Moore on 14 Jun 2010 14:50 Kevin, Something that Ginny mentioned and I want to mention it too. Always use the SQL management tool to test your SQL before you use it in your code. If you have SQLMaster, you can open up a query window and test it there too. I tend to write a lot of stored procedures. That makes it much easier to test/use. You use the SQL Management Studio to write your code, then you text it by executing the stored procedure from the management tool. Once you have that working, you just tell VO2ADO to execute the stored procedure and send in the parameters. The other big advantage to doing it like this is that you make it a lot easier if you want to make your app web enabled. All you do when you write your asp.net is to create an SQL command and pass in your stored procedure. I try to keep as much in the business layer as possible so I can switch out GUI as the client's needs change. Regards, Willie __________ 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: Kevin on 15 Jun 2010 07:43 Ginny, I used SQL Master to test the statement and it was returning nothing. The statement looked like this: Select * from Fuel where (State = null or State <> 'R') and Station = 'Topaz%' But when I changed it to: Select * from Fuel where Station = 'Topaz%' and (State = null or State <> 'R') It worked. Any idea why the second would work but the first wouldn't? Surely they should have the same result. Thanks. Kevin "Ginny Caughey" <ginny.caughey.online(a)wasteworks.com> wrote in message news:4c167664$0$6408$c3e8da3(a)news.astraweb.com: > Sounds like your SQL was incorrect then if WHERE didn't find a row that > should have been there. What did you get with SQL Management Studio? > > -- > > Ginny Caughey > www.wasteworks.com
From: Kevin on 15 Jun 2010 07:45
Willie, I was using the code in SQL Master and getting nowhere with it either. Stored procedures are an area I have not looked at yet. Kevin "Willie Moore" <williem(a)wmconsulting.com> wrote in message news:hv5tln$q0f$1(a)speranza.aioe.org: > Kevin, > > Something that Ginny mentioned and I want to mention it too. Always use > the SQL management tool to test your SQL before you use it in your code. > If you have SQLMaster, you can open up a query window and test it there > too. > > I tend to write a lot of stored procedures. That makes it much easier to > test/use. You use the SQL Management Studio to write your code, then you > text it by executing the stored procedure from the management tool. Once > you have that working, you just tell VO2ADO to execute the stored > procedure and send in the parameters. The other big advantage to doing > it like this is that you make it a lot easier if you want to make your > app web enabled. All you do when you write your asp.net is to create an > SQL command and pass in your stored procedure. I try to keep as much in > the business layer as possible so I can switch out GUI as the client's > needs change. > > Regards, > Willie > > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 5196 (20100614) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com |