How can i add more column in SQLite with android

Question

I need to use SQLite with android. I can add data and view data already. But now i need to add more column in this case i set "COL_TIME" . First i try changing column to record data from "COL_LASTNAME" to "COL_TIME" but the log cat show that the column "COL_TIME" is not exist. Despite i declare public static final String COL_TIME = "date"; in Databasestudent.Java Or i made something wrong, please provide some suggestion. here is the class to add data and the database class.

DatabaseStudent.Java

class DatabaseStudent extends SQLiteOpenHelper {
private static final String DB_NAME = "MyStudent";
private static final int DB_VERSION = 1;

public static final String TABLE_NAME = "Student";

public static final String COL_NAME = "name";
public static final String COL_LASTNAME = "last_name";
public static final String COL_SCHOOL = "school";
public static final String COL_TIME = "date";



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

public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE_NAME 
            + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " 
            + COL_NAME + " TEXT, " + COL_LASTNAME 
            + " TEXT, " + COL_SCHOOL + " TEXT);");
    db.execSQL("INSERT INTO " + TABLE_NAME + " (" 
            + COL_NAME + ", " + COL_LASTNAME  
            + ", " + COL_SCHOOL + ") VALUES ('Sleeping'"
            + ", 'For Less', 'Android School');"); 
    //db.execSQL("INSERT INTO " + TABLE_NAME + " (" 
    //        + COL_DATE + ") VALUES ('Android School');"); 
}

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

}

AddStudent.Java

public class AddStudent extends Activity {
DatabaseStudent mHelper;
SQLiteDatabase mDb;


public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.add);

    mHelper = new DatabaseStudent(this);
    mDb = mHelper.getWritableDatabase();

    final EditText editName = (EditText)findViewById(R.id.editName);
    final EditText editLastName = (EditText)findViewById(R.id.editLastName);
    final EditText editSchool = (EditText)findViewById(R.id.editSchool);

    Button buttonAdd = (Button)findViewById(R.id.buttonAdd);

    buttonAdd.setOnClickListener(new OnClickListener() {
        public void onClick(View v) {
            String name = editName.getText().toString();
            String lastname = editLastName.getText().toString();
            double school = getIntent().getDoubleExtra("Intent", 5.322);

            //Date&Time
            java.util.Date dt = new java.util.Date();
            java.text.SimpleDateFormat sdf = 
                 new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String currentTime = sdf.format(dt);

            if(name.length() != 0 && lastname.length() != 0 
                     ) {//&& school.length() != 0

                Cursor mCursor = mDb.rawQuery("SELECT * FROM " 
                        + DatabaseStudent.TABLE_NAME + " WHERE " 
                        + DatabaseStudent.COL_NAME + "='" + name + "'" 
                        + " AND " + DatabaseStudent.COL_LASTNAME + "='" 
                        + currentTime + "'" + " AND " 
                        + DatabaseStudent.COL_SCHOOL + "='" + school //add COL_SCHOOL = currentTime
                        + "'", null);

                if(mCursor.getCount() == 0) {
                    mDb.execSQL("INSERT INTO " + DatabaseStudent.TABLE_NAME 
                            + " (" + DatabaseStudent.COL_NAME 
                            + ", " + DatabaseStudent.COL_LASTNAME  
                            + ", " + DatabaseStudent.COL_SCHOOL 
                            + ") VALUES ('" + name + "', '" + currentTime //result ไม่มา
                            + "', '" + school + "');");



                    editName.setText("");
                    editLastName.setText("");
                    editSchool.setText("");


        }
    });

}

public void onStop() {
    super.onStop();
    mHelper.close();
    mDb.close();
}
}

Answer 1

If you want to modify your database once created. You are suppose to upgrade the old version of your database to new version by changing its version code.

You need to uninstall the older version of your database from the device and then only it will show the reflection of your changed database version.

So make sure you change the database version and uninstall the old application data before installing the new one from your device.

private static final int DB_VERSION = 1;

to

private static final int DB_VERSION = 2;

Answer 2

If you want to upgrade database, i.e. change the table structures you have to increment the database version so that android knows it has to upgrade it. In order to do that you need to increment this value.

 private static final int DB_VERSION = 1;

to

private static final int DB_VERSION = 2;