Android : SQLite search

Question

I am developing a simple app that inserts in database and searches in the inserted data

i created the database and i could see all the rows inserted...i want to search for the rows using the Name column

i am having an error and i don't know where is the error line

below is my code to create the database.

sampleDB =  this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);

sampleDB.execSQL("CREATE TABLE IF NOT EXISTS "+SAMPLE_TABLE_NAME +
                    " (Id INT(50), Name VARCHAR," +
                    " Price INT(50), Qty INT(50), Discount INT(50), Category INT(10));");

and here's the code to search in the database

Button saveButton = (Button) findViewById(R.id.search_button);

        saveButton.setOnClickListener(new View.OnClickListener() {

            public void onClick(View arg0) {

                String searchStr = search.getText().toString();

                Cursor c = sampleDB.rawQuery("SELECT Name, Price FROM "+SAMPLE_TABLE_NAME+" WHERE Name like '"+searchStr+"'", null);

                if (c != null ) { 
                    if  (c.moveToFirst()) {
                        do {
                            String name = c.getString(c.getColumnIndex("Name"));
                            int cat = c.getInt(c.getColumnIndex("Category"));
                            results.add("" + name + ",Age: " + cat);
                            Log.v("Fname", name);
                            Log.v("Cat", cat+"");
                        }while (c.moveToNext());
                    }
                }

            }
        });

knowing that searchStr has the name i want to search for in the database

here's the error i am having

Bad request for field slot 0,-1. numRows = 18, numColumns = 2
FATAL EXCEPTION: main
java.lang.IllegalStateException: get field slot from row 0 col -1 failed

any help please ?

Nermeen · Accepted Answer · 2012-10-17 10:19:02Z

3

You are selecting only name and price in the query:

Cursor c = sampleDB.rawQuery("SELECT Name, Price FROM "+SAMPLE_TABLE_NAME+" WHERE Name like '"+searchStr+"'", null);

and then you are trying to access category..

int cat = c.getInt(c.getColumnIndex("Category"));

So you should add category to your select statement:

Cursor c = sampleDB.rawQuery("SELECT Name, Price, Category FROM "+SAMPLE_TABLE_NAME+" WHERE Name like '"+searchStr+"'", null);

answered Oct 17, 2012 at 10:19

Nermeen

16k5 gold badges62 silver badges73 bronze badges

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

Comments

Sardor Dushamov · Accepted Answer · 2012-10-17 10:35:03Z

2

Use FTS table. FTS tables are designed so that any SELECT statement will work correctly. You can even search for specific text values or patterns directly with the == or LIKE operators. These will work, although they’ll be somewhat slow, since standard operators will require a full table scan.

The real power of the FTS system comes from a custom MATCH operator. This operator is able to take advantage of the indexes built around the individual text values, allowing very fast searches over large bodies of text. Generally, searches are done using a query similar to:

answered Oct 17, 2012 at 10:35

Sardor Dushamov

1,6674 gold badges17 silver badges45 bronze badges

Comments

Yaqub Ahmad · Accepted Answer · 2012-10-17 10:19:57Z

1

Issue is here: Your SQL is:

SELECT Name, Price FROM

But you are getting the value of Category

int cat = c.getInt(c.getColumnIndex("Category"));

answered Oct 17, 2012 at 10:19

Yaqub Ahmad

27.7k24 gold badges105 silver badges151 bronze badges

Comments

neknek mouh · Accepted Answer · 2012-12-18 08:08:53Z

1

You are trying to access Category field, but in your select query, there's no Category. You have to add it in your select query so you can use it.

answered Dec 18, 2012 at 8:08

neknek mouh

1,8028 gold badges28 silver badges58 bronze badges

1 Comment

Rana Osama Over a year ago

yes i actually discovered this stupid error i did :) thank you very much for you help

Collectives™ on Stack Overflow

Android : SQLite search

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related