exception fetching last inserted id

Question

I want to get id of the last inserted row in sqlite but I am getting exception no such column on fetching it.I have posted my sqlite table,the method where I want to fetch id and the exception.I tried a lot of answers in stack but no use.please help me

public int getLastId() {
    int _id = 0;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query("calls", new String[] {BaseColumns._ID}, null, null, null, null, null);

    if (cursor.moveToLast()) {
        _id = cursor.getInt(0);
    }

    cursor.close();
    db.close();
    return _id;
}

exception

`E/AndroidRuntime: FATAL EXCEPTION: main
              Process: com.opentok.example.efflorescence.myaudiotranscription, PID: 20655
              android.database.sqlite.SQLiteException: no such column: _id (code 1): , while compiling: SELECT _id FROM calls

                  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:898)
                  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:509)
                  at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                  at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
                  at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
                  at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1346)
                  at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1193)
                  at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1064)
                  at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1232)
                  at com.opentok.example.efflorescence.myaudiotranscription.database.DBHelper.getLastId(DBHelper.java:79)
                  at com.opentok.example.efflorescence.myaudiotranscription.AudioCallActivity$3.run(AudioCallActivity.java:199)
                  at android.os.Handler.handleCallback(Handler.java:815)
                  at android.os.Handler.dispatchMessage(Handler.java:104)
                  at android.os.Looper.loop(Looper.java:194)
                  at android.app.ActivityThread.main(ActivityThread.java:5637)
                  at java.lang.reflect.Method.invoke(Native Method)
                  at java.lang.reflect.Method.invoke(Method.java:372)
                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:959)
                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:754)`

my sqlite table

public class DBHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "MyDBName.db";
public static final String CALLS_TABLE_NAME = "calls";
public static final String CALLS_COLUMN_ID = "id";
public static final String CALLS_COLUMN_NAME = "name";
public static final String CALLS_COLUMN_TIME = "time";
public static final String CALLS_COLUMN_TYPE = "type";
public int numRows;
ArrayList<String> array_list = new ArrayList<String>();

private HashMap hp;

public DBHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(
            "create table calls " +
                    "(id integer primary key, name text,time text, type text,archiveId text)"
    );
}

`id integer primary key` - Your `id` column name has no leading underscore. `BaseColumns._ID` does. Either fix your query, or fix the `CREATE TABLE` statement. — Mike M., Dec 01 '17 at 11:53
https://stackoverflow.com/questions/9902394/how-to-get-last-record-from-sqlite check this — user7176550, Dec 01 '17 at 11:53
@user7176550 This question is not for the last record but just for the last `id` value. — CL., Dec 01 '17 at 11:56
https://stackoverflow.com/questions/7575166/android-sqlite-get-last-insert-row-id please check this — user7176550, Dec 01 '17 at 11:59

Omkar · Answer 1 · 2017-12-01T12:09:29.233

0

You have to change primary key Columns id to _id try below code

public static final String CALLS_COLUMN_ID = "_id";//change here
public static final String CALLS_COLUMN_NAME = "name";
public static final String CALLS_COLUMN_TIME = "time";
public static final String CALLS_COLUMN_TYPE = "type";
public int numRows;
ArrayList<String> array_list = new ArrayList<String>();

private HashMap hp;

public DBHelper(Context context) {
    super(context, DATABASE_NAME, null, 2);// also change db version
}

change db version

edited Dec 01 '17 at 12:09

answered Dec 01 '17 at 11:54

Omkar

3,040
1
22
42

The [`BaseColumns` interface](https://developer.android.com/reference/android/provider/BaseColumns.html) does not have a member called `id`. – CL. Dec 01 '17 at 11:55
this is my String in Base Columns Class public static final String _ID = "_id";i changed _id to id but it shows the same error – user8601021 Dec 01 '17 at 12:01

score 0 · Accepted Answer · answered Dec 01 '17 at 11:54

You are trying to read a column named _id, but the column you actually have is id.

Anyway, instead of reading all table rows, it would be much faster to just read the last value directly:

long getLastId() {
    SQLiteDatabase db = this.getReadableDatabase();
    try {
        return DatabaseUtils.longForQuery(db,
                    "SELECT max(id) FROM calls", null);
    } finallly {
        db.close();
    }
}

exception fetching last inserted id

2 Answers2