String SQLquery won't retrieve data from SQLite database

Question

I have tried to retrieve data from SQL database. Before retrieve data, user must select a name in spinner. Here spinner value:

String name[] = {"Sandking","Bristleback","Sven","Tiny","Undying", "Naix","Weaver","Spectre","Lich"};

Next, i want to use the selected name from spinner for precondition on SQLquery: I put selected name, use this method:

String nameSelected = spinner1.getSelectedItem().toString();

Last, i tried to put that string ("nameSelected") on my SQLquery

public List<String> getAllRecord(String nameSelected) { 

    List<String> namagambar = new ArrayList<String>(); 
    String selectQuery = "SELECT * FROM " + TABLE_RECORD + " WHERE "
    + HERO_NAME +"="+nameSelected; 

    database = dbHelper.getReadableDatabase(); 
    cursor = database.rawQuery(selectQuery, null); 

    if (cursor.moveToFirst()) { 

        do { 

            namagambar.add(cursor.getString(5)); 
            } while (cursor.moveToNext()); 

    } 
    database.close(); 
    return namagambar; 

}

But it did't show any data from database. Looks like I made the mistake of putting a string nameSelected, is there someone who can tell my mistakes and how to fix it ?

here is my activity:

    public class help_activity extends Activity implements OnClickListener{

Spinner spinner1;
SQLiteConnector sqlConnect;
ListView lvUsers;
Button b1;



String colors[] = {"Sandking","Bristleback","Sven","Tiny","Undying", "Naix","Weaver","Spectre","Lich"}; 
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.layout_help);


    lvUsers = (ListView) findViewById(R.id.listView1);
    b1 = (Button) findViewById(R.id.btn1);
    sqlConnect = new SQLiteConnector(this);
    addListenerOnSpinnerItemSelection();



    final ArrayAdapter<String> adapter = new ArrayAdapter<String>(
            this, android.R.layout.simple_list_item_1, sqlConnect.getAllRecord(null));

    b1.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View v) {

            String nameSelected = spinner1.getSelectedItem().toString();
            if (nameSelected.equals("Sandking")) {
                lvUsers.setAdapter(adapter);
                adapter.notifyDataSetChanged();
            }
            // TODO Auto-generated method stub

        }
    });





}
public void addListenerOnSpinnerItemSelection() {
        spinner1 = (Spinner) findViewById(R.id.spinner1);

        ArrayAdapter<String> spinnerArrayAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_spinner_dropdown_item,colors );
        spinner1.setAdapter(spinnerArrayAdapter);
      }



@Override
public void onClick(View v) {


    /*if (v == b1) {


        ArrayAdapter<String> adapter = new ArrayAdapter<String>(

                this, android.R.layout.simple_list_item_1, sqlConnect.getAllRecord()); 

        lvUsers.setAdapter(adapter);
    */
}

}

You forgot to add single quotes: = '"+nameSelected + "'". For this reason you should use parametrized statements and not raw statements to avoid such a errors. — Simon Dorociak
– Simon Dorociak, Commented Apr 19, 2014 at 13:35

Phantômaxx · Accepted Answer · 2014-04-19 13:35:41Z

3

String parameters have to be single quoted.
Anyway, you can take advantage of Android's parameter binging and let it do that for you.

Change this

String selectQuery = "SELECT * FROM " + TABLE_RECORD + " WHERE "
+ HERO_NAME +"="+nameSelected;

database = dbHelper.getReadableDatabase();
cursor = database.rawQuery(selectQuery, null);

To

String selectQuery = "SELECT * FROM " + TABLE_RECORD + " WHERE " + 
    HERO_NAME +" = ?";

database = dbHelper.getReadableDatabase();
cursor = database.rawQuery(selectQuery, new String[]{nameSelected});

answered Apr 19, 2014 at 13:35

Phantômaxx

38.1k21 gold badges88 silver badges122 bronze badges

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

5 Comments

Labalumit Over a year ago

and on public List<String> getAllRecord(String colorSelected), should i delete "String colorSelected" or not ?

Phantômaxx Over a year ago

Do you mean public List<String> getAllRecord(String nameSelected)? No, leave it as it is. Otherwise you can't pass the nameSelected parameter used in the WHERE clause.

Labalumit Over a year ago

i have tried that, but it showing the error msesage. the bind value at index 1 is null android whats going on ?

Phantômaxx Over a year ago

Make sure that nameSelected is a not null string prior to pass it. If nothing is selected in your spinner, then spinner1.getSelectedItem().toString(); will be null. What I'd do to get rid of that message: if (!nameSelected == null){getAllRecord(String nameSelected)} (only call getAllRecords if nameSelected is not null)

Labalumit Over a year ago

i already edited my post, and show my activity, can you check where is my false ?

Collectives™ on Stack Overflow

String SQLquery won't retrieve data from SQLite database

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related