Android SQLite query error "no such column"

Question

all. I am working on one assignment with SQLite. I really appreciate to help me.

The situation is very simple SQL query but does not work

public class TasksDBHelper extends SQLiteOpenHelper {

    private static final String LOGTAG = "Task Manager";

    public static final String DB_COL_TITLE = "title";
    public static String DB_COL_DESCRIPTION = "description";
    public static String DB_COL_DUEDATE = "duedate";
    public static String DB_COL_CATEGORY = "category";
    public static String DB_COL_PRIORITY = "priority";
    public static String DB_COL_BODY = "body";

    public static String DB_COL_ID = BaseColumns._ID; //same as setting to "_id"
    public static final String[] DB_ALL_COLUMNS = { DB_COL_ID, DB_COL_TITLE, DB_COL_DESCRIPTION, DB_COL_DUEDATE,
                                                    DB_COL_CATEGORY, DB_COL_PRIORITY};

    private static final String DB_NAME = "my_tasks.db";
    public  static final String DB_TABLE = "tasks";
    private static final int DB_VERSION = 1;

    private static final String DB_TABLE_CREATE =
            "CREATE TABLE tasks (" +
                    DB_COL_ID + " integer primary key autoincrement," +
                    DB_COL_TITLE + " text not null," +
                    DB_COL_DESCRIPTION + " text not null," +
                    DB_COL_DUEDATE + " text not null," +
                    DB_COL_CATEGORY + " text not null," +
                    DB_COL_PRIORITY + " text not null);";

    private static TasksDBHelper INSTANCE;
    public static TasksDBHelper getInstance(Context ctx){
        if (INSTANCE == null)
            INSTANCE = new TasksDBHelper(ctx.getApplicationContext());

        return INSTANCE;
    }

    private TasksDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d(LOGTAG, "onCreate() of the SQLite Demo");
        db.execSQL(DB_TABLE_CREATE);
        Log.d(LOGTAG, "Notes DB table created");
        //Populate with some sample notes
        ContentValues cv = new ContentValues();
        for (int i = 0; i < 10; i++) {
            cv.put(DB_COL_TITLE, String.format("Title : %d", i));
            cv.put(DB_COL_DESCRIPTION, String.format("Body : %d", i));
            cv.put(DB_COL_DUEDATE, String.format("Body : %d", i));
            cv.put(DB_COL_CATEGORY, String.format("Body : %d", i));
            cv.put(DB_COL_PRIORITY, String.format("Body : %d", i));
            db.insert(DB_TABLE, null, cv);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
        onCreate(db);
    }

    /////CRUD Methods

    public Task createTask(Task task) {
        Log.d(LOGTAG, "Calling createNote()");

        SQLiteDatabase db = getWritableDatabase();
        ContentValues cv = new ContentValues();

        cv.put(TasksDBHelper.DB_COL_TITLE, task.getTitle());
        cv.put(TasksDBHelper.DB_COL_DESCRIPTION, task.getDescription());
        cv.put(TasksDBHelper.DB_COL_DUEDATE, task.getDuedate());
        cv.put(TasksDBHelper.DB_COL_CATEGORY, task.getCategory());
        cv.put(TasksDBHelper.DB_COL_PRIORITY, task.getPriority());

        long id =  db.insert(TasksDBHelper.DB_TABLE, null, cv);
        task.setId(id);
        Log.d(LOGTAG, "Task created : " + id);
        return task;
    }

    public int updateTask(Task task) {
        Log.d(LOGTAG, "Calling updateNote()");

        SQLiteDatabase notesDB = getWritableDatabase();
        ContentValues cv = new ContentValues();

        cv.put(TasksDBHelper.DB_COL_TITLE, task.getTitle());
        cv.put(TasksDBHelper.DB_COL_DESCRIPTION, task.getDescription());
        cv.put(TasksDBHelper.DB_COL_DUEDATE, task.getDuedate());
        cv.put(TasksDBHelper.DB_COL_CATEGORY, task.getCategory());
        cv.put(TasksDBHelper.DB_COL_PRIORITY, task.getPriority());
        return notesDB.update(TasksDBHelper.DB_TABLE, cv, TasksDBHelper.DB_COL_ID + "=" + task.getId(), null);
    }


    public ArrayList<Task> getTasks() {
        Log.d(LOGTAG, "Calling getTasks()");
        SQLiteDatabase notesDB = getReadableDatabase();
        Cursor c = null;
        ArrayList<Task> tasks = new ArrayList<Task>();



        try {
            //c = notesDB.query(TasksDBHelper.DB_TABLE, TasksDBHelper.DB_ALL_COLUMNS, null, null, null, null, null);
            String query = String.format("SELECT _id, %s, %s, %s, %s, %s FROM %s", 
            TasksDBHelper.DB_COL_TITLE, TasksDBHelper.DB_COL_DESCRIPTION, TasksDBHelper.DB_COL_DUEDATE,
                     TasksDBHelper.DB_COL_CATEGORY, TasksDBHelper.DB_COL_PRIORITY, TasksDBHelper.DB_TABLE);

            c = notesDB.rawQuery(query, null);

            if ((c != null) && c.getCount() > 0 ) {
                while(c.moveToNext())
                    tasks.add(getNoteFromCursor(c));
            }
        }
        finally
        {
            if (c != null)
                c.close();
        }
        return tasks;
    }


    public Task getTask(long rowId) {
        Cursor c = null;
        Task task = null;
        Log.d(LOGTAG, "Calling getNote()");
        SQLiteDatabase tasksDB = getReadableDatabase();

        try {
            c = tasksDB.query(TasksDBHelper.DB_TABLE, new String[]
                            { TasksDBHelper.DB_COL_ID, TasksDBHelper.DB_COL_TITLE,
                                    TasksDBHelper.DB_COL_DESCRIPTION, TasksDBHelper.DB_COL_DUEDATE,
                                    TasksDBHelper.DB_COL_CATEGORY, TasksDBHelper.DB_COL_PRIORITY},
                    TasksDBHelper.DB_COL_ID + "=" + rowId, null, null, null, null);
            if (c != null)
                c.moveToFirst();
            task = getNoteFromCursor(c);
            return task;

        } catch (Exception e) {
            Log.e(LOGTAG, "Error on getNote()", e);
        }
        finally {
            if (c != null)
                c.close();
        }
        return task;

    }

    public boolean delete(long rowId) {
        SQLiteDatabase notesDB = getWritableDatabase();
        return notesDB.delete(TasksDBHelper.DB_TABLE, TasksDBHelper.DB_COL_ID + "=" + rowId, null) > 0;
    }


    private static Task getTaskFromCursor(Cursor c){
        if ((c == null) || (c.getCount() == 0))
            return null;
        else {
            Task task = new Task();
            task.setId(c.getLong(c.getColumnIndex(TasksDBHelper.DB_COL_ID)));
            task.setTitle(c.getString(c.getColumnIndex(TasksDBHelper.DB_COL_TITLE)));
            task.setdescription(c.getString(c.getColumnIndex(TasksDBHelper.DB_COL_DESCRIPTION)));
            task.setdescription(c.getString(c.getColumnIndex(TasksDBHelper.DB_COL_DUEDATE)));
            task.setdescription(c.getString(c.getColumnIndex(TasksDBHelper.DB_COL_CATEGORY)));
            task.setdescription(c.getString(c.getColumnIndex(TasksDBHelper.DB_COL_PRIORITY)));
            return task;
        }

    }
}

07-06 00:35:23.621 5558-5558/? E/AndroidRuntime: FATAL EXCEPTION: main Process: com.comp3617.assignment3.chiseongoh, PID: 5558 java.lang.RuntimeException: Unable to start activity ComponentInfo{com.comp3617.assignment3.chiseongoh/com.comp3617.assignment3.chiseongoh.DisplayTasksActivity}: android.database.sqlite.SQLiteException: no such column: title (code 1): , while compiling: SELECT _id, title, description, duedate, category, priority FROM tasks at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2416) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476) at android.app.ActivityThread.-wrap11(ActivityThread.java) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344) at android.os.Handler.dispatchMessage(Handler.java:102) at android.os.Looper.loop(Looper.java:148) at android.app.ActivityThread.main(ActivityThread.java:5417) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616) Caused by: android.database.sqlite.SQLiteException: no such column: title (code 1): , while compiling: SELECT _id, title, description, duedate, category, priority FROM tasks at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316) at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255) at com.comp3617.assignment3.chiseongoh.db.TasksDBHelper.getTasks(TasksDBHelper.java:128) at com.comp3617.assignment3.chiseongoh.DisplayTasksActivity.bindListViewToNotes(DisplayTasksActivity.java:76) at com.comp3617.assignment3.chiseongoh.DisplayTasksActivity.onCreate(DisplayTasksActivity.java:71) at android.app.Activity.performCreate(Activity.java:6237) at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1107) at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2369) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476) at android.app.ActivityThread.-wrap11(ActivityThread.java) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344) at android.os.Handler.dispatchMessage(Handler.java:102) at android.os.Looper.loop(Looper.java:148) at android.app.ActivityThread.main(ActivityThread.java:5417) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)

Errors at here

public Task getTask(long rowId) {
        Cursor c = null;
        Task task = null;
        Log.d(LOGTAG, "Calling getNote()");
        SQLiteDatabase tasksDB = getReadableDatabase();

        try {
            c = tasksDB.query(TasksDBHelper.DB_TABLE, new String[]
                            { TasksDBHelper.DB_COL_ID, TasksDBHelper.DB_COL_TITLE,
                                    TasksDBHelper.DB_COL_DESCRIPTION, TasksDBHelper.DB_COL_DUEDATE,
                                    TasksDBHelper.DB_COL_CATEGORY, TasksDBHelper.DB_COL_PRIORITY},
                    TasksDBHelper.DB_COL_ID + "=" + rowId, null, null, null, null);
            if (c != null)
                c.moveToFirst();
            task = getNoteFromCursor(c);
            return task;

        } catch (Exception e) {
            Log.e(LOGTAG, "Error on getNote()", e);
        }
        finally {
            if (c != null)
                c.close();
        }
        return task;

    }

Thank you!

score 1 · Accepted Answer · answered Jul 07 '16 at 03:11

1

You can change DB_VERSION or uninstall the old app on your device then reinstall. I thinks old database with no title column existed in that device.

answered Jul 07 '16 at 03:11

mdtuyen

4,470
5
28
50

Thank you! you are right! I used an old example before. Thank you! – Chi Seong Oh Jul 07 '16 at 03:30

score 0 · Answer 2 · answered Jul 07 '16 at 03:05

0

Maybe you can run your code in emulator and then drag the db file out。Then open the file in "SQLite Expert" to see whether or not that you had created the table contains the column "title".

answered Jul 07 '16 at 03:05

mrz

71
3

Android SQLite query error "no such column"

2 Answers2