How to use IN operator with SQLite in Android?

Question

I have add the status column to my database table and I want to search result and display all the names of employee where status is open. When I run the application I'm getting exception SQliteException no such column xxx while compiling query.

I can't figure out how to do this.

Here is my code:

public void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.search_data);

        searchSpinner=(Spinner)findViewById(R.id.searchSpinner);
        btnSearch=(Button)findViewById(R.id.searchButton);
        newLeadDat_List = (ListView)findViewById(R.id.listView_DisplayData);

        displayNewLeadData();
        System.out.println("Data Displayed Succesfully!!!!!!!!!");

        searchSpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener()
        {
            @Override
            public void onItemSelected(AdapterView<?> parent, View v,
                    int position, long id) {
                // TODO Auto-generated method stub
                selectedSearchItem=parent.getItemAtPosition(position).toString().trim();
                System.out.println("selectedProductItem =" + selectedSearchItem);

            }

            @Override
            public void onNothingSelected(AdapterView<?> arg0) {
                // TODO Auto-generated method stub  
            }
        });

        btnSearch.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View arg0)
            {
                dbAdapter=new DataBase_Adapter(Serach_Data.this).open();
                dataBase = dbAdapter.getDatabaseInstance();


                    String query = "SELECT name FROM "+ DataBase_Adapter.TABLE_NEW_LEAD +" where "+ DataBase_Adapter.KEY_NEW_LEAD_STATUS +" IN  ("+ selectedSearchItem + ")";

                    cursor = dataBase.rawQuery(query , null);
                    adapter = new SimpleCursorAdapter(Serach_Data.this, 
                              R.layout.search_data_item_listview, 
                              cursor, 
                              new String[ ] {"name"}, 
                              new int[ ] {R.id.textView2_Name});

                    new_Lead_List_Adapter = new New_Lead_List_Adapter(Serach_Data.this  ,
                                 arrayList_newLead_Name );

                    newLeadDat_List.setAdapter(adapter);    
            }
        });
    }

    public void displayNewLeadData() 
    {
        dbAdapter=new DataBase_Adapter(Serach_Data.this).open();
        dataBase = dbAdapter.getDatabaseInstance();

        Cursor mCursor = dataBase.rawQuery("SELECT name FROM " + DataBase_Adapter.TABLE_NEW_LEAD, null);

        mCursor.moveToFirst();

        arrayList_newLead_Name.clear();

        {
            do 
            {   
                  arrayList_newLead_Name.add(mCursor.getString(mCursor.getColumnIndex(DataBase_Adapter.KEY_NEW_LEAD_NAME)));
            } while (mCursor.moveToNext());
        }

        new_Lead_List_Adapter = new New_Lead_List_Adapter(Serach_Data.this  ,
                                                        arrayList_newLead_Name );
        newLeadDat_List.setAdapter(new_Lead_List_Adapter);
        new_Lead_List_Adapter.notifyDataSetChanged();
        mCursor.close();
        System.out.printf("Data will Be Display." , new_Lead_List_Adapter);

    }
}

Here is my Log Cat stack trace info.

12-13 14:43:37.880: I/System.out(528): selectedProductItem =open
12-13 14:43:39.760: I/Database(528): sqlite returned: error code = 1, msg = no such column: open
12-13 14:43:39.760: D/AndroidRuntime(528): Shutting down VM
12-13 14:43:39.760: W/dalvikvm(528): threadid=1: thread exiting with uncaught exception (group=0x40015560)
12-13 14:43:39.810: E/AndroidRuntime(528): FATAL EXCEPTION: main
12-13 14:43:39.810: E/AndroidRuntime(528): android.database.sqlite.SQLiteException: no such column: open: , while compiling: SELECT name FROM new_lead where status IN  (open)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1356)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1324)
12-13 14:43:39.810: E/AndroidRuntime(528):  at com.lead_management_project.Serach_Data$2.onClick(Serach_Data.java:86)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.view.View.performClick(View.java:2485)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.view.View$PerformClick.run(View.java:9080)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.os.Handler.handleCallback(Handler.java:587)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.os.Handler.dispatchMessage(Handler.java:92)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.os.Looper.loop(Looper.java:123)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.app.ActivityThread.main(ActivityThread.java:3683)
12-13 14:43:39.810: E/AndroidRuntime(528):  at java.lang.reflect.Method.invokeNative(Native Method)
12-13 14:43:39.810: E/AndroidRuntime(528):  at java.lang.reflect.Method.invoke(Method.java:507)
12-13 14:43:39.810: E/AndroidRuntime(528):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
12-13 14:43:39.810: E/AndroidRuntime(528):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
12-13 14:43:39.810: E/AndroidRuntime(528):  at dalvik.system.NativeStart.main(Native Method)

The error shows that you have no such a column did you check the column name? — Viswanath Lekshmanan
– Viswanath Lekshmanan, Commented Dec 13, 2013 at 9:31

peter.petrov · Accepted Answer · 2013-12-13 09:58:48Z

2

Try

" where "+ DataBase_Adapter.KEY_NEW_LEAD_STATUS +" IN  ('"+ selectedSearchItem + "')";

I am assuming your column is of some string type.

If you have more than one status which you want to query for, then
you need to loop through your statuses and in this loop you should append
1) '
2) status value (some string like open, closed, etc.)
3) '
4) , (comma)
to some StringBuilder object, then remove the last , (comma) and then
produce an IN clause from this StringBuilder object. And make sure you
produce at least one status because "IN ()" is not valid in SQL.

edited Dec 13, 2013 at 9:58

answered Dec 13, 2013 at 9:28

peter.petrov

39.6k24 gold badges114 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

Viswanath Lekshmanan Over a year ago

Since its a column name ' is not needed! That may be useful if its a value

tazeenmulani Over a year ago

@Peter.petrov Column name is status and selectedSearchItem is the value which is insert in status column.

peter.petrov Over a year ago

@tazeenmulani I don't get what you mean. This is not a valid SQL statement: SELECT name FROM new_lead where status IN (open). This is quite obvious. What are your possible statuses? Are they strings or ints or ... ?

peter.petrov Over a year ago

@Arju Can you elaborate? What is a column name?

peter.petrov Over a year ago

@Arju Thank you for explaining me what "From and In shows"! I still didn't get much though. But thanks.

|

Renjith Krishnan · Accepted Answer · 2013-12-13 09:40:32Z

0

First uninstall the application and run it Again. The database is in emulator or phone is last used one, It is not updated. When you are uninstalling the application the database is also removed.

answered Dec 13, 2013 at 9:40

Renjith Krishnan

2,6786 gold badges33 silver badges54 bronze badges

Comments

Aniket Gupte · Accepted Answer · 2013-12-13 10:17:06Z

0

Since you mention you added column later, you'll need to change database version.

edited Dec 13, 2013 at 10:17

answered Dec 13, 2013 at 9:40

Aniket Gupte

1221 silver badge8 bronze badges

4 Comments

Budius Over a year ago

down-vote because further questions to clarify the OPs question should be make in the comment are below the OPs question.

Aniket Gupte Over a year ago

I know, but what can i do, I do not have enough reputation to comment.

Budius Over a year ago

patience, young padawan! =) ps. Removed the down-vote because you know the proper good practice.

Aniket Gupte Over a year ago

ok. I agree but as said earlier without 50 reputation it is not possible to comment even on authors question. I have reworded comment so it looks more like an answer.

Collectives™ on Stack Overflow

How to use IN operator with SQLite in Android?

3 Answers 3

19 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

19 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related