CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Suyash Saurabh
Suyash Saurabh

Reputation: 33

How to delete the full row of a SQLite database by entering only a string value of a particular column?

I am using this method to get query for this string:

public void deletedata(){
    p=srt.split(",");

    DatabaseHandler dba=new DatabaseHandler(this);
    for(String s:p) {
        dba.removeSingleproduct(s);
    }

Database method is :

public boolean removeSingleproduct(String name) {
        SQLiteDatabase db = this.getWritableDatabase();

        return db.delete(tablename, productinserted + "=" + name, null) > 0;
    }

I want to delete only one row by calling database as product inserted can have two same value.

Please help guys.

Upvotes: 1

Views: 51

Answers (2)

dotGitignore
dotGitignore

Reputation: 1627

Since you're deleting with a selectedValue String, add a single quote before and after the name

return db.delete(tablename, productinserted + " = '" + name + "'", null) > 0;

Or you can simplify your code.

public int removeSingleproduct(String name) {
    return getWritableDatabase().delete(tablename, productinserted + " = ?", new String[] { name });
}

Return int - the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause.

Upvotes: 1

MikeT
MikeT

Reputation: 57043

The following will use the name to locate all rows with the provided name but only delete the first according to it's rowid (unless WITHOUT ROWID has been specified [very likely not]). 

public boolean removeSingleproduct(String name) {
    boolean rv = false;
    SQLiteDatabase db = this.getWritableDatabase();

    Cursor csr = db.query(tablename,new String[]{"rowid AS dltid"},productinserted + "=?",new String[]{name},null,null,null);
    if(csr.moveToFirst()) {
        rv = db.delete(tablename,"rowid=?",new String[]{Long.toString(csr.getLong(csr.getColumnIndex("dltid")))}) > 0;
    }
    csr.close();
    return rv;
}

If you wanted to ensure that a row was only deleted if multiple rows with the same productinserted name existed, then you could simply change

    if(csr.moveToFirst()) { ........

to 

    if(csr.moveToFirst() && csr.getCount() > 1) { .......

Note! csr.moveToLast() could be used instead of csr.moveToFirst() it would probably then delete the newest addition rather than probably deleting the oldest addition.

If you think

but I haven't defined a column called rowid

then :-

Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "rowid" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "rowid", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

SQL As Understood By SQLite - ROWIDs and the INTEGER PRIMARY KEY

Upvotes: 0

Related Questions