Why is my Android Database not storing anything?

Question

I'm working on an Android app that also involves databases and I've run across a very annoying bug. I'm working on an example taken from the web.

The example below works just fine (no logcat errors). HOWEVER if I try and change the content of the KEY Strings, it stops working.

E.g. if I change KEY_LOCATION = "title" to KEY_LOCATION = "title1", the whole DB crashes.

I've also tried increasing the DB version number with no success so far. Any help would be most appreciated.

    private static final String DATABASE_NAME="data";
private static final String DATABASE_TABLE="reminders";
private static final int DATABASE_VERSION=4;

public static final String KEY_LOCATION="title1";
public static final String KEY_BODY="body";
public static final String KEY_DATE_TIME="date_time_value";
public static final String KEY_ROW_ID="_id";

private DatabaseHelper mDbHelper;
private SQLiteDatabase db;

private static final String DATABASE_CREATE =
        "create table " + DATABASE_TABLE + " ( "+
                KEY_ROW_ID+ " integer primary key autoincrement, "+
                KEY_LOCATION+ " text not null, "+
                KEY_BODY+" text not null, "+
                KEY_DATE_TIME+ " text not null);";

private Context mContext;

public DbAdapter(Context c){
    mContext=c;
}


public DbAdapter open() throws android.database.SQLException{
    mDbHelper=new DatabaseHelper(mContext);
    db=mDbHelper.getWritableDatabase();
    return this;
}

public void close(){
    mDbHelper.close();
}

public long createReminder(String title, String body, String dateTime){
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_LOCATION, title);
    initialValues.put(KEY_BODY, body);
    initialValues.put(KEY_DATE_TIME, dateTime);

    return db.insert(DATABASE_TABLE, null, initialValues);

}

..................

These are the errors that I'm getting:

    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:146)
    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1737)
    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1610)
    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at pirelli.app.dbadapter.PirelliDbAdapter.createReminder(PirelliDbAdapter.java:55)
    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at pirelli.app.dbadapter.PirelliDbAdapter.createReminder(PirelliDbAdapter.java:55)
    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at pirelli.app.HSEActivity$1.onClick(HSEActivity.java:149)
    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at android.view.View.performClick(View.java:3110)
    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at android.view.View$PerformClick.run(View.java:11934)
    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at android.os.Handler.handleCallback(Handler.java:587)
    11-29 21:43:02.722: E/SQLiteDatabase(2150):     at android.os.Handler.dispatchMessage(Handler.java:92)

Answer 1

There is a good chance there is already data in the database from an earlier test when you try to add this new data.

Since you added that data with a different key (title instead of title1) the database table is expecting you to insert data following the title - body - date_time_value - _id sequence.

You will need to drop your old database before inserting new data with different column names, or create a different table by changing your table name to something like reminders2 as Coder said.

To drop your database, call this method:

public void dropTable() {
        db.execSQL("DROP TABLE IF EXISTS reminders");
    }

Answer 2

Try changing.

private static final String DATABASE_TABLE="reminders";

to

private static final String DATABASE_TABLE="reminders2";