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)

Guillaume · Accepted Answer · 2011-12-07 07:45:58Z

3

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);";

answered Dec 7, 2011 at 7:45

Guillaume

22.9k15 gold badges59 silver badges70 bronze badges

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

3 Comments

Kev Over a year ago

Thank you. It works fine now. But when I tap on the list view for details the app crashes

Guillaume Over a year ago

Apparently, you tried to call fetchNote before calling open() ;) (your mDB is null)

Hiral Vadodaria Over a year ago

please join this room chat.stackoverflow.com/rooms/5649/… will continue our discussion there.

Hiral Vadodaria · Accepted Answer · 2011-12-07 08:10:11Z

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.

I just copied the database from the Notepad tutorial, how should I add the message column?
What do you exactly want to fetch from table? message means body? or it is other than that?
Yeah, first when the user tapped the send button i want the message to be saved in the database and may be able to view it via list view on the other tab.

Collectives™ on Stack Overflow

Android: SQLite database

2 Answers 2

3 Comments

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Related