0

So I do not have any issues inserting information into my table, but when I try to query my database, I seem to have issues retrieving it. I am new to SQLite and databases in general so maybe my understanding is different. Here are the declarations for my constants:

    public static abstract class TableInfo implements BaseColumns
{
    //User Table
    public static final String USER_NAME = "user_name";
    public static final String USER_PASS = "user_pass";
    public static final String USER_FNAME = "user_fname";
    public static final String USER_LNAME = "user_lname";
    public static final String DATABASE_NAME = "current_users";
    public static final String USER_TABLE = "user_info";

    //Message Table
    public static final String MESSAGE_ID = "msg_id";
    public static final String RECEIVER_ID = "receiver_id";
    public static final String SENDER_ID = "sender_id";
    public static final String MESSAGE = "message";
    public static final String STATUS = "status";
    public static final String MSG_TABLE = "msg_info";

This is my query string to create the table (which works)

 public String CREATE_MSG_TABLE = "CREATE TABLE " + TableData.TableInfo.MSG_TABLE +
        " ( " + TableData.TableInfo.MESSAGE_ID + " INTEGER PRIMARY KEY, "
        + TableData.TableInfo.RECEIVER_ID + " TEXT, "
        + TableData.TableInfo.SENDER_ID + " TEXT, "
        + TableData.TableInfo.MESSAGE + " TEXT, "
        + TableData.TableInfo.STATUS + " INTEGER);";
 public void onCreate(SQLiteDatabase sdb){
    sdb.execSQL(CREATE_USER_TABLE);
    Log.d("Database operations", "Table1 created");
    sdb.execSQL(CREATE_MSG_TABLE);
    Log.d("Database operations", "Table2 created");

}

This is the method I use to query the database for my information. It is a messaging application so I want the receiver_id and message:

public Cursor getInformationMsgTable(DatabaseOperations dop) {
    SQLiteDatabase SQ = dop.getWritableDatabase();

    Cursor CR = SQ.rawQuery("SELECT "
            +  TableData.TableInfo.RECEIVER_ID + ","
            +  TableData.TableInfo.SENDER_ID + ","
            +  TableData.TableInfo.MESSAGE + ","
            +  TableData.TableInfo.STATUS
            + " FROM " + TableData.TableInfo.MSG_TABLE,null);
    return CR;
}

And finally, this what I do when I return with the cursor:

 private void loadMessages()
    {
    DatabaseOperations DOP = new DatabaseOperations(CTX);
    //The cursor will iterate through the DB
    Cursor CR = DOP.getInformationMsgTable(DOP);
    if(CR.getCount() > 0)
    {
        CR.moveToFirst();

        String[] fromFieldNames = new String[]
            {
                    TableData.TableInfo.RECEIVER_ID,
                    TableData.TableInfo.MESSAGE
            };
        int[] toViewIDs = new int[]
            {
                    R.id.userNameFromDB,
                    R.id.messageInfoDB
            };
        SimpleCursorAdapter myCursorAdapter;
        myCursorAdapter = new SimpleCursorAdapter(
                CTX,
                R.layout.item_layout,
                CR,
                fromFieldNames,
                toViewIDs);
        ListView myList = (ListView) findViewById(R.id.friendsLV);
        myList.setAdapter(myCursorAdapter);
    }
}

I have inserted multiple rows into the table, I log everytime I do. But the error I get is "java.lang.IllegalArgumentException: column '_id' does not exist", which is referring to the receiver_id column. Any thoughts? I find it strange that it cuts out the part before the '_'. Also, when the table is empty, I can use the load message function and not have any issues, but when I add a row to the table THEN query the table, I get issues and errors. Thanks. EDIT: I copied the wrong function, it is corrected now.

A trace as requested: 04-07 23:14:57.850 32315-32315/com.example.andy.justalk D/AndroidRuntime﹕ Shutting down VM 04-07 23:14:57.852 32315-32315/com.example.andy.justalk E/AndroidRuntime﹕ FATAL EXCEPTION: main Process: com.example.andy.justalk, PID: 32315 java.lang.IllegalArgumentException: column '_id' does not exist at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:303) at android.widget.CursorAdapter.init(CursorAdapter.java:172) at android.widget.CursorAdapter.(CursorAdapter.java:120) at android.widget.ResourceCursorAdapter.(ResourceCursorAdapter.java:52) at android.widget.SimpleCursorAdapter.(SimpleCursorAdapter.java:78) at com.example.andy.justalk.FriendsList.loadMessages(FriendsList.java:72) at com.example.andy.justalk.FriendsList.access$000(FriendsList.java:17) at com.example.andy.justalk.FriendsList$1.onClick(FriendsList.java:35) at android.view.View.performClick(View.java:4780) at android.view.View$PerformClick.run(View.java:19866) at android.os.Handler.handleCallback(Handler.java:739) at android.os.Handler.dispatchMessage(Handler.java:95) at android.os.Looper.loop(Looper.java:135) at android.app.ActivityThread.main(ActivityThread.java:5254) at java.lang.reflect.Method.invoke(Native Method) at java.lang.reflect.Method.invoke(Method.java:372) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

3
  • Could you post your complete logcat trace about this error? Commented Apr 8, 2015 at 4:13
  • I don't have extensive experience with SQLite, but I don't see a ; at the end of your select. Commented Apr 8, 2015 at 4:28
  • Unfortunately, that did not make a difference @smskelley Commented Apr 8, 2015 at 4:33

1 Answer 1

2

You can create and define tables as you wish, but if you will have to use the table via a CursorAdapter that won't work without a tweak.

CursorAdapter: The Cursor must include a column named "_id" or this class will not work.

You must always issue a select col1 as _id ... to work.

So it's recommended to use _id in the table schema.

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

5 Comments

I thought that was the issue as well but it I have a column named msg_id and it is an Integer primary key. I included that in my query just now and I still get an error, is that what you meant?
Nope. You must have a column name with "_id" name or you can make some changes in select query. can you just post me select query?
Cursor CR = SQ.rawQuery("SELECT " + TableData.TableInfo.RECEIVER_ID + "," + TableData.TableInfo.SENDER_ID + "," + TableData.TableInfo.MESSAGE + "," + TableData.TableInfo.STATUS + " FROM " + TableData.TableInfo.MSG_TABLE,null);
Cursor CR = SQ.rawQuery("SELECT " + TableData.TableInfo.MESSAGE_ID +" AS _id, " + TableData.TableInfo.RECEIVER_ID + "," + TableData.TableInfo.SENDER_ID + "," + TableData.TableInfo.MESSAGE + "," + TableData.TableInfo.STATUS + " FROM " + TableData.TableInfo.MSG_TABLE,null);
Thank you so much, I just changed the column name of msg_id to _id. Thank you for the help

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.