B4J Question SQL Wilcard problem

[AndrewW]

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]

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):

SQL1.ExecQuery("SELECT CustSur FROM tbltickets WHERE CustSur LIKE '%e%'")

 

[AndrewW]

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.

 

[LucaMs]

This is another reason to use variables.

 

[AndrewW]

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?

 

[LucaMs]

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

 

[Daestrum]

Could you not just enter the search string and get the program to add the %'s around it?

 

[LucaMs]

Could you not just enter the search string and get the program to add the %'s around it?

That's right.

This is what I did for a my Custom View (by the way, I do not think it has been appreciated ).

The head pain is unbearable today!

 

[AndrewW]

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.