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

Daestrum

Expert
Licensed User
Longtime User
Could you not just enter the search string and get the program to add the %'s around it?
 
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