Android: SQLite database

Question

I have this error:

12-07 15:27:11.567: E/AndroidRuntime(15797): Caused by: android.database.sqlite.SQLiteException: no such column: recipient: , while compiling: SELECT _id, recipient, body FROM notes

I don't know what's the problem. I'm trying to save a data to the database by tapping the send button the data will be saved automatically to the database and will be shown via list view under the tab activity but then there's this error. Here's my code for the database:

public class MessagesDBAdapter {

    public static final String KEY_RECIPIENT = "recipient";
    public static final String KEY_MESSAGE = "message";
    public static final String KEY_ROWID = "_id";

    private static final String TAG = "MessagesDBAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;


     //Database creation sql statement

    private static final String DATABASE_CREATE =
        "create table notes (_id integer primary key autoincrement, "
        + "title text not null, body text not null);";

    private static final String DATABASE_NAME = "data";
    private static final String DATABASE_TABLE = "notes";
    private static final int DATABASE_VERSION = 2;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS notes");
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public MessagesDBAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public MessagesDBAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }

    public long createNote(String phoneNo, String message) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_RECIPIENT, phoneNo);
        initialValues.put(KEY_MESSAGE, message);

        open();

        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

    public boolean deleteNote(long rowId) {

        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    public Cursor fetchAllNotes() {

        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_RECIPIENT,
                KEY_MESSAGE}, null, null, null, null, null);
    }

    public Cursor fetchNote(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                    KEY_RECIPIENT, KEY_MESSAGE}, KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    public boolean updateNote(long rowId, String phoneNo, String message) {
        ContentValues args = new ContentValues();
        args.put(KEY_RECIPIENT, phoneNo);
        args.put(KEY_MESSAGE, message);

        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}

Can anyone help me?

Here's the log cat error:

12-07 16:10:23.797: E/AndroidRuntime(16596): FATAL EXCEPTION: main
12-07 16:10:23.797: E/AndroidRuntime(16596): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.KAHTextApp/com.example.KAHTextApp.KAHTextApp}: java.lang.NullPointerException
12-07 16:10:23.797: E/AndroidRuntime(16596):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2663)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at android.app.ActivityThread.access$2300(ActivityThread.java:125)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at android.os.Handler.dispatchMessage(Handler.java:99)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at android.os.Looper.loop(Looper.java:123)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at android.app.ActivityThread.main(ActivityThread.java:4627)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at java.lang.reflect.Method.invokeNative(Native Method)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at java.lang.reflect.Method.invoke(Method.java:521)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at dalvik.system.NativeStart.main(Native Method)
12-07 16:10:23.797: E/AndroidRuntime(16596): Caused by: java.lang.NullPointerException
12-07 16:10:23.797: E/AndroidRuntime(16596):    at com.example.KAHTextApp.MessagesDBAdapter.fetchNote(MessagesDBAdapter.java:98)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at com.example.KAHTextApp.KAHTextApp.populateFields(KAHTextApp.java:190)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at com.example.KAHTextApp.KAHTextApp.onCreate(KAHTextApp.java:68)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
12-07 16:10:23.797: E/AndroidRuntime(16596):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)

Answer 1

Along with the problem Guillaume described,also your column name in your create query is "body" and at the time of fetching notes,you are using "message" as column name...So it might be causing problem,if i didn't get the things wrongly.

EDIT :

I hope,its not wrong what i assume,that you want to fetch body from the table "notes" as a message. if so,then what you are coding is:

...
public static final String KEY_MESSAGE = "message";

...
private static final String DATABASE_CREATE =
        "create table notes (_id integer primary key autoincrement, "
        + "title text not null, body text not null);";
...
public Cursor fetchAllNotes() {

        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_RECIPIENT,
                KEY_MESSAGE}, null, null, null, null, null);
}

Here,you have defined KEY_MESSAGE as "message" and when you create the table,you have no column with name "message".But you are trying to fetch it in your select query.

So you need to make KEY_MESSAGE as "body" or you will have to add a column with name "message" in your table.

Answer 2

Well, your SELECT query tries to get the recipient and message columns, but your CREATE query only adds a title and body columns. That cannot work.

Change your code to have:

private static final String DATABASE_CREATE =
    "create table notes (" + KEY_ROWID + " integer primary key autoincrement, "
    + KEY_RECIPIENT + " text not null, " + KEY_MESSAGE + " text not null);";