"Table X has no column named Y" error inserting data into a SQLite database

Question

I got an error on inserting data on my database.

01-28 20:59:06.277: I/Database(553): sqlite returned: error code = 1, msg = table tableKo has no column named phone
01-28 20:59:06.309: E/Database(553): Error inserting phone= email= address= name=
01-28 20:59:06.309: E/Database(553): android.database.sqlite.SQLiteException: table tableKo has no column named phone: , while compiling: INSERT INTO tableKo(phone, email, address, name) VALUES(?, ?, ?, ?);
01-28 20:59:06.309: E/Database(553):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
01-28 20:59:06.309: E/Database(553):    at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
01-28 20:59:06.309: E/Database(553):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
01-28 20:59:06.309: E/Database(553):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
01-28 20:59:06.309: E/Database(553):    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:36)
01-28 20:59:06.309: E/Database(553):    at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1145)
01-28 20:59:06.309: E/Database(553):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1536)
01-28 20:59:06.309: E/Database(553):    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1410)
01-28 20:59:06.309: E/Database(553):    at com.example.databasetest.DBHandler.insertData(DBHandler.java:76)
01-28 20:59:06.309: E/Database(553):    at com.example.databasetest.MainActivity.saveButtonHandler(MainActivity.java:39)
01-28 20:59:06.309: E/Database(553):    at java.lang.reflect.Method.invokeNative(Native Method)
01-28 20:59:06.309: E/Database(553):    at java.lang.reflect.Method.invoke(Method.java:521)
01-28 20:59:06.309: E/Database(553):    at android.view.View$1.onClick(View.java:2067)
01-28 20:59:06.309: E/Database(553):    at android.view.View.performClick(View.java:2408)
01-28 20:59:06.309: E/Database(553):    at android.view.View$PerformClick.run(View.java:8816)
01-28 20:59:06.309: E/Database(553):    at android.os.Handler.handleCallback(Handler.java:587)
01-28 20:59:06.309: E/Database(553):    at android.os.Handler.dispatchMessage(Handler.java:92)
01-28 20:59:06.309: E/Database(553):    at android.os.Looper.loop(Looper.java:123)
01-28 20:59:06.309: E/Database(553):    at android.app.ActivityThread.main(ActivityThread.java:4627)
01-28 20:59:06.309: E/Database(553):    at java.lang.reflect.Method.invokeNative(Native Method)
01-28 20:59:06.309: E/Database(553):    at java.lang.reflect.Method.invoke(Method.java:521)
01-28 20:59:06.309: E/Database(553):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
01-28 20:59:06.309: E/Database(553):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
01-28 20:59:06.309: E/Database(553):    at dalvik.system.NativeStart.main(Native Method)

I use a separate class that extends SQLiteOpenHelper to handle my database. I already search on the internet to know how does my table don't have the column names here is my code: My main class

package com.example.databasetest;

import android.app.Activity;
import android.os.Bundle;
import android.view.Menu;
import android.view.View;
import android.widget.EditText;

public class MainActivity extends Activity {

    EditText nameET;
    EditText addressET;
    EditText phoneET;
    EditText emailET;

    String name;
    String address;
    String phone;
    String email;

    DBHandler handler;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        nameET = (EditText)findViewById(R.id.nameET);
        addressET = (EditText)findViewById(R.id.addressET);
        phoneET = (EditText)findViewById(R.id.phoneET);
        emailET = (EditText)findViewById(R.id.emailET);

        name = nameET.getText().toString();
        address = addressET.getText().toString();
        phone = phoneET.getText().toString();
        email = emailET.getText().toString();
        handler = new DBHandler(this);
    }
    public void saveButtonHandler(View v) { 
        handler.insertData(name, address, phone, email);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.activity_main, menu);
        return true;
    }
}

and this is my database handler class

package com.example.databasetest;

import android.content.ContentValues;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHandler {

    public static final String TABLE_NAME = "tableKo";
    public static final String DATABASE_NAME = "databaseKo";
    private static final int DATABASE_VERSION = 1;
    private static final String TAG = "DBHandler";

    public static final String COL_ID = "_id";
    public static final String COL_NAME = "name";
    public static final String COL_ADDRESS = "address";
    public static final String COL_PHONE = "phone";
    public static final String COL_EMAIL = "email";

    private final Context context;
    private SQLiteDatabase db;
    private MySQLiteOpenHelper DBHelper;

    private static final String CREATE_DATABASE ="create table "
            + TABLE_NAME + "(" + COL_ID
            + " integer primary key, " + COL_NAME
            + " text not null, " + COL_ADDRESS + "text not null,"
            + COL_PHONE + "text not null," + COL_EMAIL + "text not null);";
    public DBHandler(Context ctx) {
        this.context = ctx;
        DBHelper = new MySQLiteOpenHelper(context);
    }
    private static class MySQLiteOpenHelper extends SQLiteOpenHelper{
        public MySQLiteOpenHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            try {
                db.execSQL(CREATE_DATABASE);
              } catch (SQLException e) {
                e.printStackTrace();
              } 
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            Log.w(TAG, oldVersion + " to " + newVersion
                    + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
        }

    }
    public DBHandler open() throws SQLException {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        DBHelper.close();
    }
    public void insertData (String name, String address, String phone, String email) {
        open();
        ContentValues values = new ContentValues();
        values.put(COL_NAME, name);
        values.put(COL_ADDRESS, address);
        values.put(COL_PHONE, phone);
        values.put(COL_EMAIL, email);

        db.insert(TABLE_NAME, null, values);
        //db.execSQL("Insert into " +TABLE_NAME+ " VALUES('"+COL_ID+"','"+name+"','"+address+"','"+phone+"','"+email+"');");
        db.close();
    }
}

What is wrong with my code? Thanks in advance for your help

score 4 · Accepted Answer · answered Jan 28 '13 at 13:12

4

you missed a space :

instead of

COL_PHONE + "text not null,"

use

COL_PHONE + " text not null,"

answered Jan 28 '13 at 13:12

Arash GM

10,316
6
58
76

Thanks for the correction, But i still have the same error after changing what you suggested – Cadis Etrama Di Raizel Jan 28 '13 at 13:21
you have to space for all of your table name in your create table like COL_EMAIL and COL_ADDRESS,after spacing your error in logcat didn't change? – Arash GM Jan 28 '13 at 13:23
1

You have to ensure that the table is actually recreated, i.e., delete your app's data, or uninstall it, or increase the DB version. – CL. Jan 28 '13 at 13:24
@Arash i already change like what you said but still i got same error. – Cadis Etrama Di Raizel Jan 28 '13 at 13:29
@CL I'm a bit new to android how do i uninstall the app data? does my upgrade method still not enough to do that? – Cadis Etrama Di Raizel Jan 28 '13 at 13:30
your `onUpgrade()` is enough. if you increase you `DB_VERSION` and rerun then the function will be called. – David M Jan 28 '13 at 13:32
Your `onUpgrade` method will be called **only** when the DB version is increased. – CL. Jan 28 '13 at 13:37
To delete an app's data, go to settings/App Management. – CL. Jan 28 '13 at 13:38
Thanks for everything guys got it. And i learn a lot from you guys. Thanks again – Cadis Etrama Di Raizel Jan 28 '13 at 13:50

score 0 · Answer 2 · answered Dec 28 '14 at 21:43

This problem is usually solved by deleting the app on your device and reinstalling it.First check the column names in your code against the column names in the database,if they don't match it means you still have the old copy of the database on your device which you haven't changed after making changes.

"Table X has no column named Y" error inserting data into a SQLite database

2 Answers2