error: no such column

Question

I developing an android application that uses sqlite database.

1. Primary Issue: *I keep facing this error no such column but I just cant find what is the cause to this problem. Any comments will be appreciated.*

2. Secondary Issue: I wanted the application to check whether the user's info is inside the database or not. If it doesn't exist, I want the system to work on something. How to do the checking part? I tried something like below. But I'm not sure whether it works or not because of the no such column issue.

                   String values[] = helper.get_synccontentByEmailID(SettingConstant.EMAIL);
               if(!(values[0] == null)){
   
   
               }
   

--Database Class--

    public void onCreate(SQLiteDatabase db) {

    db.execSQL("CREATE TABLE synccontent (" + 
            "tableid INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "emailid_sync TEXT " +
            "contentid TEXT," +
            "contentpath TEXT," +
            "filesize TEXT," +
            "createdtime TEXT,"+
            "createdate TIMESTAMP default current_timestamp);");
}
public String[] get_synccontentByEmailID(String emailid_sync){
    SQLiteDatabase myDB = null;
    String[] returnMsg = null;
    myDB = this.getReadableDatabase();

    Cursor c = myDB.rawQuery("SELECT tableid, emailid_sync, contentid, contentpath, filesize, createdtime" +
            " from synccontent where emailid_sync='"+emailid_sync+"' ", null);
    int emailid_syncColumn = c.getColumnIndex("emailid_sync");              
    int contentidColumn = c.getColumnIndex("contentid");
    int contentpathColumn = c.getColumnIndex("contentpath");
    int filesizeColumn = c.getColumnIndex("filesize");
    int createdtimeColumn = c.getColumnIndex("createdtime");

    if (c.moveToFirst()) {
        do {
            returnMsg = new String[5]; 
            String contentid = c.getString(contentidColumn);
            String contentpath = c.getString(contentpathColumn);
            String filesize = c.getString(filesizeColumn);
            String createdtime = c.getString(createdtimeColumn);

            returnMsg[0] = emailid_sync;
            returnMsg[1] = contentid;
            returnMsg[2] = contentpath;
            returnMsg[3] = filesize;
            returnMsg[4] = createdtime;

        } while (c.moveToNext());
    }

--Main Activity--

    syncButton = (Button) findViewById(R.id.sync_btn);
    syncButton.setOnClickListener(new View.OnClickListener(){
        @Override
        public void onClick(View v) {

                    String values[] = helper.get_synccontentByEmailID(SettingConstant.EMAIL);
                if(!(values[0] == null)){


                }





E/AndroidRuntime(4543): android.database.sqlite.SQLiteException: no such column: contentid: , while compiling: SELECT tableid, emailid_sync, contentid, contentpath, filesize, createdtime from wbm_synccontent where emailid_sync='testing@yahoo.com' 

Answer 1

There's a missing comma after emailid_sync TEXT.

db.execSQL("CREATE TABLE synccontent (" + 
            "tableid INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "emailid_sync TEXT, " +
            "contentid TEXT," +
            "contentpath TEXT," +
            "filesize TEXT," +
            "createdtime TEXT,"+
            "createdate TIMESTAMP default current_timestamp);");

That should fix the primary issue, solution to secondary was given in the other answer. I think it's fine.

Answer 2

Primary Issue

Maybe you created table without contentId first.Then you added new column contentId.But table is not recreated.To recreate the table and db again uninstall the application and then install again.

Secondary Issue

You can do like this

Cursor c = myDB.rawQuery("SELECT * from synccontent where emailid_sync='"+emailid_sync+"' ", null);
if(c.getCount()==0){       //not in db
}