Android SQLiteException no such column

Question

I get the error:

android.database.sqlite.SQLiteException: no such column: app_table (code 1): , while compiling: SELECT _id FROM apps WHERE app_table=? AND app_row=? AND app_column=?

The "select" is called in this part of the code:

public int getKeyId(int table, int row, int column) {
    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.query(DB_TABLE, new String[] { KEY_ID }, KEY_TABLE+ "=?" + " AND "
            + KEY_ROW+ "=?" + " AND "+ KEY_COLUMN+ "=?",
            new String[] { String.valueOf(table), String.valueOf(row),
            String.valueOf(column)}, null, null, null, null );
    if (cursor.getCount()==0) {
        return -1;
    }
    return cursor.getInt(0);
}

These are the strings I use to create the database:

private static final String TAG = "SQLiteOpenHelper";
private static final String DB_NAME = "apps_tables";
private static final int DB_VERSION = 1;
private static final String DB_TABLE = "apps";
/**
 * Columns
 */
private static final String KEY_ID = "_id";
private static final String KEY_TABLE = "app_table";
private static final String KEY_ROW = "app_row";
private static final String KEY_COLUMN = "app_column";
private static final String KEY_NAME = "app_name";

/**
 * Creation statement
 */
private static final String DB_CREATE = "CREATE TABLE " + DB_TABLE + " (" + KEY_ID +
        " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_TABLE + " INTEGER NOT NULL, "
        + KEY_ROW + " INTEGER NOT NULL, " + KEY_COLUMN + " INTEGER NOT NULL, " +
        KEY_NAME +" TEXT NOT NULL);";

onCreate calls:

database.execSQL(DB_CREATE);

Does anyone know why do I get this error? Thanks!

Can you pull the database out of using DDMS and verify all the columns are there? — dsum27
– dsum27, Commented Jul 22, 2014 at 12:17

Ratul Ghosh · Accepted Answer · 2014-07-22 14:53:04Z

4

Go to settings clear the app data and then run again .

This problem was occuring because when app in updated or resintsalled databse doesn't get deleted by android. In that case SQLiteOpenHelper doesn't call onCreate(SQLiteDatabase db), rather it check for database version update . you neither have updated your database version nor written onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) method properly so that column doesn't exist in previous database. When you clear teh app data, database removed, then when you lauch the app again , it again called the onCreate(SQLiteDatabase db), so your create sql run once again and you dont get that exception. The actual way of do this is increase your database version and use onUpgrade method to alter the database change.

For the devlopment purpose you can use the clear app data, but in production make sure you upgared Database Version and make use of onUpgrade method.

edited Jul 22, 2014 at 14:53

answered Jul 22, 2014 at 12:51

Ratul Ghosh

1,5009 silver badges15 bronze badges

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

1 Comment

lotdrops Over a year ago

Thank you for adding the explanation. I didn't notice this because the previous database was the same, but the creating statement was wrong.

Android is everything for me · Accepted Answer · 2014-07-22 12:53:06Z

No such column error means you got success in creation of table but not in creation of column of that table so as per understanding

create table command get executed but i think you are column not get formed so please check out your query run this query in sqlite browesr check this is working fine or not?

private static final String DB_CREATE = "CREATE TABLE " + DB_TABLE + " (" + KEY_ID +
        " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_TABLE + " INTEGER NOT NULL, "
        + KEY_ROW + " INTEGER NOT NULL, " + KEY_COLUMN + " INTEGER NOT NULL, " +
        KEY_NAME +" TEXT NOT NULL);";

your error is coming due to these lines

check into DDMS DATA>DATA folder that table is existing over there ?

try out this code too into your onCreate Method of sqliteopnhelper class

public void onCreate(SQLiteDatabase db) {

db.execSQL(" CREATE TABLE " + DB_TABLE + " (" +
        KEY_ID + "  INTEGER PRIMARY KEY AUTOINCREMENT, " +
        KEY_TABLE + " INTEGER NOT NULL, " +
        KEY_ROW + " INTEGER NOT NULL, " +
        KEY_COLUMN + " INTEGER NOT NULL, " +
        KEY_NAME + " INTEGER NOT NULL);");
}

Collectives™ on Stack Overflow

Android SQLiteException no such column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related