| Subject | Re: SQL statement not working on the form |
| From | Gaetano <gaetanodd@hotmail.com> |
| Date | Mon, 31 Aug 2020 17:00:56 +1000 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | agostinho.wfm, PRODUCTS.DBF, PRODUCTS.DBT |
Hi Agostinho, I am attaching an example that works for me using a pushbutton to change the SQL statement. It is important to have a % in the original query else I was getting memory access violations when trying to use % in the requery(). The form is under c:\dbasetutorial and the table and memo file under c:\dbasetutorial\tables but you can edit the paths in the Form to meet your needs (open the form in the source editor to update paths else you may get warning about files not existing) I will let the experts confirm that this is a valid way. Cheers, Gaetano. On 31/08/2020 13:19, AGOSTINHO wrote: > Dear Mervyn Bick > I appreciate your assistance to this issue. > I'm still very confused and don't see how to put this form work. > I don't know if it to much asking if you can make this form work from the top to the bottom, so I can just copy the whole thing and paste it to the form and make it work. > > My database is a products.dbf > > I want to add a GRID to the form that have to list the itemcode,brand,description fields > > An entryfield that will input the search criteria lets say ordered by brand name. > > Yes very interesting where you say that this can work showing the result on the GRID as you type into the entryfield. > > Once again thank you very much and be safe > Agostinho > > Mervyn Bick Wrote: > >> On 2020-08-30 05:02, AGOSTINHO wrote: >>> Dear Group can anyone help me with the following? >>> As you can see on the form bellow, I'm trying to do a kind of filter on my database on onlostFocus() using a SQL command line but it does not return any filtering on the GRID. >>> When using this method direct on the command window(DOT PROMPT) >>> >>> use products >>> use products.dbf >> >> USE is the dBASE command to open a table in a work area. >> >> These two lines do exactly the same thing if you are using .dbf files. >> You only need one of them. dBASE also works very well with Paradox >> files which have the .db extension. If you are trying to access a >> paradox file then you need to specify the extension e.g. use products.db >> >> >>> ag="AGU" >>> SELECT * FROM products WHERE merk LIKE '%&ag%' >>> browse >> >> When used like this SELECT (which is a localSQL command and not a dBASE >> command) also opens the table in a workarea. The first two lines are, >> therefore, redundant. >> >> The dBASE & macro operator is not normally recognised by SQL and, >> although it works here, you should be using a parameter with the SELECT >> command. To indicate that it is a parameter one uses a : in front of >> the variable name >> >> ag = '%AGU%' >> select * from products where merk like :ag >> browse >> >>> Than I get the filtering result by browsing the database file >>> Please let me know what I'm doing wrong within the form that I can't get the >>> result as from the command windows. >>> Thanks >> >> Using an entryfield's onLostFocus event handler to filter a rowset in a >> form can work but it's certainly not a particularly good idea. >> >> In your form you display the entire contents of your table in the grid >> when you open the form. When the entryfield loses focus a workarea is >> created in memory containing the relevant records (if any) but there is >> nothing on the form to display the records. >> >> A better idea (although there are other options as well) is to use the >> entryfield's onKey event handler in conjunction with a parameter driven >> query to filter the rowset displayed in the grid. >> >> It's never a good idea to hardcode a file's path in a query. Rather use >> a User BDE Alias in conjunction with a database object. If you >> completed the tutorial you should already have the User BDE Alias set up. >> >> >> this.DBASETUTORIAL1 = new DATABASE(this) >> with (this.DBASETUTORIAL1) >> left = 22.0 >> top = 2.0 >> width = 11.0 >> height = 1.0 >> databaseName = "DBASETUTORIAL" >> active = true >> endwith >> >> >> this.PRODUCTS1 = new QUERY(this) >> with (this.PRODUCTS1) >> left = 14.0 >> top = 12.0 >> database = form.dbasetutorial1 >> sql = 'select * from "PRODUCTS.DBF" where merk like :ag' >> params['ag'] = '%' //this will open with all records displayed >> active = true >> endwith >> >> >> function ENTRYFIELD1_onKey(nChar, nPosition,bShift,bControl) >> form.products1.params['ag'] = '%'+this.value+'%' >> form.products1.requery() >> return >> >> As you type each character into entryfield1 fewer an fewer records will >> be displayed in the grid. >> >> To make the filter not case sensitive use the following in the query >> >> sql = 'select * from "PRODUCTS.DBF" where lower(merk) like lower(:ag)' >> >> >> Mervyn. > | |
** END HEADER -- do not remove this line // // Generated on 31/08/2020 // parameter bModal local f f = new agostinhoForm() if (bModal) f.mdi = false // ensure not MDI f.readModal() else f.open() endif class agostinhoForm of FORM with (this) height = 23.1818 left = 81.1429 top = 6.4091 width = 80.7143 endwith this.QUERY1 = new QUERY(this) with (this.QUERY1) left = 9.0 top = 3.0 width = 5.0 height = 1.0 sql = "select 'Stock number','Vendor code',description from 'c:\dbasetutorial\tables\products.dbf' where description like '%'" active = true endwith this.TEXTLABEL1 = new TEXTLABEL(this) with (this.TEXTLABEL1) height = 1.0 left = 26.2857 top = 3.9091 width = 17.7143 text = "search argument" endwith this.ENTRYFIELD1 = new ENTRYFIELD(this) with (this.ENTRYFIELD1) height = 1.0 left = 49.2857 top = 3.9091 width = 16.7143 value = "" endwith this.GRID1 = new GRID(this) with (this.GRID1) dataLink = form.query1.rowset height = 11.8636 left = 4.0 top = 7.5 width = 73.0 endwith this.PUSHBUTTON1 = new PUSHBUTTON(this) with (this.PUSHBUTTON1) onClick = class::PUSHBUTTON1_ONCLICK height = 1.0909 left = 51.0 top = 5.5 width = 15.2857 text = "Apply filter" endwith this.rowset = this.query1.rowset function PUSHBUTTON1_onClick() form.query1.sql="select 'Stock number','Vendor code',description from 'c:\dbasetutorial\tables\products.dbf' where description like '"+form.entryfield1.value+"'" form.query1.requery() return endclass | |