columns and table - SQLite

Question

The problem is ''android.database.sqlite.SQLiteException: table SCHOOLSUBJECT has no column named SUBJECT (code 1):'' What is wrong with my code? I want to put some data into database and then display it.

private static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "SQLiteDatabase.db";
public static final String TABLE_NAME = "SCHOOLSUBJECT";
public static final String COLUMN_ID = "ID";
public static final String COLUMN_FIRST_NAME = "SUBJECT";
public SQLiteHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table " + TABLE_NAME + " ( " + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_FIRST_NAME + " VARCHAR);");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL(" DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}
public boolean addData(String item)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_FIRST_NAME,item);

    Log.d(" DatabaseHelper","addData: Adding " + item + " to " + TABLE_NAME);
    long result = db.insert(TABLE_NAME,null,contentValues);
    db.close();
    if(result ==1)
        return false;
    else
        return true

}
public String getData()
{
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

    if (cursor != null)
        cursor.moveToNext();

    String dataa = cursor.getString(1);

    return dataa;
}
}

Please post the stack trace for the exception and the code that throws the exception. Also, try eliminating the semicolon at the end of the `create table` statement. — Ted Hopp, Oct 15 '17 at 13:53
You added the column or changed its name AFTER a previous test run. — Phantômaxx, Oct 15 '17 at 13:56
Try to reinstall your app or clear it's data. The database will be generated again, and maybe it will work. You could have changed your database as mentioned by @ModularSynth — gi097, Oct 15 '17 at 14:04
I reinstalled my app and now its:android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0 — Terran, Oct 15 '17 at 14:11
You have no results in your cursor. Make sure you have some data to consume. — Phantômaxx, Oct 15 '17 at 14:33

score 0 · Answer 1 · answered Oct 15 '17 at 19:26

Your first issue, is that onCreate method of the SQLiteOpenHelper subclass is only executed/called when the database itself is created. So if the structure is altered (e.g. adding a column, a table etc) and the change is made to the code in onCreate it will not be executed automatically.

When developing an App and the data does not need to be kept a quick get around is to either delete the App's data, uninstall the App or if the much copied Drop table(s) and call onCreate is coded in the onUpgrade method, to increase the version number.

You second issue is because the db.query method will NOT return a null cursor and that the cursor returned was empty.

In the case of no rows matching the criteria (empty table in your case) then the cursor count will be 0, but the cursor will be valid. Rather than if (cursor != null) use if (cursor.moveToFirst) or if (cursor.moveToNext) or if (cursor.getCount() > 0). You should also close the cursor before returning using cursor.close().

I would also suggest avoiding the use of hard coded cursor offsets i.e. you have String dataa = cursor.getString(1); where 1 is the hard coded offset/index, you will likely encounter fewer problems if you change this to get the offset/index according to the column's name. To do so you could use:-

String dataa = cursor.getString(cursor.getColumnIndex(COLUMN_FIRST_NAME));

I would also advise changing public static final String COLUMN_ID = "ID"; to public static final String COLUMN_ID = "_id";. This may subsequently simplify matters as sometimes an _id column is required e.g. if you use a CursorAdpater for a ListView (perhaps your next step will be to list all data).

Changing the much copied INTEGER PRIMARY KEY AUTOINCREMENT to just INTEGER PRIMARY KEY is also advisable. INTEGER PRIMARY KEY will give you a unique normally incrementing by 1 unique identifier, but without the overheads of AUTOINCREMENT which guarantees a higher number but at a cost.

columns and table - SQLite

1 Answers1