Sqlite exception: column not found

I constructed the following database:

   private static final String CREATE_TABLE_TEST=
        " create table " + TABLE_TEST +
        " (test_id integer primary key autoincrement," +
        COL_GRADE +" text not null," +
        DATE+" text not null);";



private static final String CREATE_TABLE_QUESTION=
        " create table " + TABLE_ANSWER +
        " (question_id integer primary key autoincrement," +
        COL_ANSWER_1+ " text not null,"+ 
        COL_ANSWER_2+" text not null," +
        COL_ANSWER_3+" text not null," +
        COL_ANSWER_4+" text not null," +
        COL_ANSWER_5+ " text not null,"+ 
        COL_ANSWER_6+" text not null," +
        COL_RIGHT_ANSWER+" integer," +
        COL_WRONG_ANSWER+" integer, " +
        COL_TEST_ID+" INTEGER, " + 
        "FOREIGN KEY(" + COL_TEST_ID + ") REFERENCES " + TABLE_TEST+"(test_id));";

The error persists so far:

> 11-29 18:01:20.955: E/Database(853): Error inserting right_answer=6 wrong_answer=0 answer_5=fadsfas answer_6=gadsfdasasdfa

answer_3=fasdfasf test_id=1 answer_4=fasdfsa answer_1=fasdfasd answer_2=fasdfas

11-29 18:01:20.955: E/Database(853): android.database.sqlite.SQLiteException: table answer has no column

named test_id: , while compiling: INSERT INTO answer(right_answer, wrong_answer, answer_5, answer_6, answer_3, test_id, answer_4, answer_1, answer_2) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);

@Override
public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        Log.d("database", CREATE_TABLE_TEST); //I never see this statement in catlog
        Log.d("database", CREATE_TABLE_QUESTION); //nor this
        db.execSQL(CREATE_TABLE_QUESTION);
        db.execSQL(CREATE_TABLE_TEST);
    }

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS "+TABLE_TEST);
        db.execSQL("DROP TABLE IF EXISTS "+TABLE_ANSWER);
        onCreate(db);
    }

I put this in oncreate..:

 dbClass=new database(this);        
    try
    {
        Log.w("inside ",  " database opened");///This gets printed
        dbLite=dbClass.getWritableDatabase();
        Log.w("after ",  " database opened");///This get printed
    }
    catch(Exception c)
    {
        Log.v("Open database exception caught", c.getMessage());
        dbLite=dbClass.getReadableDatabase();
    }

Nothing in the onCreate gets printed..

It is as if onCreate never gets called

Answers


Your foreign key isn't made correctly

http://www.sqlite.org/foreignkeys.html

for more information

private static final String CREATE_TABLE_QUESTION=
" create table " + TABLE_ANSWER +
" (question_id integer primary key autoincrement," +
COL_ANSWER_1+ " text not null,"+ 
COL_ANSWER_2+" text not null," +
COL_ANSWER_3+" text not null," +
COL_ANSWER_4+" text not null," +
COL_ANSWER_5+ " text not null,"+ 
COL_ANSWER_6+" text not null," +
COL_RIGHT_ANSWER+" integer," +
COL_WRONG_ANSWER+" integer, " +
COL_TEST_ID+" INTEGER, " + 
"FOREIGN KEY(" + COL_TEST_ID + ") REFERENCES " + TABLE_TEST+"(test_id))";

should do the trick

furtermore your onCreate method only executes when the database gets created, not when upgraded you should override onUpgrade to perform an update (or test on a new device/emulator)

http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#onUpgrade(android.database.sqlite.SQLiteDatabase,%20int,%20int)

if you extend sqliteOpenHelper then you need to call the super-constuctor with your newVersionId to trigger onUpgrade

public YourDatabase(Context context) {
    super(context, DatabaseName, null, DATABASE_VERSION);
}

Tip: uninstall the application from your phone/emulator completely and reinstall it (then db will be removed and it will be created definately, if not the problem is somewhere else)


Need Your Help

HTML CSS: Broken Table?

html css yui

I'm using YUI to reset the browser classes and then I'm bringing in a couple custom stylesheets... I've seen this happen a few times and I don't think that I've ever narrowed down the cause:

HTML5 Application Cache not working in ie8+ so need its alternatives for ie

html5 internet-explorer-8 html5-appcache

HTML5 Application Cache not working in ie8+, so i need some of the solution which makes my resource available even in offline mode in ie8+ browsers, in all other browsers the html5 appcache is work...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.