CODEWITHSUNDEEP
androidsqliteexception
BlackFire27
BlackFire27

Reputation: 1550

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

Upvotes: 0

Views: 1028

Answers (4)

vanleeuwenbram
vanleeuwenbram

Reputation: 1339

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)

Upvotes: 1

Joe
Joe

Reputation: 2659

Can you try clearing the application data? Sometimes the database gets created before you have all the columns in it and if your onUpgrade method is not implemented correctly the column never gets added until you clear the data.

Upvotes: 0

StAlphonzo
StAlphonzo

Reputation: 766

The first thing that jumps out at me is the lack of spaces after the column name and before column type in the create table directive ...

COL_GRADE +"text not null," + DATE+"text not null);";

Are you sure the table actually was created correctly?

Upvotes: 0

Toni Toni Chopper
Toni Toni Chopper

Reputation: 1851

Blind guess: do you need a ; after INTEGER REFERENCES test ) ?

Upvotes: 0

Related Questions