CODEWITHSUNDEEP
iossqlsqlitesql-update
dammarpol
dammarpol

Reputation: 77

Error while updating sqlite row in ios

I'm getting "error while updating row" error when I'm trying to update sqlite row in iOS. Here is my update method:

-(BOOL) updateResultForLesson:(int)lessonId result:(int)result{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {

        sqlite3_stmt *statement;
        NSString *querySQL = [NSString stringWithFormat:@"update lessons set result=%d where id=%d",result, lessonId];

        const char *query_stmt = [querySQL UTF8String];

        sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL);


        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"updated");
        return YES;
        }
        else {
            sqlite3_reset(statement);
            NSLog(@"error while updating row");
            return NO;
        }
    }
    return NO;

}

and here insert method which works fine:

-(BOOL) addLesson:(NSString *)title levelId:(NSInteger)levelId categoryId:(NSInteger)categoryId{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        int result = 0;

        NSString *insertSQL = [NSString stringWithFormat:@"insert into lessons (title, level_id, category_id, result) values(\"%@\", \"%d\", \"%d\", \"%d\")",title, levelId, categoryId, result];

        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);

        if (sqlite3_step(statement) == SQLITE_DONE)
        {
           ;
            return YES;
        }
        else {
            sqlite3_reset(statement);
            return NO;
        }
    }
    return NO;

}

what am I doing wrong? can anyone help? Insert and select methods works well, I have only problem with updating rows.

Upvotes: 0

Views: 396

Answers (2)

Doro
Doro

Reputation: 2413

I suggest you to use sqlite3_bind_ functions:

SQLITE_API int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
SQLITE_API int sqlite3_bind_double(sqlite3_stmt*, int, double);
SQLITE_API int sqlite3_bind_int(sqlite3_stmt*, int, int);
SQLITE_API int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
SQLITE_API int sqlite3_bind_null(sqlite3_stmt*, int);
SQLITE_API int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
SQLITE_API int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
SQLITE_API int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
SQLITE_API int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);

and check if sqlite3_prepare_v2 returns 'OK' result.

Try this code:

-(BOOL) updateResultForLesson:(int)lessonId result:(int)result{
    const char *dbpath = [databasePath UTF8String];
     BOOL success = NO;
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {

        sqlite3_stmt *statement;

        const char *query_stmt = "update lessons set result= ? where id= ? ";

        if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) ==SQLITE_OK)
        {

            int bindRes = 0;
            bindRes = sqlite3_bind_int64(statement, 0, result);
            bindRes = sqlite3_bind_int64(statement, 1, lessonId);


            int state = sqlite3_step(statement);
            if (state == SQLITE_DONE)
            {
                success = YES;
            }
            else {
                NSLog(@"Failed to update record");
                success = NO;
            }
        }


        sqlite3_reset(statement);
        sqlite3_finalize(statement);

        return success;
}

WARNING: I do not know the type of 'result', so you must use correct bind function for statement from the list

Upvotes: 2

Sujay
Sujay

Reputation: 2520

Change your update query, you missed quote for value in query

NSString *querySQL = [NSString stringWithFormat:@"update lessons set result=\"%d\" where id=\"%d\"",result, lessonId];

Upvotes: 0

Related Questions