CODEWITHSUNDEEP
androidsqlsqlitesql-delete
user3064141
user3064141

Reputation: 407

How to delete row from table based on text that is not primary key?

I have a table of in a SQLite database I am using in a wishlist app. I want to be able to store a history of items purchased in the database. Each item in the wish list has a detailed view which has "purchased" checkbox the user can click to indicate that item in the wish list has been purchased. I then have a separate "History" option in the options menu the user can click which brings up a listview that is populated by items in the database.

I have been able to get the listener for the checkbox to add the item to the database using it's name when I check the checkbox "purchased" on its detail view and the name shows up in the history listview for the database. But when I uncheck the box the sql command I am trying to use to delete the row from the database does not work. It use to give an error now it just does nothing. I have included the helper class and the portion of code where I add and delete the item using its name from the main code. I want to delete using the item's name directly instead of searching for the id number and then querying that to delete it. I'd rather just use the name directly since I am not worried about deleting duplicates. I have tried reading other posts and several references but can't figure out what I am missing.

ps. In the delete statement sql I tried without " ' " but got error. This way I get nothing. It just doesn't remove it from the view.

code from DBHelper

public class DBHelper extends SQLiteOpenHelper {

private static final String DB_NAME = "goodthings2.db";
private static final int DB_VERSION = 2;

public static final String TABLE_THINGS = "things";
public static final String C_ID = "_id";
public static final String C_TITLE = "title";
public static final String C_PRICE = "price";
public static final String C_DATE = "date";

public DBHelper(Context context){
    super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {

    final String sqlCreateTablePeople = 
            "CREATE TABLE " + TABLE_THINGS + 
            "( " + C_ID + " integer primary key autoincrement, " 
                 + C_TITLE + " text not null );";

    db.execSQL(sqlCreateTablePeople);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    final String sqlDropTablePeople = "DROP TABLE IF EXISTS " + TABLE_THINGS +  
";";
    db.execSQL(sqlDropTablePeople);
    onCreate(db);

}

}

section of code from fragment for wish list item detailed view:

    mPurchasedCheckBox.setOnCheckedChangeListener(
        new OnCheckedChangeListener(){
            public void onCheckedChanged(CompoundButton buttonView, 
boolean isChecked){

            mThing.setPurchased(isChecked);
            //Toast.makeText(getActivity(), 
mThing.getTitle().toString(), Toast.LENGTH_LONG).show();

            //String name = mThing.getTitle().toString();

            if(isChecked == true){
                Toast.makeText(getActivity(), "isChecked = true",  
Toast.LENGTH_LONG).show();

                // add the new data to the db
                DBHelper helper = new DBHelper(getActivity());
                SQLiteDatabase db = helper.getWritableDatabase();
                ContentValues cv = new ContentValues();
                cv.put(DBHelper.C_TITLE, 
mThing.getTitle().toString());
                //cv.put(DBHelper.C_PRICE, 
mThing.getPrice().toString());
                //cv.put(DBHelper.C_DATE, 
mThing.getDate().toString());
                //cv.put(DBHelper.C_ID, mThing.getId().toString());
                db.insert(DBHelper.TABLE_THINGS, null, cv);
                db.close();


            }else{

                Toast.makeText(getActivity(), "isChecked = false",  
Toast.LENGTH_LONG).show();

                /*SQLiteDatabase db = new  
DBHelper(getActivity()).getWritableDatabase();

                int rowsAffected = -1;

                rowsAffected = db.delete(DBHelper.TABLE_THINGS,  
DBHelper.C_ID + " = " + mThing.getId().toString(), null);

                Toast.makeText(getActivity(), "rowsAffected = " +  
rowsAffected, Toast.LENGTH_LONG).show();

                db.close();*/

                DBHelper helper = new DBHelper(getActivity());

                SQLiteDatabase db = helper.getWritableDatabase();

                db.execSQL("delete from " + DBHelper.TABLE_THINGS +  
" where " + DBHelper.C_TITLE + " = " + " ' " +  mThing.getTitle().toString() + " ' " );

            }
        }

    });

Upvotes: 0

Views: 255

Answers (1)

matiash
matiash

Reputation: 55360

The condition parameter is not properly built:

 DBHelper.C_TITLE + " = " + " ' " +  mThing.getTitle().toString() + " ' " );

The spaces between the quotes will cause the parameter to have extra spaces at the beginning and end (e.g. ' The Title ' instead of 'The Title', therefore the record to delete is not found).

Suggestion: Do not delete rows by building a custom SQL statement like this, use the SQLiteDatabase.delete() method instead. This will correctly escape the parameters, if needed. Otherwise your app will be exposed to SQL injection. As an extra bonus, the result will tell you how many records were deleted.

Upvotes: 1

Related Questions