can't seem to create new table in sqlite android

Question

Hello all, i have come into quite a funny hole here. i can't seem to be able to create a 2nd database table to my existing one. i basically want them to be of the same structure and the same of columns, in other words a copy of my existing table. my goal is to copy the values from my first table to the second table. The first table i created works cos i have used it already.

i have tried everything i know, unistalled and reinstalled the app to clear the database, changed the name of the tables, version..etc but i keep getting a "table not found exception". please any ideas what could be causing this and how to go about it?.. thanks

here is the creation for both of them:

/* tables variable*/
public static final String DATABASE_TABLE = "Memo";
    public static final String ROUTE = "Route";

 public static final String KEY_ID = "_id";
public static final String NAME = "Title";
public static final String DATE = "date";
public static final String TIME = "time";
public static final String VENUE = "venue";
public static final String CATEGORY = "category";
    public static final String PRIORITY = "priority";
    public static final String NOTES = "notes";

  private static final String DATABASE_CREATE = "create table Memo (_id integer primary key autoincrement," 
    + "Title text, date text, time text,"
    + "venue text, category text,"
     + "priority text, notes text);";

  private static final String DATABASE_CREATE_ROUTE = "create table Route (_id integer primary key autoincrement," 
    + "Title text, date text, time text,"
    + "venue text, category text,"
     + "priority text, notes text);";


public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
        Log.d(TAG, "created Memo table");

        db.execSQL(DATABASE_CREATE_ROUTE );
        Log.d(TAG, "created Route table");

    }
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG,"upgrading Database from version"+ oldVersion+ "to"
                +newVersion+ "which old data will be lost");
        db.execSQL("DROP TABLE IF EXITS Memo");
        db.execSQL("DROP TABLE IF EXITS Route");

        onCreate(db);
    }
}


public void copyData(long rowId){

db.execSQL("insert into ROUTE select * from DATABASE_TABLE WHERE KEY_ID = rowId");
}

Logcat Exception is:

10-22 15:47:17.326: ERROR/Database(807): Failure 1 (no such table: ROUTE) on 0x1d7f20 when preparing 'insert into ROUTE select * from DATABASE_TABLE WHERE KEY_ID = rowId'.

10-22 15:47:17.356: ERROR/AndroidRuntime(807): Uncaught handler: thread main exiting due to uncaught exception

10-22 15:47:17.526: ERROR/AndroidRuntime(807): android.database.sqlite.SQLiteException: no such table: ROUTE: insert into ROUTE select * from DATABASE_TABLE WHERE KEY_ID = rowId

10-22 15:47:17.526: ERROR/AndroidRuntime(807):     at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method)

10-22 15:47:17.526: ERROR/AndroidRuntime(807):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1496)

10-22 15:47:17.526: ERROR/AndroidRuntime(807):     at com.MemoManager.DBAdapter.copyData(DBAdapter.java:265)

Answer 1

I think you are missing a paren in your DATABASE_CREATE and DATABASE_CREATE_ROUTE

private static final String DATABASE_CREATE = "create table Memo (_id integer primary key autoincrement," 
    + "Title text, date text, time text,"
    + "venue text, category text,"
    + "priority text, notes text;";
                                ^ (HERE?)

Answer 2

Could it be referring to your DATABASE_TABLE in the insert string? I assume it needs to be "insert into ROUTE select * from " + DATABASE_TABLE + " WHERE KEY_ID = rowId"

EDIT: I think Aaron might correct too/instead.