SQLite database , wont let me add more than 5 columns

Question

i followed a tutorial and created a SQLite db for Android.

The db works fine (can insert) with less than 6 columns, but if i try to add a 6th column and insert data into it, nothing inserts and the app crashes.

This is a problem as i need my db to have 13 columns.

Below is the code for the db, and also code from my main activity which inserts the data into the database through user input.

public class DataBaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "Student.db";
public static final String TABLE_NAME = "student_table";
public static final String COL_1 = "ID";
public static final String COL_2 = "EVENTS";
public static final String COL_3 = "DETAILS";
public static final String COL_4 = "ALLOCATION";
public static final String COL_5 = "TEST";
public static final String COL_6 = "TESTt";





public DataBaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table " + TABLE_NAME +" (ID INTEGER PRIMARY KEY AUTOINCREMENT,EVENTS TEXT,DETAILS TEXT,ALLOCATION TEXT, TEST TEXT, TESTt TEXT)");
}

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

public boolean insertData(String event,String details,String allocation,String test, String testt) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL_2,event);
    contentValues.put(COL_3,details);
    contentValues.put(COL_4,allocation);
    contentValues.put(COL_5,test);
    contentValues.put(COL_6,testt);
    long result = db.insert(TABLE_NAME,null ,contentValues);
    if(result == -1)
        return false;
    else
        return true;
}

public Cursor getAllData() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from "+TABLE_NAME,null);
    return res;
}

public boolean updateData(String id,String event,String details,String allocation,String test,String testt) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL_1,id);
    contentValues.put(COL_2,event);
    contentValues.put(COL_3,details);
    contentValues.put(COL_4,allocation);
    contentValues.put(COL_5,test);
    contentValues.put(COL_6,testt);
    db.update(TABLE_NAME, contentValues, "ID = ?",new String[] { id });
    return true;
}

public Integer deleteData (String id) {
    SQLiteDatabase db = this.getWritableDatabase();
    return db.delete(TABLE_NAME, "ID = ?",new String[] {id});
}

and in the main

 public  void AddData() {
    add.setOnClickListener(
            new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    boolean isInserted = myDb.insertData(tv.getText().toString(),
                            details.getText().toString(),
                            tktal.getText().toString(),
                            gen.getText().toString(),
                            genamt.getText().toString()
                    );
                    if (isInserted == true)
                        Toast.makeText(MainActivity.this, "Data Inserted", Toast.LENGTH_LONG).show();
                    else
                        Toast.makeText(MainActivity.this, "Data not Inserted", Toast.LENGTH_LONG).show();
                }
            }
    );
}

Is there anything glaringly obvious i seem to be missing here? Its been very frustrating!

**tl;dr**, You must change the database version and **re-create** the database in order to add more columns. — OneCricketeer, Apr 14 '17 at 20:04

score 0 · Accepted Answer · answered Apr 14 '17 at 20:21

As apointed by @cricket_007, you need to increment your database version to execute onUpgrade and recreate the table.

To explain the funcionality:

The fourth parameter of the SQLiteOpenHelper constructor is the version, you are passing version 1:

public DataBaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

In the first time you install your application, android create the database and keep the version.

If you increase your version number, let´s say to 2 and execute your app, android will see that the current version is greater than the version of the database and will call the onUpgrade passing 1 in the oldVersion and 2 in the newVersion.

You are always recreating your table in the onUpgrade, so just changing the version number will solve.

I changed the version to 2 and ran the app and im getting the same outcome. i have since uninstalled/reinstalled it, and yet it still crashes when i try to add data. im at a complete loss as to why this is happening — 944jmc, Apr 15 '17 at 13:25

SQLite database , wont let me add more than 5 columns

1 Answers1