Data not getting deleted from database using SQLite

Question

I have loaded some data in my tableview using SQLite database in the format 1) some value, 2) some value so on. 1 and 2 are primary key, this is my code for delete which I have put on the swipe on delete function of the table view

-(void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath
{
    [self openDB];
    NSString *idofTable = [NSString new];
    NSScanner *scanner = [[NSScanner alloc] initWithString:[self.tableLogView cellForRowAtIndexPath:indexPath].textLabel.text];
    [scanner scanUpToString:@")" intoString:&idofTable];

    NSLog(@"id=%@, text=%@", idofTable, [self.tableLogView cellForRowAtIndexPath:indexPath].textLabel.text);

    if(editingStyle==UITableViewCellEditingStyleDelete)
    {
        char *error;

        NSString *sql = [NSString stringWithFormat:@"DELETE FROM History WHERE 'id' = '%@'", idofTable];

        NSLog(@"%@",sql);

        if(sqlite3_exec(db, [sql UTF8String], NULL, NULL,&error)!=SQLITE_OK)
        {
            sqlite3_close(db);
            NSAssert(0,@"Could not create table");

        }
        else
        {
            NSLog(@"Data Deleted");
            sqlite3_exec(db, "COMMIT", NULL, NULL, &error);
        }
    }
    [tableLogView reloadData];
}

The log "Data Deleted" gets printed but the data doesnt get deleted in database nor in tableview. Any help appreciated. I dont know where the error is occuring, the query seems to be correct.

Please post the Log of the sql statemnt here. Is the ID correct? — Amin Negm-Awad
– Amin Negm-Awad, Commented Aug 14, 2013 at 7:31
how can you say the data not deleted, by reloading tableview? are you sure the values for tableview got updated? — Nazik
– Nazik, Commented Aug 14, 2013 at 7:32
you should also delete the record from your table data array to refresh the tableview. — iNeal
– iNeal, Commented Aug 14, 2013 at 7:34
@gamerlegend This comment is not related to your question. I would suggest you to used FMDB Framework for all your database operations. You may refer this answer stackoverflow.com/a/17268032/1017893 — icodebuster
– icodebuster, Commented Aug 14, 2013 at 7:44
Thanks to code of kapil I figured out that my delete statement worked when I remove the quotes 'id' as id. I did the insert and all using quotes now bit confused in using quotes. — Francis F
– Francis F, Commented Aug 14, 2013 at 8:48

Bug · Accepted Answer · 2013-08-14 07:49:33Z

1

Try This

 if(editingStyle==UITableViewCellEditingStyleDelete)
        {
            NSString *dbFilePath =[DBclass getDBPath];

            sqlite3_stmt *deleteStmt = nil;
         if(sqlite3_open([dbFilePath UTF8String], &database)==SQLITE_OK)
        {

        NSString *deleteString=[NSString stringWithFormat:@"delete from table where ID =%d",idValue];
        const char *sql =[deleteString UTF8String];

        if(sqlite3_prepare_v2(database, sql, -1, &deleteStmt, NULL) != SQLITE_OK)
            NSAssert1(0, @"Error while creating delete view statement. '%s'", sqlite3_errmsg(database));

        if(SQLITE_DONE != sqlite3_step(deleteStmt))
            NSAssert1(0, @"Error while deleting data. '%s'", sqlite3_errmsg(database));
        else
            NSLog(@"Success in deleting the medicine.");

        sqlite3_close(database);
    }
        }
        [tableLogView reloadData];

edited Aug 14, 2013 at 7:49

answered Aug 14, 2013 at 7:37

Bug

2,5602 gold badges23 silver badges38 bronze badges

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

5 Comments

Tarek Hallak Over a year ago

Na the code is not right, he doesn't even finalize nor closing the database ;)

Francis F Over a year ago

Thanks, actually my code now works too when I removed the quotes from the 'id', thanks to your code that helped me figure out the error. The value from database gets deleted now, but it doesnt update the table view even when I call the reloadData. Any ideas??

Bug Over a year ago

yes you do one more thing after delete from data base you delete value from array index before reload data like that [yourArray removeObjectAtIndex:indexPath.row]; [table reloadData];

Francis F Over a year ago

Tried it but throwing error, anyways I solved it by repopulating my database in the tablerow return event. Now I am getting error when deleting all data, my sql stmt is DELETE * FROM History. It throws Assertion failure error. Any ideas??

Bug Over a year ago

Bro don't use * when you fire delete statement try this DELETE FROM History

user2532315 · Accepted Answer · 2013-08-14 07:45:12Z

1

You arent finalising nor closing the Database.

sqlite3_finalize(stmt);

sqlite3_close(DB);

answered Aug 14, 2013 at 7:45

user2532315

Comments

Tarek Hallak · Accepted Answer · 2013-08-14 07:36:19Z

0

This is the right way to delete a row:

if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

   NSString *sql = [NSString stringWithFormat:@"DELETE FROM History WHERE 'id' = '%@'", idofTable];

   const char *del_stmt = [sql UTF8String];
   sqlite3_prepare_v2(contactDB, del_stmt, -1, & deleteStmt, NULL);
   if (sqlite3_step(deleteStmt) == SQLITE_DONE)
   {
       NSLog(@"Data Deleted");
       sqlite3_exec(db, "COMMIT", NULL, NULL, &error);
   }else {
       NSLog( @"Error: %s", sqlite3_errmsg(database) );
   }
   sqlite3_finalize(deleteStmt);
   sqlite3_close(contactDB);


}

answered Aug 14, 2013 at 7:36

Tarek Hallak

18.5k7 gold badges62 silver badges71 bronze badges

Collectives™ on Stack Overflow

Data not getting deleted from database using SQLite

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related