BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  TADODataset.Filter? Goto page 1, 2  Next          BorlandTalk.com Forum Index -> Delphi Databases (ADO) View previous topic :: View next topic   Author Message Sven Pran Guest Posted: Sat Oct 21, 2006 1:36 am    Post subject: TADODataset.Filter? I use the following SQL statement to retrieve records from a local Access database and display them in a DBGrid: SELECT spillernummer, fornavn, etternavn FROM mpData WHERE fornavn LIKE "AB%" AND etternavn LIKE "DE%" ORDER BY etternavn, fornavn, spillernummer This works fine, but I am wondering if I rather than execute the same SQL statement each time I modify the text 'AB' or 'DE' would be better off by using the filter property of TADODataset? I always start the application retrieving the entire dataset, i.e. the WHERE part of the statement is: WHERE fornavn LIKE "%" AND etternavn LIKE "%". I have tried to find some information in the documentation on the advantage or disadvantage of using "Filter" and also on how to code the filter in the above case ('Include all records where the values in the named field begin with the characters specified'). Sadly to say I haven't found any good answer to either question and my attempts of coding filter by intuition have been greatly unsuccessful. Should I bother with TADODataset.Filter at all, and in case how do I code the value of that property? Or should I just continue using SQL SELECT over and over again within the application? Comments appreciated. regards Sven Back to top Bill Todd Guest Posted: Sat Oct 21, 2006 2:34 am    Post subject: Re: TADODataset.Filter? The fact that you want to filter the dataset suggests that there are a lot of records. If so, why do you want to retrieve all of them initially? Users never want to look through more than 100 records manually. There is no reason not to use the WHERE clause to select the records you want. -- Bill Todd (TeamB) Back to top Sven Pran Guest Posted: Sat Oct 21, 2006 8:11 am    Post subject: Re: TADODataset.Filter? "Bill Todd" <no (AT) no (DOT) com> wrote in message news:453940f4$1 (AT) newsgroups (DOT) borland.com... Quote: The fact that you want to filter the dataset suggests that there are a lot of records. If so, why do you want to retrieve all of them initially? Users never want to look through more than 100 records manually. There is no reason not to use the WHERE clause to select the records you want. -- Bill Todd (TeamB) Good question, the dataset is a membership directory with about 30000 entries. I have two edit fields where the user can type in the leading characters in the given name and surname and the display (DBGrid) continuously reflects what has been typed. Performance is quite OK so I don't fancy an additional "Run" button that must be pressed each time the user wants to see the effrect of his filtering so far. Instead I execute the SQL SELECT statement from inside the OnChange event handler for the edit fields. I did wonder if it would be more efficient, once I had the entire table loaded to my TADODataset, to control the display with the filter property rather than by re-issuing the SQL statement, but when I tried "filter" I could not get it to work? I suspect that my "problem" was "which character(s) is/are used as wildcard in the filter text"? regards Sven Back to top Bill Todd Guest Posted: Sat Oct 21, 2006 7:21 pm    Post subject: Re: TADODataset.Filter? If you are using a client-side cursor, which loads the entire result set into local memory, then a filter may be faster. -- Bill Todd (TeamB) Back to top Sven Pran Guest Posted: Sat Oct 21, 2006 8:09 pm    Post subject: Re: TADODataset.Filter? "Bill Todd" <no (AT) no (DOT) com> wrote in message news:453a2ceb$1 (AT) newsgroups (DOT) borland.com... Quote: If you are using a client-side cursor, which loads the entire result set into local memory, then a filter may be faster. -- Bill Todd (TeamB) Thanks, I have made some tests (and documentation searches) but I cannot get filtering on partial text to work? name = 'smith' returns all (and only) the records where the field 'name' value is exactly 'smith' (or 'Smith'), but name = 'sm*' (or name = "sm*") returns no records at all? I have not been able to find and inspect the property ADODataset1.FilterOptions in the object inspector, but I have every reason to believe that it is empty, which according to the help system is the default implying that the asterisk is treated as a wildcharacter representing any number of any characters. At present I am stuck. regards Sven Back to top Bill Dekleris Guest Posted: Sat Oct 21, 2006 9:24 pm    Post subject: Re: TADODataset.Filter? The wildcard charcter is the asterisk, but you should use the "Like" operator. Thus, if you want to make a partial search using TAdoDataset filter, you should use a command like this: ADOTable1.Filter := 'name Like bi*'; // name starts from "bi" or ADOTable1.Filter := 'name Like *bi*'; // name contains "bi" Regards, Bill Dekleris. ----------------------------------------------------------------------------------------- http://www.infosnap.eu Home page of InfoSnap - the powerful, all-purpose information and knowledge-base manager. ----------------------------------------------------------------------------------------- Sven Pran wrote: Quote: "Bill Todd" <no (AT) no (DOT) com> wrote in message news:453a2ceb$1 (AT) newsgroups (DOT) borland.com... If you are using a client-side cursor, which loads the entire result set into local memory, then a filter may be faster. -- Bill Todd (TeamB) Thanks, I have made some tests (and documentation searches) but I cannot get filtering on partial text to work? name = 'smith' returns all (and only) the records where the field 'name' value is exactly 'smith' (or 'Smith'), but name = 'sm*' (or name = "sm*") returns no records at all? I have not been able to find and inspect the property ADODataset1.FilterOptions in the object inspector, but I have every reason to believe that it is empty, which according to the help system is the default implying that the asterisk is treated as a wildcharacter representing any number of any characters. At present I am stuck. regards Sven Back to top Bill Dekleris Guest Posted: Sat Oct 21, 2006 9:42 pm    Post subject: Re: TADODataset.Filter? Oops! I forgot to quote the search string! The correct syntax would be: // name starts from "bi" ADOQuery1.Filter := 'name Like ' + QuotedStr('bi*'); // name contains "bi" ADOQuery1.Filter := 'name Like ' + QuotedStr('*bi*'); Sorry for the error! Bill. ----------------------------------------------------------------------------------------- http://www.infosnap.eu Home page of InfoSnap - the powerful, all-purpose information and knowledge-base manager. ----------------------------------------------------------------------------------------- Bill Dekleris wrote: Quote: The wildcard charcter is the asterisk, but you should use the "Like" operator. Thus, if you want to make a partial search using TAdoDataset filter, you should use a command like this: ADOTable1.Filter := 'name Like bi*'; // name starts from "bi" or ADOTable1.Filter := 'name Like *bi*'; // name contains "bi" Regards, Bill Dekleris. Back to top Sven Pran Guest Posted: Sat Oct 21, 2006 11:44 pm    Post subject: Re: TADODataset.Filter? "Bill Dekleris" <quasar (AT) hol (DOT) gr> wrote in message news:453a4e00 (AT) newsgroups (DOT) borland.com... Quote: Oops! I forgot to quote the search string! The correct syntax would be: // name starts from "bi" ADOQuery1.Filter := 'name Like ' + QuotedStr('bi*'); // name contains "bi" ADOQuery1.Filter := 'name Like ' + QuotedStr('*bi*'); Sorry for the error! Bill. No problem, and thanks for the clarification. I did try the LIKE operator as in SQL statements but I believe I tried using the percent sign as wildcard (also as in SQL syntax). And as 'LIKE' is not mentioned in the documentation for 'filter', not even where partial string filtering is concerned I didn't recognize the possibility that it was applicable after all. Anyway, with the deficient documentation in the current Delphi online help there are comments like yours that give me progress in understanding the new features in Delphi. I have only recently begun trying out ADO and am most grateful for all the help I receive here. (My experience with Delphi/Pascal as such goes back to TP version 3 around 1985!) regards Sven Back to top Sven Pran Guest Posted: Sun Oct 22, 2006 5:18 am    Post subject: Re: TADODataset.Filter? "Bill Dekleris" <quasar (AT) hol (DOT) gr> wrote in message news:453a4e00 (AT) newsgroups (DOT) borland.com... Quote: Oops! I forgot to quote the search string! The correct syntax would be: // name starts from "bi" ADOQuery1.Filter := 'name Like ' + QuotedStr('bi*'); // name contains "bi" ADOQuery1.Filter := 'name Like ' + QuotedStr('*bi*'); Sorry for the error! Bill. I tried this tonight and got a most surprising result: No error message and no filtering? I single-stepped the application and could see that ADODataset1 was "loaded" with a separate SQL SELECT statement. Then whenever I changed the filter mask (in an edit box) I could see that the following statements were executed 1: ADODataset1.Filtered := false; (typically) 2: ADODataset1.Filter := 'name LIKE ' + QuotedStr('bi*'); 3: ADODataset1.Filtered := true; but the DBGrid display did not change, it still displayed the entire dataset? Now I am lost? (It is 2:15 AM here and I am going to bed.) regards Sven Back to top Dennis Passmore Guest Posted: Sun Oct 22, 2006 1:40 pm    Post subject: Re: TADODataset.Filter? The following code works fine for me using either a ADOQuery or ADODataSet. The actual SQL text was 'select * from Employees' from the MsSql NorthWind database. procedure TForm1.Button1Click(Sender: TObject); begin // ADOQuery1.Close; // ADOQuery1.Filter := 'LastName Like ' + QuotedStr('Fu*'); //Fuller // ADOQuery1.Filtered := true; // ADOQuery1.Open; ADODataSet1.Close; ADODataSet1.Filter := 'LastName Like ' + QuotedStr('Fu*'); //Fuller ADODataSet1.Filtered := true; ADODataSet1.Open; end; Back to top Sven Pran Guest Posted: Sun Oct 22, 2006 2:14 pm    Post subject: Re: TADODataset.Filter? "Dennis Passmore" <dennis_passmore (AT) nospam_yahoo (DOT) com> wrote in message news:lfbmj2lpbs5k0m9ujqb13smlor6o08onvi (AT) 4ax (DOT) com... Quote: The following code works fine for me using either a ADOQuery or ADODataSet. The actual SQL text was 'select * from Employees' from the MsSql NorthWind database. procedure TForm1.Button1Click(Sender: TObject); begin // ADOQuery1.Close; // ADOQuery1.Filter := 'LastName Like ' + QuotedStr('Fu*'); //Fuller // ADOQuery1.Filtered := true; // ADOQuery1.Open; ADODataSet1.Close; ADODataSet1.Filter := 'LastName Like ' + QuotedStr('Fu*'); //Fuller ADODataSet1.Filtered := true; ADODataSet1.Open; end; Thanks, I didn't close my ADODataset, I only set 'filtered' to false before changing 'filter'. (That was how I understood the little sample code provided with the online help on TADODataset.Filter). But if I close the dataset will then not also the SQL SELECT command be executed again when I reopen the dataset? If that is so I cannot see any reason for using filter instead of just SQL SELECT ... WHERE... ? regards Sven Back to top Bill Dekleris Guest Posted: Sun Oct 22, 2006 2:27 pm    Post subject: Re: TADODataset.Filter? Sven, are you sure your DBGrid is connected to the correct DataSource / DataSet? I know it's a stupid remark, but sometimes we overlook such details when engaging in furious testing... The code I sent you works for me. To be exact, here is the event handler for the "filter" button click: procedure TForm1.Button2Click(Sender: TObject); begin // ADOQuery1.Filtered := false; ADOQuery1.Filter := 'name like ' + QuotedStr(edit1.Text); ADOQuery1.Filtered := true; end; Notice that I don't even need to enable "ADOQuery1.Filtered := false". It works either way. Also, after Googling a bit, I came across the following (older) post: http://groups.google.gr/group/comp.lang.pascal.delphi.databases/browse_thread/thread/6e11209f42079578/511e3467a8adfafc%23511e3467a8adfafc where I found out that the wildcard characters depend on the version of the Jet driver you use. Some versions support * and ?, others % and _. So I tested it, and realized that BOTH * and % wildcards work (using Microsoft.Jet.OLEDB.4.0 provider)! Please let me know if you made any progress. Regards, Bill Dekleris. ----------------------------------------------------------------------------------------- http://www.infosnap.eu InfoSnap · the powerful, all-purpose information and knowledge-base manager. ----------------------------------------------------------------------------------------- Sven Pran wrote: Quote: "Bill Dekleris" <quasar (AT) hol (DOT) gr> wrote in message news:453a4e00 (AT) newsgroups (DOT) borland.com... Oops! I forgot to quote the search string! The correct syntax would be: // name starts from "bi" ADOQuery1.Filter := 'name Like ' + QuotedStr('bi*'); // name contains "bi" ADOQuery1.Filter := 'name Like ' + QuotedStr('*bi*'); Sorry for the error! Bill. I tried this tonight and got a most surprising result: No error message and no filtering? I single-stepped the application and could see that ADODataset1 was "loaded" with a separate SQL SELECT statement. Then whenever I changed the filter mask (in an edit box) I could see that the following statements were executed 1: ADODataset1.Filtered := false; (typically) 2: ADODataset1.Filter := 'name LIKE ' + QuotedStr('bi*'); 3: ADODataset1.Filtered := true; but the DBGrid display did not change, it still displayed the entire dataset? Now I am lost? (It is 2:15 AM here and I am going to bed.) regards Sven Back to top Sven Pran Guest Posted: Sun Oct 22, 2006 4:22 pm    Post subject: Re: TADODataset.Filter? Thanks for your efforts, I shall be doing more tests later today, and in the meantime: 1: I should be most surprised if there is anything wrong in the connections between Dataset, Datasource and DBGrid. After all I have the correct results when I do not use the filter property? They are connected as much as possible already at compile- time (I shall triple-check this though). Also my filter attempts worked as expected, but only for exact matches when I used equal sign instead of the word LIKE (and wildcards probably treated as significant characters even though the filteroptions property is empty). 2: Is there a typo in your text below? Shouldn't the parameter to QuotedStr be (edit1.Text + '*') ? 3: I noticed that you seemed to use the Filter and Filtered properties either or both in TADODataset and in TADOQuery. I see no need to involve TADOQuery so all my filtering attempts are done on my TADODataset component. Do you know if there could be any trap here? (I have TADOConnection, TADODataset, TDataSource and TDBGrid all linked together and working as expected except for my difficulties with local filtering). regards Sven "Bill Dekleris" <quasar (AT) hol (DOT) gr> wrote in message news:453b399a (AT) newsgroups (DOT) borland.com... Quote: Sven, are you sure your DBGrid is connected to the correct DataSource / DataSet? I know it's a stupid remark, but sometimes we overlook such details when engaging in furious testing... The code I sent you works for me. To be exact, here is the event handler for the "filter" button click: procedure TForm1.Button2Click(Sender: TObject); begin // ADOQuery1.Filtered := false; ADOQuery1.Filter := 'name like ' + QuotedStr(edit1.Text); ADOQuery1.Filtered := true; end; Notice that I don't even need to enable "ADOQuery1.Filtered := false". It works either way. Also, after Googling a bit, I came across the following (older) post: http://groups.google.gr/group/comp.lang.pascal.delphi.databases/browse_thread/thread/6e11209f42079578/511e3467a8adfafc%23511e3467a8adfafc where I found out that the wildcard characters depend on the version of the Jet driver you use. Some versions support * and ?, others % and _. So I tested it, and realized that BOTH * and % wildcards work (using Microsoft.Jet.OLEDB.4.0 provider)! Please let me know if you made any progress. Regards, Bill Dekleris. ----------------------------------------------------------------------------------------- http://www.infosnap.eu InfoSnap · the powerful, all-purpose information and knowledge-base manager. ----------------------------------------------------------------------------------------- Sven Pran wrote: "Bill Dekleris" <quasar (AT) hol (DOT) gr> wrote in message news:453a4e00 (AT) newsgroups (DOT) borland.com... Oops! I forgot to quote the search string! The correct syntax would be: // name starts from "bi" ADOQuery1.Filter := 'name Like ' + QuotedStr('bi*'); // name contains "bi" ADOQuery1.Filter := 'name Like ' + QuotedStr('*bi*'); Sorry for the error! Bill. I tried this tonight and got a most surprising result: No error message and no filtering? I single-stepped the application and could see that ADODataset1 was "loaded" with a separate SQL SELECT statement. Then whenever I changed the filter mask (in an edit box) I could see that the following statements were executed 1: ADODataset1.Filtered := false; (typically) 2: ADODataset1.Filter := 'name LIKE ' + QuotedStr('bi*'); 3: ADODataset1.Filtered := true; but the DBGrid display did not change, it still displayed the entire dataset? Now I am lost? (It is 2:15 AM here and I am going to bed.) regards Sven Back to top Bill Dekleris Guest Posted: Sun Oct 22, 2006 5:26 pm    Post subject: Re: TADODataset.Filter? Sven Pran wrote: Quote: 2: Is there a typo in your text below? Shouldn't the parameter to QuotedStr be (edit1.Text + '*') ? Not really, I have done this on purpose - for flexibility during testing, I type the wildcard(s) within the TEdit box (so they are included in "edit1.Text"). Quote: 3: I noticed that you seemed to use the Filter and Filtered properties either or both in TADODataset and in TADOQuery. I see no need to involve TADOQuery so all my filtering attempts are done on my TADODataset component. Do you know if there could be any trap here? (I have TADOConnection, TADODataset, TDataSource and TDBGrid all linked together and working as expected except for my difficulties with local filtering). Yes, I have used TADOQuery, TADOTable, TADODataset interchangeably in my tests. They all behaved correctly. Unfortunately, I am not aware of any "traps" you should avoid when using TADOdataset. By the way, have you tried using TADOTable instead of TADODataset? Bill Dekleris. ----------------------------------------------------------------------------------------- http://www.infosnap.eu InfoSnap · the powerful, all-purpose information and knowledge-base manager. ----------------------------------------------------------------------------------------- Back to top Dennis Passmore Guest Posted: Sun Oct 22, 2006 6:36 pm    Post subject: Re: TADODataset.Filter? Quote: But if I close the dataset will then not also the SQL SELECT command be executed again Yes it will, I had it in my example but I should have removed it. One thing you should do is DisableControls and after updating the Filter then EnableControls so the DbGrid will then refresh itself. ADODataSet1.DisableControls; ADODataSet1.Filtered := false; ADODataSet1.Filter := 'LastName Like ' + QuotedStr('Fu*'); //Fuller ADODataSet1.Filtered := true; ADODataSet1.EnableControls; Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (ADO) All times are GMT Goto page 1, 2  Next Page 1 of 2   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.