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)

Answer 1

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  

Answer 2

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

Answer 3

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);";