Android - sqlite.SQLiteException: no such column: _id

Question

I tried changing the version of my SQLite database. I do not think I am doing this right. My fetchAllMeds method is throwing an error because of a non-consistent _id column. My updateData should have updated my SQLite. I think I am missing something.

public class DBAdapter {
public static final String ROW_ID ="_id";
public static final String KEY_DRUGNAME = "drugname";
public static final String KEY_DOSE = "dose";
public static final String KEY_FREQUENCY = "frequency";
public static final String KEY_ROUTE = "route";
public static final String KEY_REFILLDATE = "refilldate";
public static final String KEY_EXPIRATIONDATE = "expirationdate";

private static final String DATABASE_NAME = "MymedlistDB";
private static final String DATABASE_TABLE = "mymedlist";
private static final int DATABASE_VERSION = 2;

private static final String TAG = "MedDBAdapter";
private DatabaseHelper mDatabaseHelper;
private SQLiteDatabase mDb;

private final Context mCtx;

private static final String DATABASE_CREATE =
        "CREATE TABLE if not exists " + DATABASE_TABLE + " (" +
                ROW_ID + "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                KEY_DRUGNAME + "," +
                KEY_DOSE + "," +
                KEY_FREQUENCY + "," +
                KEY_ROUTE + "," +
                KEY_REFILLDATE + "," +
                KEY_EXPIRATIONDATE + ")";



private static class DatabaseHelper extends SQLiteOpenHelper {
    public DatabaseHelper(Context context) {
        super( context, DATABASE_NAME, null, DATABASE_VERSION );
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.w(TAG, DATABASE_CREATE);
        db.execSQL( DATABASE_CREATE );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
        + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate( db );
    }
}

public DBAdapter(Context ctx){
    this.mCtx = ctx;
}

public DBAdapter open() throws SQLException{
    mDatabaseHelper = new DatabaseHelper( mCtx );
    mDb = mDatabaseHelper.getWritableDatabase();
    return this;
}
public void close(){
    if (mDatabaseHelper != null){
        mDatabaseHelper.close();
    }
}
public long createMed(String drugname, String dose, String frequency,
                      String route, String refilldate, String expirationdate){

    ContentValues initialValues = new ContentValues(  );
    initialValues.put(KEY_DRUGNAME, drugname);
    initialValues.put(KEY_DOSE, dose);
    initialValues.put(KEY_FREQUENCY, frequency);
    initialValues.put(KEY_ROUTE, route);
    initialValues.put(KEY_REFILLDATE, refilldate );
    initialValues.put(KEY_EXPIRATIONDATE, expirationdate);

    return mDb.insert( DATABASE_TABLE, null, initialValues );
}

public int updateData(String drugname, String dose, String frequency,
                      String route, String refilldate, String expirationdate){
    ContentValues medsUpdate = new ContentValues(  );
    medsUpdate.put(DBAdapter.KEY_DRUGNAME,drugname);
    medsUpdate.put(DBAdapter.KEY_DOSE,dose);
    medsUpdate.put(DBAdapter.KEY_FREQUENCY,frequency);
    medsUpdate.put(DBAdapter.KEY_ROUTE,route);
    medsUpdate.put(DBAdapter.KEY_REFILLDATE,refilldate);
    medsUpdate.put(DBAdapter.KEY_EXPIRATIONDATE,expirationdate);
    int i = mDb.update(DBAdapter.DATABASE_TABLE, medsUpdate, DBAdapter.ROW_ID, null);
    return i;

}

public Cursor fetchAllMeds(){
    String[] columns = new String[] {ROW_ID,KEY_DRUGNAME, KEY_DOSE, KEY_FREQUENCY, KEY_ROUTE, KEY_REFILLDATE, KEY_EXPIRATIONDATE};
    Cursor mCursor = mDb.query( DATABASE_TABLE,columns,ROW_ID, null, null, null, null, null);

    if (mCursor != null){
        mCursor.moveToFirst();
    }
    return mCursor;
}
}

Here is my fetchAllMed portion of my MedMainActivity:

    Cursor cursor = dbcon.fetchAllMeds();
    String[] from = new String[]{DBAdapter.ROW_ID, DBAdapter.KEY_DRUGNAME, DBAdapter.KEY_DOSE, DBAdapter.KEY_FREQUENCY, DBAdapter.KEY_ROUTE, DBAdapter.KEY_REFILLDATE
            , DBAdapter.KEY_EXPIRATIONDATE};
    int[] to = new int[]{R.id._id, R.id.drugName3, R.id.dose3, R.id.frequency3, R.id.route3, R.id.refillDate3, R.id.expirationDate3};

As a side note, database upgrades usually go out of their way to preserve data when data structure upgrades take place. Imagine if you lost all data whenever you decided to update an app from the Play store... — Lynn Crumbling
– Lynn Crumbling, Commented Nov 2, 2016 at 12:18
pskink, can you provide me another hint as to how to formulate the syntax to adding that _id column? — Andrew Jake Villegas
– Andrew Jake Villegas, Commented Nov 2, 2016 at 12:27
You are right Lynn. I am learning the ins and outs of updating my app's database. It seems that I am falling out on basic elements on how I should go about changing, adding columns and at the same time preserving data. — Andrew Jake Villegas
– Andrew Jake Villegas, Commented Nov 2, 2016 at 12:30
sql syntax: "select some_column as _id, ...", w3schools.com/sql/sql_alias.asp — pskink
– pskink, Commented Nov 2, 2016 at 12:30

Vitaly Zinchenko · Accepted Answer · 2016-11-02 12:08:50Z

4

Please instead of

ROW_ID + "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +

try

ROW_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +

answered Nov 2, 2016 at 12:08

Vitaly Zinchenko

4,9315 gold badges38 silver badges54 bronze badges

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

2 Comments

Opiatefuchs Over a year ago

if you make any changes on database, you have to deinstall your app and reinstall (or delete database and create new one)....

Andrew Jake Villegas Over a year ago

Opiatefuchs, do you have any references I can peruse so I will learn how to deinstall my app from my android studio?

Collectives™ on Stack Overflow

Android - sqlite.SQLiteException: no such column: _id

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related