CODEWITHSUNDEEP
javaandroidsqlite
Hiran Walawage
Hiran Walawage

Reputation: 2185

Android SQLite SELECT Query

I have taken String value from a EditText and set it inside SELECT QUERY after WHERE condition

As

TextView tv = (TextView) findViewById(R.id.textView3);
EditTextet2 et = (EditText) findViewById(R.id.editText1);

String name = et.getText().toString();

Cursor c = db.rawQuery("SELECT * FROM tbl1 WHERE name = '"+name+"'", null); 

c.moveToNext();

tv.setText(c.getString(c.getColumnIndex("email")));

But it doesn't work. Any suggestions?

Upvotes: 26

Views: 172240

Answers (6)

japhungshar7
japhungshar7

Reputation: 11

Detailed answer:

String[] selectionArgs = new String[]{name};
    Cursor c = db.rawQuery(
       "SELECT * FROM " + tabl1 + 
       " WHERE " + name + " = ? ", selectionArgs
    );

selectionArgs : this takes the 'name' you desire to compare with, as argrument.

Here note "A Cursor object, which is positioned before the first entry of the table you refer to".

So,to move to first entry :

c.moveToFirst();

getColumnIndex(String ColumnName) : this returns the zero-based column index for the given column name.

tv.setText(c.getString(c.getColumnIndex("email")));

In case, you want to go searching through multiple rows for a given name under 'name' column then use loop as below:

if (cursor.moveToFirst()){
    do{
        ////go traversing through loops
           
    }while(cursor.moveToNext());
}

This should solve the problem.

Upvotes: 1

thunderkill
thunderkill

Reputation: 146

 public User searchUser(String name) {
    User u = new User();
    SQLiteDatabase db = this.getWritableDatabase(); //get the database that was created in this instance
    Cursor c = db.rawQuery("select * from " + TABLE_NAME_User+" where username =?", new String[]{name});
    if (c.moveToLast()) {
        u.setUsername(c.getString(1));
        u.setEmail(c.getString(1));
        u.setImgUrl(c.getString(2));
        u.setScoreEng(c.getString(3));
        u.setScoreFr(c.getString(4));
        u.setScoreSpan(c.getString(5));
        u.setScoreGer(c.getString(6));
        u.setLevelFr(c.getString(7));
        u.setLevelEng(c.getString(8));
        u.setLevelSpan(c.getString(9));
        u.setLevelGer(c.getString(10));
        return u;

    }else {
        Log.e("error not found", "user can't be found or database empty");
        return u;
    }

}

this is my code to select one user and one only so you initiate an empty object of your class then you call your writable Database use a cursor in case there many and you need one here you have a choice Use : 1-

   if (c.moveToLast()) { } //it return the last element in that cursor

or Use : 2- 

 if(c.moveToFirst()) {  } //return the first object in the cursor

and don't forget in case the database is empty you'll have to deal with that in my case i just return an empty object

Good Luck

Upvotes: 1

Vladimir Monsanto
Vladimir Monsanto

Reputation: 552

This is a complete code for select statements.

SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery("SELECT column1,column2,column3 FROM table ", null);
if (c.moveToFirst()){
    do {
        // Passing values 
        String column1 = c.getString(0);
        String column2 = c.getString(1);
        String column3 = c.getString(2); 
        // Do something Here with values
    } while(c.moveToNext());
}
c.close();
db.close();

Upvotes: 39

CoderDecoder
CoderDecoder

Reputation: 445

Here is the below code.

String areaTyp = "SELECT " +AREA_TYPE + "  FROM "
            + AREA_TYPE_TABLE + " where `" + TYPE + "`="
            + id;

where id is the condition on which result will be displayed.

Upvotes: 1

Zach Lauzon
Zach Lauzon

Reputation: 151

Try using the following statement:

Cursor c = db.rawQuery("SELECT * FROM tbl1 WHERE name = ?", new String[] {name});

Android requires that WHERE clauses compare to a ?, and you then specify an equal number of ? in the second parameter of the query (where you currently have null).

And as Nambari mentioned, you should use c.moveToFirst() rather than c.moveToNext()

Also, could there be quotations in name? That could screw things up as well.

Upvotes: 15

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

Reputation: 5759

Try trimming the string to make sure there is no extra white space:

Cursor c = db.rawQuery("SELECT * FROM tbl1 WHERE TRIM(name) = '"+name.trim()+"'", null);

Also use c.moveToFirst() like @thinksteep mentioned.

Upvotes: 42

Related Questions