The last inserted row id in database is successfully inserted but to get that id is 0?

Question

This is the last row inserted method that return me the last id.

Code:

-(int)LastId {
    int noteID;

    NSString * sqlStr;

    sqlStr = [NSString stringWithFormat:@"select * from notes"];

    sqlite3_stmt *ReturnStatement = (sqlite3_stmt *) [self getStatement: sqlStr];

    while(sqlite3_step(ReturnStatement) == SQLITE_ROW){

        @try{
            noteID=[[NSString stringWithUTF8String:(char *)sqlite3_column_text(ReturnStatement, 0)] intValue];
        } @catch (NSException *ept) {
            NSLog(@"Exception in Method: '%@', Reason: %@", @"loadData", [ept reason]);
        }
    }

    return noteID;
}

This is the method that i inserted the last return id

-(void)AddNoteImages1:(NSString *)imageName1 andID:(int)notesID{
    notesID = [self  LastId];

    NSString *query = [NSString stringWithFormat:@"insert into image1(image1Name,notes_id) values('%@','%d')",imageName1,notesID];

    [self InsUpdateDelData:query];
}

This is the method that i want to get the last inserted row id

-(NSMutableArray *)loadImages1:(int)note_ID{
    NSMutableArray *dataArray =[[NSMutableArray alloc] init];

    NSString * sqlStr = [NSString stringWithFormat:@"SELECT * FROM image1 WHERE notes_id = %d",note_ID];

    sqlite3_stmt *ReturnStatement = (sqlite3_stmt *) [self getStatement: sqlStr];

    while(sqlite3_step(ReturnStatement) == SQLITE_ROW){

        @try{
            image1DC *imageData = [[image1DC alloc] init];
            imageData.image1ID = [[NSString stringWithUTF8String:(char *)sqlite3_column_text(ReturnStatement, 0)] intValue];

            imageData.image1Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(ReturnStatement, 1)];

            [dataArray addObject:imageData];
        } @catch (NSException *ept) {
            NSLog(@"Exception in %s, Reason: %@", __PRETTY_FUNCTION__, [ept reason]);
        }
    }

    return dataArray;
}

Answer 1

There are two ways of doing this one

1. Using the sqlite3_last_insert_rowid() method of sqlite
2. Using the MAX(your_auto_Incremental_primary_key_variable)

What i generally do is club the sqlite3_last_insert_rowid() call with an insert statement to get the last inserted rowID

- (BOOL)insertItem{

const char *query = "your insert statement";
sqlite3_stmt *sqlstatement = nil;

if (sqlite3_prepare_v2(dbreference, query, -1, &sqlstatement, NULL)==SQLITE_OK) {

    //Your insert code here

    float rowID = sqlite3_last_insert_rowid(dbreference);
    NSLog(@"Last inserted row id = %.0f",rowID);

    sqlite3_close(dbreference);
}

return YES;
}

where dbreference is a variable of type sqlite3

If the above solution is not suitable for your case then you can go with the Max(ID) approach here which would accepts the column name where you have stored your primary key which is auto incremental

- (int)getLastItemID{

const char *query = "select MAX(userid) from SampleTable";
sqlite3_stmt *sqlstatement = nil;
if (sqlite3_prepare_v2(dbreference, query, -1, &sqlstatement, NULL)==SQLITE_OK) {

    while (sqlite3_step(sqlstatement)==SQLITE_ROW) {

        int lastInsertedPrimaryKey = sqlite3_column_int(sqlstatement, 0);
        return lastInsertedPrimaryKey;
    }

     sqlite3_close(dbreference);

}

return 0;
}

Answer 2

The LastId function just steps through all rows in the table, without any specific order; it will return some random ID.

To get the last ID inserted in the same database connection, call the sqlite3_last_insert_rowid() function.

To get the ID with the largest value, execute SELECT MAX(ID) FROM ....