Unable to add column dynamically into the sqlite table

Question

The logcat says following error:

08-12 21:31:12.373: I/Database(325): sqlite returned: error code = 1, msg = near "10010325": syntax error
08-12 21:31:12.373: E/Database(325): Failure 1 (near "10010325": syntax error) on 0x2396b8 when preparing 'ALTER TABLE Student_attendance ADD COLUMN 10010325 INTEGER NOT NULL DEFAULT 0;'.

Here is the StudentLoginPage.java when a student makes a new account addcolumn function is called which should add a new column to the ATTENDANCE_TABLE

case R.id.buttonRegister:

    mStudentname = (EditText) findViewById(R.id.editStudentname);
    mSPassword = (EditText) findViewById(R.id.editSPassword);
    mSRollno = (EditText) findViewById(R.id.editRoll);
    mSEmail = (EditText) findViewById(R.id.editEmail);

    String uname = mStudentname.getText().toString();
    String pass = mSPassword.getText().toString();
    String roll = mSRollno.getText().toString();
    String email = mSEmail.getText().toString();
    boolean invalid = false;

        if (uname.equals("")) {
            invalid = true;
            Toast.makeText(getApplicationContext(), "Username Missing",
                    Toast.LENGTH_SHORT).show();
        } else if (pass.equals("")) {
            invalid = true;
            Toast.makeText(getApplicationContext(), "Password Missing",
                    Toast.LENGTH_SHORT).show();
        } else if (roll.equals("")) {
            invalid = true;
            Toast.makeText(getApplicationContext(), "Roll no Missing",
                    Toast.LENGTH_SHORT).show();
        } else if (email.equals("")) {
            invalid = true;
            Toast.makeText(getApplicationContext(), "Email ID Missing",
                    Toast.LENGTH_SHORT).show();
        }

        if (invalid == false) {
            addEntry(uname, pass, roll, email);
            addColumn(roll);
            Intent i_register = new Intent(NewStudentPage.this,
                    MainActivity.class);
            startActivity(i_register);
            finish();
        }

        break;
    }
}

private void addColumn(String roll) {
    // TODO Auto-generated method stub
    SQLiteDatabase db = myDb.getWritableDatabase();
    try {
        db.execSQL("ALTER TABLE "+ DbHelper.ATTENDANCE_TABLE +" ADD COLUMN "+ roll +" INTEGER NOT NULL DEFAULT 0;");
    } catch (Exception e) {
        // TODO: handle exception
    }

}

Here's the DbHelper.java Here I have created three tables STUDENT_TABLE,ATTENDANCE_TABLE and TEACHER_TABLE

public class DbHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "subh.db";
    private static final int DATABASE_VERSION = 1;
    public static final String ATTENDANCE_TABLE = "Student_attendance";
    public static final String TEACHER_TABLE = "Teacher_login";
    public static final String STUDENT_TABLE = "Student_login";
    public static final String T_ROW_ID = "teacher_id";

    private static final String ATTENDANCE_TABLE_CREATE = "CREATE TABLE "
            + ATTENDANCE_TABLE + "(" + "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
            + "teacher_id INTEGER , " + "time TEXT NOT NULL); ";

    private static final String TEACHER_TABLE_CREATE = "CREATE TABLE "
            + TEACHER_TABLE
            + "( teacher_id INTEGER PRIMARY KEY AUTOINCREMENT , "
            + "t_name TEXT NOT NULL , t_pass TEXT NOT NULL , t_email TEXT NOT NULL);";

    private static final String STUDENT_TABLE_CREATE = "CREATE TABLE "
            + STUDENT_TABLE + "( s_id INTEGER PRIMARY KEY AUTOINCREMENT , "
            + "s_name TEXT NOT NULL , s_pass TEXT NOT NULL , "
            + "s_roll_no TEXT NOT NULL , " + "s_email TEXT NOT NULL);";

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        System.out.println("In constructor");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        try {
            db.execSQL(TEACHER_TABLE_CREATE);
            db.execSQL(ATTENDANCE_TABLE_CREATE);
            db.execSQL(STUDENT_TABLE_CREATE);

            System.out.println("In onCreate");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Log the version upgrade.
        Log.w("TaskDBAdapter", "Upgrading from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");


            db.execSQL("DROP TABLE IF EXISTS " + "TEMPLATE");

        // Create a new one.
        onCreate(db);

    }

}

Answer 1

ADD COLUMN 10010325 ...

Column name must be valid identifier - alphanumeric starting with latin letter, but you have a number 10010325 for column name - this won't work!

UPDATE:

Adding columns dynamically sounds like recipe for disaster. You will be better off by using proper database schema, like this:

CREATE TABLE classes (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    class_name VARCHAR(32),
    teacher_id INTEGER,
    time TEXT NOT NULL
);

and then create attendance table like this:

CREATE TABLE student_attendance (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    class_id INTEGER,
    student_id INTEGER
);

Table student_attendance is now supposed to have extra row for every student attending given class. This way, you do not have to alter your schema on a fly.