How to delete a single row in android sqlite

Question

I have sqlite data represented on a ListView by a CustomListAdapter.On clicking a row an alert dialogue pops up that prompts the user to delete the single row from sqlite in my activity:

private void deleteDialog() {
        AlertDialog.Builder alertDialog = new AlertDialog.Builder(MyCart.this);
        alertDialog.setCancelable(false);
        alertDialog.setMessage("Delete item?");
        alertDialog.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                myDb.deleteSingleContact(toString());
            }
        });
        alertDialog.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                dialog.cancel();

            }
        });
        alertDialog.show();
    }

On my DBHelper.java:

 public void deleteSingleContact(String title){

        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(CONTACTS_TABLE_NAME, CONTACTS_COLUMN_TITLE + "=?", new String[]{title});
//KEY_NAME is a column name
    }

However the above code doesn't delete anything.I guess its the function i havent done correctly.Any suggestions?

Full DBHelper.java

public class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "MyDBName.db";
    public static final String CONTACTS_TABLE_NAME = "contacts";
    public static final String CONTACTS_COLUMN_ID = "id";
    public static final String CONTACTS_COLUMN_TITLE = "title";
    public static final String CONTACTS_COLUMN_AMOUNT = "amount";
    public static final String CONTACTS_COLUMN_DESC = "description";

    private HashMap hp;

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(
                "create table contacts " +
                        "(id integer primary key, title text,amount float,description text)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS contacts");
        onCreate(db);
    }

    public boolean insertContact(String title,  float amount, String description) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();

        contentValues.put("title", title);
        contentValues.put("amount", amount);
        contentValues.put("description", description);


        db.insert("contacts", null, contentValues);
        return true;
    }

    public Cursor getData(int id) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from contacts where id=" + id + "", null);
        return res;
    }

    public int numberOfRows() {
        String countQuery = "SELECT  * FROM " + CONTACTS_TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int cnt = cursor.getCount();
        cursor.close();
        return cnt;
    }

    public boolean updateContact(Integer id, String title, float amount, String description) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("title", title);
        contentValues.put("amount", amount);
        contentValues.put("description", description);

        db.update("contacts", contentValues, "id = ? ", new String[]{Integer.toString(id)});
        return true;
    }

    public void deleteContact() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("contacts", null, null);
        db.close();
    }

    public void deleteSingleContact(String title){

        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(CONTACTS_TABLE_NAME, CONTACTS_COLUMN_TITLE + "=?", new String[]{title});
//KEY_NAME is a column name
    }

    public boolean checkForTables() {
        boolean hasRows = false;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + CONTACTS_TABLE_NAME, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        if(count > 0)
            hasRows = true;
        db.close();
        return hasRows;
    }
    public ArrayList<ContactListItems> getAllContacts() {
        ArrayList<ContactListItems> contactList = new ArrayList<>();
        hp = new HashMap();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from contacts", null);
        res.moveToFirst();
        while (!res.isAfterLast()) {
                ContactListItems contactListItems = new ContactListItems();

                contactListItems.setTitle(res.getString(res
                        .getColumnIndex("title")));
                contactListItems.setAmount(res.getFloat(res
                        .getColumnIndex("amount")));
                contactListItems.setDescription(res.getString(res
                        .getColumnIndex("description")));
                contactList.add( contactListItems);
                res.moveToNext();
            }
        res.close();
        return contactList;
    }
    public int getTotalOfAmount(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c=db.rawQuery("SELECT SUM(amount) FROM " +CONTACTS_TABLE_NAME,null);
        c.moveToFirst();
        int i=c.getInt(0);
        c.close();
        return i;
    }
}

EDIT

obj.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
                deleteDialog();
                return true;
            }
        });
    }

Logcat Error:

android.database.sqlite.SQLiteException: near "=": syntax error (code 1): , while compiling: DELETE FROM contacts WHERE title=
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
            at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
            at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
            at com.softtech.stevekamau.buyathome.DBHelper.removeSingleContact(DBHelper.java:157)
            at com.softtech.stevekamau.buyathome.MyCart$6.onClick(MyCart.java:140)
            at com.android.internal.app.AlertController$ButtonHandler.handleMessage(AlertController.java:166)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:4745)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
            at dalvik.system.NativeStart.main(Native Method)

We don't do the debugging for you. Please add only the very relevant code parts. — juergen d
– juergen d, Commented Jul 1, 2015 at 12:58
I figured the column names might be relevant to those who are interested. — Steve Kamau
– Steve Kamau, Commented Jul 1, 2015 at 13:11

Max Tayler · Accepted Answer · 2015-07-21 17:08:30Z

21

+50

You can use SQL DELETE statement to choose which entry you want to delete. When using a string value as the selector, make sure you enclose the value in ''.

    /**
     * Remove a contact from database by title
     *
     * @param title to remove
     */
    public void removeSingleContact(String title) {
        //Open the database
        SQLiteDatabase database = this.getWritableDatabase();

        //Execute sql query to remove from database
        //NOTE: When removing by String in SQL, value must be enclosed with ''
        database.execSQL("DELETE FROM " + TABLE_NAME + " WHERE " + CONTACTS_COLUMN_TITLE + "= '" + title + "'");

        //Close the database
        database.close();
    }

Then whenever you want to remove an entry from the database use the following:

DBHelper dbHelper = new DBHelper(context);
dbHelper.removeSingleContact("CONTACT_TO_REMOVE_HERE");

UPDATE Assuming that your ListView dataset is an ArrayList containing ContactListItems, you can set an OnItemClickListener for your ListView and use it to get your selected title, and then remove it.

listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {

    @Override
    public void onItemClick(AdapterView<?> parent, View view, final int position, long id) {

        AlertDialog.Builder alertDialog = new AlertDialog.Builder(MyCart.this);
        alertDialog.setCancelable(false);
        alertDialog.setMessage("Delete item?");
        alertDialog.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                String title;

                title = arrayList.get(position).getTitle();

                dbHelper.removeSingleContact(title);

                //Update your ArrayList
                arrayList = dbHelper.getAllContacts();

                //Notify your ListView adapter
                adapter.notifyDataSetChanged();

            }
        });
        alertDialog.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                dialog.cancel();
            }
        });
        alertDialog.show();

    }

});

edited Jul 21, 2015 at 17:08

answered Jul 21, 2015 at 2:18

Max Tayler

3111 silver badge6 bronze badges

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

5 Comments

Steve Kamau Over a year ago

Here's my worry,you see i load my activity from a JSON,on listview item click one can add the item to the sqlite.I then have an activity that loads sqlite items onto a listview.I have items with different titles so i cannot hardcode what title exactly i would want to delete..You see my problem wth this line dbHelper.removeSingleContact("CONTACT_TO_REMOVE_HERE");

Max Tayler Over a year ago

I have added another snippet to my answer which may help you.

Steve Kamau Over a year ago

The method works like a charm!!But is there a way i can put it inside my deleteDialog() method??

Max Tayler Over a year ago

Yes, of course! you just need to move the code from deleteDialog() inside the onItemClick method. I have updated my answer to illustrate this.

Noor Hossain Over a year ago

Happy Coding Excellent !

Shreyash Mahajan · Accepted Answer · 2015-07-10 05:01:20Z

1

I guess the problem in your code is at below line:

myDb.deleteSingleContact(toString());

There should be the title as parameter. You are passing nothing. There has to be title that you want to remove from the database. something like below:

myDb.deleteSingleContact(title.toString());

Let me know if this not resolved your issue.

UPDATE

For deleting record from the database you must need to define some information like from database, which record you want to delete. It is required to at-least to identify the index of that record. E.g.: You have some title in the database like sport, Joy, Adventure, Fun and Action. Now if you want to delete the record of action from the database. So what you need to do is, You need to pass that title name as parameter in the above function. (deleteSingleContact("Action") in your case). So it will find the index where the title have name as "Action" and it will delete that record from the database.

Hope you got the point.

Let me know still if you have any doubt.

Sincerely, Shreyash

edited Jul 10, 2015 at 5:01

answered Jul 8, 2015 at 4:41

Shreyash Mahajan

23.7k35 gold badges120 silver badges190 bronze badges

6 Comments

Steve Kamau Over a year ago

Thanks for the reply but i cannot seem to resolve how to define "title" on my activity before i call onCreate()

Steve Kamau Over a year ago

I think i am getting your point...What if i tried this on my database---> public boolean deleteSingleContact(int id) { String where = CONTACTS_COLUMN_ID + "=" + id; SQLiteDatabase db = this.getWritableDatabase(); return db.delete(CONTACTS_TABLE_NAME, where, null) != 0; }

Shreyash Mahajan Over a year ago

@SteveKamau yes that is. You need to pass something to identify the record you want to delete. let me know if i can help you any other matter. Please accept the answer so it can help other as well.

Steve Kamau Over a year ago

Passing the parameter on my activity so as to be deleted from my database is where my problem is.I cannot seem to achieve or handle that.I am sorry but i am new at this.

Shreyash Mahajan Over a year ago

@SteveKamau now in that caase, I need more information like what you want to delete and what you have.

|

Blackbelt · Accepted Answer · 2015-07-19 18:47:13Z

If I were in you, I would use a subclass of CursorAdapter, with a CursorLoader, this way, every changes you bring to the database will be reflected immediately also on the ui (your ListView), without any additional line of code. To do so, you will have to add a column called _id, ("_id INTEGER PRIMARY KEY AUTOINCREMENT", in your create table query) to your database.

Assuming that your ListView's dataset is made of ContactListItems, when onItemLongClick, is invoked you can retrieve the object you long-pressed on with

ContactListItems item = (ContactListItems) arg0.getItemAtPosition(arg2);

Now, if you change the signature of deleteDialog, like

private void deleteDialog(final String title)

in onItemLongClick, you can call

 @Override
 public boolean onItemLongClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
     ContactListItems item = (ContactListItems) arg0.getItemAtPosition(arg2);
     deleteDialog(item.getTitle());
     return true;
 }

you will have also to change

alertDialog.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                myDb.deleteSingleContact(toString());
            }
});

with

alertDialog.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                myDb.deleteSingleContact(title);
            }
 });

Collectives™ on Stack Overflow

How to delete a single row in android sqlite

3 Answers 3

5 Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related