B4J Question SQL Wilcard problem

AndrewW

Member
Licensed User
Longtime User
This is the SQL query:

SQL1.ExecQuery2("SELECT CustSur,CustFore,Street,Broker,tktColour,tktNo,tktdate,Items,Cost,ID" _
& " FROM tbltickets WHERE CustSur LIKE ? AND CustFore LIKE ? ORDER BY CustSur,CustFore",ArrayAsString(txtCustSur.text, txtCustFore.text))

If I put in "be%" for CustSur I get (eg) "Beaumont" returned

If I put in "_be% , I get "Beaumont"

If I put in "%eau%, I get "Beaumont"

If I put in %e%, I get nothing.

Am I just exceeding the capacity of the tablet to return too many records? (Samsung Galaxy Note)
 

LucaMs

Expert
Licensed User
Longtime User
It should work also with %e%.

Make sure you enter the text exactly (I'd use variables, not the Text property).

You may also try this way (to see if the problem is ExecQuery2):
B4X:
SQL1.ExecQuery("SELECT CustSur FROM tbltickets WHERE CustSur LIKE '%e%'")
 
Upvote 0

AndrewW

Member
Licensed User
Longtime User
Thanks for that, however I have taken it a step further - txtCustSur is an EditText field where the user puts in the search name.
When I press '%' the field shows '%'. When I then press 'e' the field shows '%[space]e' ! the keyboard autocomplete/suggestion field above the pop-up keyboard shows '%e' (no space).
If I delete the space, the query then works, as expected. To defend myself the space isn't very obvious. The question now is where does the space come from?
I tried to reply to my own question - this also occurs with the £,$, Euro and Yen characters. It is clearly a 'feature' of the Samsung keyboard programming. I shall have to investigate it elsewhere. B4A is cleared of any fault, I think.
 
Upvote 0

AndrewW

Member
Licensed User
Longtime User
You have me there!
I still need to use the keyboard to enter the search term.
How does using a variable get round that?
Am I missing something obvious?
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
No, there are other reasons to use variables.

But it is also more convenient and clean carry out checks on the variables instead of the .Text
 
Upvote 0

AndrewW

Member
Licensed User
Longtime User
You could do that, but it would return records that were not wanted.

eg % + smith + % would give whitesmith, smithson etc, when the user didn't want them.

I have put in (using your suggestion):

strSur=txtCustSur.Text
strSur=strSur.Replace("%" & Chr(32),"%") - and put strSur in my SQL query.

This work around is OK for me.
 
Upvote 0
Top