0

My sqlite query got error due to value of CharSequence s. In this value i have single quote(people's) which is not running properly. the query is running correct with double time single quote(people''s).

and code error is like below

07-27 14:48:32.042: E/SQLiteLog(28325): (1) near "s": syntax error
07-27 14:48:32.052: E/AndroidRuntime(28325): FATAL EXCEPTION: main
07-27 14:48:32.052: E/AndroidRuntime(28325): Process:
com.compare.WorldAtlasPro, PID: 28325 07-27 14:48:32.052:
E/AndroidRuntime(28325): android.database.sqlite.SQLiteException: near
"s": syntax error (code 1): , while compiling: SELECT Name FROM
country_info WHERE Name LIKE '%People's Republic of China%' AND Name
NOT LIKE ''AND Name NOT LIKE ''  AND Name NOT LIKE ''AND Name NOT LIKE
'' order by Name ASC

Here below of my code.

    country2.addTextChangedListener(new TextWatcher() {

        public void onTextChanged(CharSequence s, int start, int before, int count) {

            ArrayList<String> mArrayList=new ArrayList<String>();

            Cursor c=db.rawQuery("SELECT Name FROM country_info WHERE Name LIKE '%"+s+"%' AND" +" Name NOT LIKE'"+country1.getText().toString()+"'AND Name NOT LIKE'"+country3.getText().toString()+"' " +" AND Name NOT LIKE'"+country4.getText().toString()+"'AND Name NOT LIKE'"+country5.getText().toString()+"' " +    "order by Name ASC", null);

            while(c.moveToNext()){

                mArrayList.add(c.getString(0));

            }

            country2.setAdapter(new CustomAdapter(mCtx,mArrayList));


        }

        public void beforeTextChanged(CharSequence s, int start, int count,
                int after) {

        }

        public void afterTextChanged(Editable s) {

        }
    });
VenomVendor
  • 15,064
  • 13
  • 65
  • 96
Yuvaan Chauhan
  • 344
  • 1
  • 3
  • 12
  • i tried that solution but i got error as " The method replaceAll(String, String) is undefined for the type CharSequence " – Yuvaan Chauhan Jul 27 '16 at 09:31
  • Why don't you use **bound parameters**, instead? – Phantômaxx Jul 27 '16 at 09:32
  • Leave space in front of `AND` in two places @YuvaanChauhan – Arshak Jul 27 '16 at 09:32
  • Not solved @arshak – Yuvaan Chauhan Jul 27 '16 at 09:43
  • Your `country1.getText().toString()` , `country3.getText().toString()` , `country4.getText().toString()` , `country5.getText().toString()` is returning `empty` @YuvaanChauhan – Arshak Jul 27 '16 at 09:55
  • That question has more than one answer. – CL. Jul 27 '16 at 10:33
  • @Arshak return empty is not problem in query but problem is i want to change value from single comma (people's) to double time single comma (people''s) – Yuvaan Chauhan Jul 28 '16 at 04:08
  • @YuvaanChauhan Try to find out whether the arguments passed into the query contains `special character`, & if it contain's special character then use escape sequence... in your case, append it with another `'` . Try [this](http://stackoverflow.com/a/603579/5744335).. – Arshak Jul 28 '16 at 05:03
  • Done as **String sy =s.toString().replaceAll("'","''"); Cursor c=db.rawQuery("SELECT Name FROM country_info WHERE Name LIKE '%"+sy+"%' AND" +" Name NOT LIKE'"+country1.getText().toString().replaceAll("'","''")+"'AND Name NOT LIKE '"+country3.getText().toString().replaceAll("'","''")+"' " +" AND Name NOT LIKE "+country4.getText().toString().replaceAll("'","''")+"'AND Name NOT LIKE '"+country5.getText().toString().replaceAll("'","''")+"' " +"order by Name ASC", null);** – Yuvaan Chauhan Jul 28 '16 at 07:26

0 Answers0