Subject Re: SQL statement not working on the form
From Akshat Kapoor <akshat.kapoor@kapoorsons.in>
Date Mon, 31 Aug 2020 21:44:48 +0530
Newsgroups dbase.getting-started
Attachment(s) test_requery.wfm

> 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.
Good Evening Agostinho,
I am attaching a form which uses tables from dBaseSamples.
It has a grid and entryfield.

Try entering characters in the entryfield and see how the number of rows
changes

It is a simple demo form with skelton commands (i.e. just enough
commands to display the functioning)

There is no end to further improvements.

I am sure you will be able to do those as per your requirements.
Line number 69 is
form.customers1.params["cstr"] = lower(ltrim(rtrim(this.value))+ "%")
lower() has been added here and in sql to make it case insensitive.
It is presently set to search for strings starting with


Change it to
form.customers1.params["cstr"] = "%" + lower(ltrim(rtrim(this.value))+ "%")

And it will mimmick the $ (contained in function)

form.customers1.params["cstr"] = "%" + lower(ltrim(rtrim(this.value)))
should make it search for strings ending in (But this has never been
checked hence not sure)

I hope this helps as an example
Regards
Akshat



** END HEADER -- do not remove this line
//
// Generated on 31-08-2020
//
parameter bModal
local f
f = new test_requeryForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class test_requeryForm of FORM
   with (this)
      height = 23.9545
      left = 9.8571
      top = 0.0
      width = 119.5714
      text = ""
   endwith

   this.DBASESAMPLES1 = new DATABASE(this)
   with (this.DBASESAMPLES1)
      left = 17.0
      top = 2.0
      width = 11.0
      height = 1.0
      databaseName = "DBASESAMPLES"
      active = true
   endwith

   this.CUSTOMERS1 = new QUERY(this)
   with (this.CUSTOMERS1)
      left = 18.0
      top = 2.0
      width = 9.0
      height = 1.0
      database = form.dbasesamples1
      sql = "select * from CUSTOMERS.DBF where lower(company) like :cstr"
      params["cstr"] = "%"
      active = true
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.customers1.rowset
      allowEditing = false
      anchor = 1        // Bottom
      height = 14.0
      left = 0.0
      top = 9.8636
      width = 119.2857
   endwith

   this.ENTRYFIELD1 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD1)
      onKey = class::ENTRYFIELD1_ONKEY
      height = 1.0
      left = 37.0
      top = 3.5
      width = 33.0
      value = ""
   endwith


   function ENTRYFIELD1_onKey(nChar, nPosition,bShift,bControl)
      form.customers1.params["cstr"] = lower(ltrim(rtrim(this.value))+ "%")
      form.customers1.requery()
      return

endclass