Android Database SQLite Exception " Table has no column named" error

Question

I added 2 new columns to my SQL lite Database but I get an SQL error as one of the new columns isn't being created. I've read other threads with similar error and double checked to make sure I'm spelling it right.

The problem seems to be on my onCreate as this is where the program is crashing but I can't seem to figure it out. I added the Player_Number and Team columns however the Player_Number isnt being created yet the Team one is.

Here's my code for the DBAdapter:

public class PlayerDbAdapter {

private static final String DATABASE_CREATE = "CREATE TABLE Players (_id integer primary key autoincrement, Player_Name text not null, Player_Position text not null, Player_Number text not null, Team text not null);";
private static final String DATABASE_NAME = "Score";
private static final String DATABASE_TABLE = "Players";
private static final int DATABASE_VERSION = 3;
  public static final String KEY_BODY = "Player_Name";
  public static final String KEY_ROWID = "_id";
  public static final String KEY_TITLE = "Player_Position";
  public static final String KEY_NUMBER = "Player_Number";
  public static final String KEY_TEAM = "Team";
private static final String TAG = "PlayerDbAdapter";
  private final Context mCtx;
  private SQLiteDatabase mDb;
  private DatabaseHelper mDbHelper;

  public PlayerDbAdapter(Context paramContext)
  {
    this.mCtx = paramContext;
  }

  public void close()
  {
    this.mDbHelper.close();
  }


  public long createPlayers(String playerName, String playerPosition, String playerNumber, String team)
  {
    ContentValues localContentValues = new ContentValues();
    localContentValues.put(KEY_BODY, playerName);
    localContentValues.put(KEY_TITLE, playerPosition);
    localContentValues.put(KEY_NUMBER, playerNumber);
    localContentValues.put(KEY_TEAM, team);
    try{
        return this.mDb.insert("Players", null, localContentValues);
    } catch (Exception e) {
        Log.e("Juma", e.getMessage());
    }

    return 0;
  }

Logcat

04-17 14:41:42.890: I/Database(407): sqlite returned: error code = 1, msg = table Players has no column named Player_Number
04-17 14:41:43.011: E/Database(407): Error inserting Player_Number=23 Team=Chester 1st Team (Men) Player_Position=Goalkeeper Player_Name=a
04-17 14:41:43.011: E/Database(407): android.database.sqlite.SQLiteException: table Players has no column named Player_Number: , while compiling: INSERT INTO Players(Player_Number, Team, Player_Position, Player_Name) VALUES(?, ?, ?, ?);
04-17 14:41:43.011: E/Database(407):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
04-17 14:41:43.011: E/Database(407):    at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
04-17 14:41:43.011: E/Database(407):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
04-17 14:41:43.011: E/Database(407):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
04-17 14:41:43.011: E/Database(407):    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:41)
04-17 14:41:43.011: E/Database(407):    at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1149)
04-17 14:41:43.011: E/Database(407):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1549)
04-17 14:41:43.011: E/Database(407):    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1422)
04-17 14:41:43.011: E/Database(407):    at playerdatabase.PlayerDbAdapter.createPlayer(PlayerDbAdapter.java:46)
04-17 14:41:43.011: E/Database(407):    at your.dissertation.project.SquadActivity$1.onClick(SquadActivity.java:53)
04-17 14:41:43.011: E/Database(407):    at android.view.View.performClick(View.java:2485)
04-17 14:41:43.011: E/Database(407):    at android.view.View$PerformClick.run(View.java:9080)
04-17 14:41:43.011: E/Database(407):    at android.os.Handler.handleCallback(Handler.java:587)
04-17 14:41:43.011: E/Database(407):    at android.os.Handler.dispatchMessage(Handler.java:92)
04-17 14:41:43.011: E/Database(407):    at android.os.Looper.loop(Looper.java:123)
04-17 14:41:43.011: E/Database(407):    at android.app.ActivityThread.main(ActivityThread.java:3647)
04-17 14:41:43.011: E/Database(407):    at java.lang.reflect.Method.invokeNative(Native Method)
04-17 14:41:43.011: E/Database(407):    at java.lang.reflect.Method.invoke(Method.java:507)
04-17 14:41:43.011: E/Database(407):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
04-17 14:41:43.011: E/Database(407):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
04-17 14:41:43.011: E/Database(407):    at dalvik.system.NativeStart.main(Native Method)

your create query is faulty, you have inverted table and the table name — njzk2, Apr 17 '13 at 14:53
You have a syntax error as njzk2 stated, use: `CREATE TABLE Players ...` You should also use your static variables, like `DATABASE_TABLE`, to help prevent typos. — Sam, Apr 17 '13 at 15:05

score 2 · Accepted Answer · answered Apr 17 '13 at 15:12

Here is your query with correct syntax and using your constant...

private static final String DATABASE_TABLE = "Players";
private static final String DATABASE_CREATE = "CREATE TABLE "+DATABASE_TABLE+" (_id integer primary key autoincrement, Player_Name text not null, Player_Position text not null, Player_Number text not null, Team text not null);";

Tarun Sharma · Answer 2 · 2014-08-28T09:01:47.243

2

1. I think Always Double check your create table command  
   .execSQL("create table " + DATABASE_TABLE+ 
                "(" 
                + KEY_ID    + " integer primary key autoincrement,"
                + KEY_NAME  + " text not null," 
                + KEY_START + " text not null,"
                + Key_END   + " text not null,"
                + KEY_PAGE  + " text not null);"
            );

2. if you add or remove any column or change schema  then make sure you Upgrade your database version .
   using previous version through similar exception

edited Aug 28 '14 at 09:01

answered Aug 27 '14 at 06:56

Tarun Sharma

824
1
11
24

I got similar kind of Exception, when i was trying to add data in Database column(COLUMN NAME IS INCORRECT). Correct me if i am wrong ? – Tarun Sharma Nov 08 '14 at 03:39

score 0 · Answer 3 · edited May 21 '14 at 07:51

This problem is mainly caused by syntax. for example, previously my code was correct. here it is:

String CREATE_PRODUCTS_TABLE = "CREATE TABLE " + TABLE_PRODUCTS + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_MESSAGEADDRESS
                    + " TEXT," + COLUMN_MESSAGEBODY + " TEXT " + ")";

the above code was running correct but i added another column and it started causing the mentioned error. below is the erroneous code:

String CREATE_PRODUCTS_TABLE = "CREATE TABLE " + TABLE_PRODUCTS + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_MESSAGEADDRESS
                    + " TEXT," + COLUMN_MESSAGEBODY + " TEXT" + COLUMN_MESSAGETIME + " LONG" + ")";

as you can see, when i added the new column i forgot to include a comma after type TEXT. this means that when this code is executed, there will be syntax error as the compiler will read the last part as:

COLUMN_MESSAGEBODY TEXTCOLUMN_MESSAGETIME LONG

as opposed to:

COLUMN_MESSAGEBODY TEXT, COLUMN_MESSAGETIME LONG

solution: ensure that your syntax is correct and you heed to spaces and commas.

kindly check the below link for more info:
http://stackoverflow.com/questions/15123966/android-table-has-no-column-named-variable-name-mysql-database-error/23776738#23776738

Android Database SQLite Exception " Table has no column named" error

3 Answers3