Android sqlite database column error

Question

I'm having problem inserting into a sqlite database in my application. Whenever I try to insert the data, I get the following error: android.database.sqlite.SQLiteException: table tblstudents has no column named town. I've tried looking through the code but I cannot understand what is causing this error. My code is as follows:

package com.StudentTracker;
import java.sql.Blob;

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


public class DBAdapter {

    public static final String KEY_ROWID = "id";
    public static final String KEY_STUDENTNAME = "studentname";
    public static final String KEY_DOB = "dob";
    public static final String KEY_ADDRESS1 = "address1";
    public static final String KEY_ADDRESS2 = "address2";
    public static final String KEY_TOWN = "town";
    public static final String KEY_POSTCODE = "postcode";
    public static final String KEY_PHONE = "phone";  
    public static final String KEY_STUDENT_PIC = "studentpic";  

    private static final String TAG = "DBAdapter"; 



    private static final String DATABASE_NAME = "studentDB2";
    private static final String DATABASE_TABLE = "tblstudents";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE =
        "create table tblstudents (id integer primary key autoincrement, "
        + "studentname text not null, dob text not null, " 
        + "address1 text not null, address2 text not null, "
        + "town text not null, postcode text not null, "
        + " phone text not null);";

    private final Context context; 

    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapter(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
        int newVersion) 
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                    + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS studentDB2");
            onCreate(db);
        }
    }    

    //---opens the database---
    public DBAdapter open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---    
    public void close() 
    {
        DBHelper.close();
    }

    //---insert a title into the database---
    public long insertStudent(String studentname, String dob, String address1, String address2, String town, String postcode, String phone) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_STUDENTNAME, studentname);
        initialValues.put(KEY_DOB, dob);
        initialValues.put(KEY_ADDRESS1, address1);
        initialValues.put(KEY_ADDRESS2, address2);
        initialValues.put(KEY_TOWN, town);
        initialValues.put(KEY_POSTCODE, postcode);
        initialValues.put(KEY_PHONE, phone);
        //initialValues.put(KEY_STUDENT_PIC, studentpic);

        return db.insert(DATABASE_TABLE, null, initialValues);
    }

    //---deletes a particular title---
    public boolean deleteTitle(long rowId) 
    {
        return db.delete(DATABASE_TABLE, KEY_ROWID + 
                "=" + rowId, null) > 0;
    }

    //---retrieves all the titles---
    public Cursor getAllStudents() 
    {
        return db.query(DATABASE_TABLE, new String[] {
                KEY_ROWID, 
                KEY_STUDENTNAME,
                KEY_DOB,
                KEY_ADDRESS1,
                KEY_ADDRESS2,
                KEY_TOWN,
                KEY_POSTCODE,
                KEY_PHONE
                            }, 
                null, 
                null, 
                null, 
                null, 
                null);
    }

    //---retrieves a particular title---
    public Cursor getStudent(long rowId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                        KEY_ROWID, 
                        KEY_STUDENTNAME,
                        KEY_DOB,
                        KEY_ADDRESS1,
                        KEY_ADDRESS2,
                        KEY_TOWN,
                        KEY_POSTCODE,
                        KEY_PHONE                  
                        }, 
                        KEY_ROWID + "=" + rowId, 
                        null,
                        null, 
                        null, 
                        null, 
                        null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    //---updates a title---
    public boolean updateStudent(long rowId, String studentname, 
    String dob, String address1, String address2, String town,
    String postcode, String phone) 
    {
        ContentValues args = new ContentValues();
        args.put(KEY_STUDENTNAME, studentname);
        args.put(KEY_DOB, dob);
        args.put(KEY_ADDRESS1, address1);
        args.put(KEY_ADDRESS2, address2);
        args.put(KEY_TOWN, town);
        args.put(KEY_POSTCODE, postcode);
        args.put(KEY_PHONE, phone);
        //args.put(KEY_STUDENT_PIC, studentpic);

        return db.update(DATABASE_TABLE, args, 
                         KEY_ROWID + "=" + rowId, null) > 0;
    }




}

Answer 1

public class DBaseHelper extends SQLiteOpenHelper {
private final static int    DB_VERSION = 2;

//CHECK IT^^ Database version is a new value (incremented), for one

public TracksDB(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}


@Override
public void onCreate(SQLiteDatabase db) {
    final String DATABASE_CREATE =
        "create table tblstudents (id integer primary key autoincrement, "
        + "studentname text not null, dob text not null, " 
        + "address1 text not null, address2 text not null, "
        + "town text not null, postcode text not null, "
        + " phone text not null);";
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
        final String ALTER_TBL = 
            "ALTER TABLE " + TABLE_NAME +
            " ADD COLUMN town text not null;";
        db.execSQL(ALTER_TBL);
    }
}
}

Once you've incremented the version number, it will signal the database builder to be aware of the create tables statement in the onCreate method. Once it sees something new or any type of discrepancy, it goes to the onUpgrade method and runs the code you have for it to function as you wish it to, with the table column now included.

Answer 2

You should drop the table tblstudents in OnUpgrade(), not studentDB2 table.

db.execSQL("DROP TABLE IF EXISTS tblstudents");

Don't forget to increase the number of DATABASE_VERSION. Otherwise, OnUpgrade() won't be called.

private static final int DATABASE_VERSION = 2;

Answer 3

If you've recently added that column, remember to increase the database version as well so that the upgrade routine gets executed.