Android sqlite delete row failure

Question

I'm trying to delete a row in a table where I know the row exists. I tried:

final String s = "DELETE from %s where Name = '%s'";        
String sql = String.format(s, GetTableName(), sListName);
Cursor cr= GetUserDb().GetSQLiteDb().rawQuery(sql, null);

and I tried:

String sWhere = String.format("Name = '%s'", sListName);
long lRowsUpdated = GetUserDb().GetSQLiteDb().delete(GetTableName(), sWhere, null);


** sWhere >> Name = 'groceries' **
** sql >> DELETE from Lists where Name = 'groceries' **

There is no crash or logcat exception but the row is still not deleted. Is there something I'm missing? Maybe I have a lock on it somewhere else or I need to set certain permissions in my Manifest or something?

ALSO my primary key is the sListName which is TEXT not sure if that matters or not... — Mike6679
– Mike6679, Commented Sep 11, 2011 at 5:52
umm... are you sure about the String.format()? Is it return the correct statement? Try String sql = String.format(s, new Object[]{GetTableName(), sListName}); — mihail
– mihail, Commented Sep 11, 2011 at 10:12
@mihail - thanks. I tried your suggestion in which sql = DELETE from Lists where Name = 'groceries' and STILL it doesn't remove the row and thee are no errors thrown . I think the issue is just something else entirely.... — Mike6679
– Mike6679, Commented Sep 11, 2011 at 14:19
try executing GetUserDb().GetSQLiteDb().execSQL(sql); but it is void function — mihail
– mihail, Commented Sep 11, 2011 at 15:00

Harald Wilhelm · Accepted Answer · 2011-09-11 05:49:32Z

2

Use delete() from SqliteDatabase - this returns the count of affected rows. E.g. delete(tablename, "name=?", new String[] { aString } )

answered Sep 11, 2011 at 5:49

Harald Wilhelm

6,71612 gold badges70 silver badges88 bronze badges

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

5 Comments

Mike6679 Over a year ago

Thanks I did try that as you can see in my original post and no luck.

Harald Wilhelm Over a year ago

Please show create statement and an example about the values. There's nothing wrong with the code - it must be the values.

Harald Wilhelm Over a year ago

a.) You use mixed case for tablenames (Lists), columns (Name) and your listvalue is lower case (groceries)? Is it really lowercase? b.) Is there an open cursor when you try to delete this row? c.) Is there some sort of referential integrity in your database (PK=grocerie is FK in another table)?

Mike6679 Over a year ago

I suspect Harald may be on to something here. I haven't had chance to test that out yet...thx for the tip Harald!

Mike6679 Over a year ago

Harald nailed it. The primary key was 'Groceries' and my query used 'groceries'. Darn case sensitivity!

IncrediApp · Accepted Answer · 2011-09-11 05:08:33Z

0

Try this:

       SQLiteStatement stmt = db.compileStatement("DELETE FROM " + getTableName() + "WHERE Name=?");
       stmt.bindString(1, sListName);
   stmt.execute();

answered Sep 11, 2011 at 5:08

IncrediApp

10.4k2 gold badges35 silver badges24 bronze badges

5 Comments

Mike6679 Over a year ago

Thanks, however, how do you know if stmt.execute() succeeds?

IncrediApp Over a year ago

For some reason Android doesn't have the method that returns the number of rows deleted (executeUpdateDelete()) in API versions lower than 11... But personally I'm deleting rows which I know are there. If it doesn't succeed, it means the row didn't exist - which means I get the desired result anyway...

IncrediApp Over a year ago

And don't forget to call stmt.close() at the end

Mike6679 Over a year ago

Didn't work . Same results, I'm thinking its not how I'm calling my sql delete but maybe how my table is structured....does primary key have to be _ID? My primary key is TEXT (sListName)

IncrediApp Over a year ago

AFAIK, your primary key has to be _id if you're using cursor adapters. Don't know if it's a requirement for other purposes, although my primary key is always _id due to this fact.

Sleepybear · Accepted Answer · 2011-09-11 06:14:45Z

0

if the column you're trying to have your key by is sListName you should have delete where sListName = '%s' unless you have another column that is called Name that you are trying to delete, but if that isn't your primary key you might end up getting two rows in your delete.

You can always use the emulator and an adb shell and just go run the sqlite3 shell command and try your SQL statements. If you type yours in and get 0 rows affected, you know you're statement is messed up, not your java.

If you're not using any built in CurorAdapters or ContentProviders your primary key does not need to be named _ID, it can be named whatever. As for the type, in SQLite3, it's really just a suggestion on how to cast it, you can put whatever data in whatever column you want.

answered Sep 11, 2011 at 6:14

Sleepybear

3621 silver badge9 bronze badges

2 Comments

Mike6679 Over a year ago

Thanks. My statement returns 0 rows which means it didn't delete anything. I do use : delete where sListName = 'Test' so I know my SQL statement is correct. I know the row is there because I run a query before this to verify that....I just can't figure what could be the issue. Could it be thet last query I ran is somehow causing issues?

Sleepybear Over a year ago

What is your table schema? I'm not convinced that it's not an issue of column name/variable name/query name used mix up that is causing the problem.

Collectives™ on Stack Overflow

Android sqlite delete row failure

3 Answers 3

5 Comments

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related