Column not found - Sqlite Android

Question

I searched around here too much but couldn't able to resolve the errors !!

I have marjorly 2 classes

public class DatabaseOperation extends SQLiteOpenHelper{
    private static final int DATABASE_VERSION = 2;

    public String QUERY = "CREATE TABLE " + TableData.TableInfo.TABLE_NAME +"("+ TableData.TableInfo.USER_NAME+"TEXT,"+
            TableData.TableInfo.USER_EMAIL+"TEXT,"+ TableData.TableInfo.USER_NUMBER+"TEXT );";
    public DatabaseOperation(Context context){
        super(context, TableData.TableInfo.DATABASES_NAME,null,DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db){
        db.execSQL(QUERY);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){

    }
    public void putInfo(DatabaseOperation dop,String name,String email,String pass,String no){
        SQLiteDatabase sq = dop.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(TableData.TableInfo.USER_NAME,name);
        cv.put(TableData.TableInfo.USER_EMAIL,email);
        cv.put(TableData.TableInfo.USER_NUMBER,no);
//        cv.put(TableData.TableInfo.USER_PASS,pass);
        cv.put(TableData.TableInfo.USER_STATE,"Available");
        long k = sq.insert(TableData.TableInfo.TABLE_NAME,null,cv);
    }

    public boolean seacrhEmail(DatabaseOperation db,String email){

        return true;
    }
}

and

public class TableData {

    public static abstract class TableInfo implements BaseColumns{
        public static final String USER_NAME = "user_name";
        public static final String USER_EMAIL = "user_eMail";
//        public static final String USER_PASS = "user_pass";
        public static final String USER_NUMBER = "user_phNumber";
        public static final String DATABASES_NAME = "user_info";
        public static final String TABLE_NAME = "taxi_registration";
        public static final String USER_STATE = "taxi_state";
    }
}

Error when I run insert statement

06-03 21:57:26.459    3700-3700/com.example.jawad.testapplication E/SQLiteLog﹕ (1) table taxi_registration has no column named user_name
06-03 21:57:26.475    3700-3700/com.example.jawad.testapplication E/SQLiteDatabase﹕ Error inserting user_name=bb user_eMail=bb@z.n taxi_state=Available user_phNumber=666
    android.database.sqlite.SQLiteException: table taxi_registration has no column named user_name (code 1): , while compiling: INSERT INTO taxi_registration(user_name,user_eMail,taxi_state,user_phNumber) VALUES (?,?,?,?)
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:893)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:504)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
            at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1492)
            at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1364)
            at com.example.jawad.testapplication.d.a(Unknown Source)
            at com.example.jawad.testapplication.r.onClick(Unknown Source)
            at android.view.View.performClick(View.java:4463)
            at android.view.View$PerformClick.run(View.java:18770)
            at android.os.Handler.handleCallback(Handler.java:808)
            at android.os.Handler.dispatchMessage(Handler.java:103)
            at android.os.Looper.loop(Looper.java:193)
            at android.app.ActivityThread.main(ActivityThread.java:5292)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:824)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:640)
            at dalvik.system.NativeStart.main(Native Method)

I followed this tutorial ... I don't know what I missed ...

PS: I am bit new to android.

Looks like you missed a space between `TableData.TableInfo.USER_NAME+"TEXT,"`. Try changing everything in this format. `TableData.TableInfo.USER_NAME+" TEXT," // added a space before TEXT` — siriscac, Jun 03 '15 at 17:07
@DhavalPatel sqlite is not very strict on the syntax. see https://www.sqlite.org/lang_createtable.html in column-def, type-name is optional. — njzk2, Jun 03 '15 at 17:10
@Junaid: you obviously have to delete and recreate the table for it to work. e.g., remove the app, clear the data, or implement the `onUpgrade` method. — njzk2, Jun 03 '15 at 17:11
@njzk2 I added `db.execSQL(QUERY);` statement in `onUpgrade()` method. but result is same !! — Junaid, Jun 03 '15 at 17:16
@Junaid: that's not how you upgrade a table. read more tutorials. — njzk2, Jun 03 '15 at 17:18
See [When is SQLiteOpenHelper onCreate() / onUpgrade() run?](http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run) — CL., Jun 03 '15 at 17:28

score 3 · Answer 1 · answered Jun 03 '15 at 17:14

I believe the problem is pretty simple.

public static final String USER_NAME = "user_name";

I believe you can find no column simple cause it names is different.

Following your create table, the name of your user name column is not

user_name

but

user_nameTEXT

as there is no space between the name and the column type.

public String QUERY = "CREATE TABLE " + TableData.TableInfo.TABLE_NAME +"("+ TableData.TableInfo.USER_NAME+"TEXT,"+
        TableData.TableInfo.USER_EMAIL+"TEXT,"+ TableData.TableInfo.USER_NUMBER+"TEXT );";

Try to add a space before the column type. Like this:

public String QUERY = "CREATE TABLE " + TableData.TableInfo.TABLE_NAME +"("+ TableData.TableInfo.USER_NAME+" TEXT,"+
        TableData.TableInfo.USER_EMAIL+" TEXT,"+ TableData.TableInfo.USER_NUMBER+" TEXT );";

You have to change the DATABASE_VERSION in order to update your database. — Caue Ferreira, Jun 03 '15 at 17:26

score 2 · Accepted Answer · edited Dec 10 '15 at 23:29

Add spaces to your SQLite creation script

public String QUERY = "CREATE TABLE " + TableData.TableInfo.TABLE_NAME +"("+ TableData.TableInfo.USER_NAME+" TEXT,"+
        TableData.TableInfo.USER_EMAIL+" TEXT,"+ TableData.TableInfo.USER_NUMBER+" TEXT );";

After this you should increase your DATABASE_VERSION from 2 to 3 in order to execute the upgrade method of sqlite

private static final int DATABASE_VERSION = 3;

In the upgrade database event you should drop your table and create it again.

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

Column not found - Sqlite Android

2 Answers2