[Gambas-user] DataSource filter using Like operator

[Ernest Wilson]

I found the problem to be the syntax I was using. The correct syntax is 
documented in the SQLite docs.

As noted in the post from Lee, I did need a wildcard, but the wildcard 
is "%" instead of the traditional "*"

Here is the code that works properly:

Public Sub Button1_Click()
   'Find Button
   Dim $findtext As String
   Dim $filterstr As String
   'Assignment to a variable seems to be required for DB.Subst
   'also facilitates prepend & append of wildcard "%"
   $findtext = "%" & TextBox1.Text & "%"
   'Look for Find entry in first name or last name or company
   'See SQLite documentation for syntax of Like operator
   $filterstr = DB.Subst("LIKE(&1,LName) OR LIKE(&1,FName) OR 
LIKE(&1,Company)", $findtext)

   DataSource1.Filter = $filterstr
   DataBrowser1.Refresh
   FormatDataBrowser1 'Set column widths, header alignment, etc.

End

I will eventually test with mySQL, but I'm not ready yet.

Regards,
Ernest Wilson

On 05/05/2018 08:14 AM, PICCORO McKAY Lenz wrote:
> 2018-05-04 14:04 GMT-04:00 Ernest Wilson <gambasml at junkheaven.com 
> <mailto:gambasml at junkheaven.com>>:
>
>
>     On a form I have a DataBrowser displaying the content of an SQLite
>     database table containing a list of people. The fields consist of:
>
>
> hi,please can you make same test in a mysql and report feedback.. ??? 
> it might be a bug i guess!
>
> i use always odbc and have experience same problem in the past (and i 
> very angry due the so many commits to the mysql-puach module and lesss 
> to the others that are more significativelly)
>
>
>         ID - ID Number
>         FName - Persons first name and possibly an initial
>         LName - Persons surname
>         Company - Persons employer
>         Various other fields with persons information
>
>     Also, on the form are a TextBox for user entry, a button for
>     "Find" and a button for "Clear".
>
>     The desired action is as follows:
>
>     User enters any substring of the persons name (first name or
>     surname) in the TextBox and clicks the "Find" Button. The matching
>     records are displayed in the DataBrowser. When the user clicks the
>     "Clear" button, the DataBrowser displays all the records.
>
>     Example: If I have records for (1) Jack Smith, (2) Sally Simple,
>     (3) Jack B. Nimble, (4) John Dough, (5) Jack B. Quick, entering:
>
>     "Jack" should return records 1, 3, 5
>     "im" should return records 2, 3
>     "s" should return records 1, 2
>     "Jack B" should return records 3, 5
>
>     I have implemented the "Find" button as follows:
>
>     Public Sub Button1_Click()
>       'Find Button
>       Dim $findtext As String
>       Dim $filterstr As String
>
>       $findtext = TextBox1.Text 'Assignment to a variable seems to be
>     required for DB.Subst
>
>       $filterstr = DB.Subst("LName LIKE &1 OR FName LIKE &1",
>     $findtext) 'Look for Find entry in either First or Last name
>
>       TextBox2.Text = $filterstr 'Debugging output
>
>       DataSource1.Filter = $filterstr
>       DataBrowser1.Refresh
>       FormatDataBrowser1 'Set column widths, header alignment, etc.
>     End
>
>     The result I get is only an exact match of the string entered in
>     the TextBox, as if I had used "=" instead of "Like" as the
>     operator in the filter string.
>
>     I am obviously missing something, or have taken a wrong approach.
>     Suggestions will be appreciated.
>
>     Regards,
>
>     Ernest Wilson
>
>
>
>     --------------------------------------------------
>
>     This is the Gambas Mailing List:
>     https://lists.gambas-basic.org/listinfo/user
>     <https://lists.gambas-basic.org/listinfo/user>
>
>     Search the list:
>     https://lists.gambas-basic.org/cgi-bin/search.cgi
>     <https://lists.gambas-basic.org/cgi-bin/search.cgi>
>
>     Hosted by https://www.hostsharing.net
>
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20180505/249753b3/attachment.html>