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>