Adding a new attribute to sqlite database

Question

I'm quite new to Android Studio and have tried the first time to implement a database in my project. I have just added a new attribute called "noteThema" to my SQLite Database in Android studio. Now my ListView (In Activity "Begriffe") no longer displays the data from the database. Additionally, the "Spielen" Activity crashes everytime when Im startig it. However, the programme doesn't display any error messages. I would be glad if you could send me some help!

class Note

public class Note implements Serializable {
private int noteId;
private String noteTitle;
private String noteContent;
private int noteThema;

public Note()  {

}

public Note(  String noteTitle, String noteContent, int noteThema) {
    this.noteTitle= noteTitle;
    this.noteContent= noteContent;
    this.noteThema = noteThema;
}

public Note(int noteId, String noteTitle, String noteContent, int noteThema) {
    this.noteId= noteId;
    this.noteTitle= noteTitle;
    this.noteContent= noteContent;
    this.noteThema = noteThema;
}

public int getNoteId() {
    return noteId;
}

public void setNoteId(int noteId) {
    this.noteId = noteId;
}

public String getNoteTitle() {
    return noteTitle;
}

public void setNoteTitle(String noteTitle) {
    this.noteTitle = noteTitle;
}


public String getNoteContent() {
    return noteContent;
}

public void setNoteContent(String noteContent) {
    this.noteContent = noteContent;
}

public int getNoteThema(){return noteThema;}


public void setNoteThema(int noteThema) {this.noteThema = noteThema;}


@Override
public String toString()  {
    return this.noteTitle;
}

}

part of class Begriffe

public class Begriffe extends AppCompatActivity {

private ListView listView;


private static final int MY_REQUEST_CODE = 1000;

private final List<Note> noteList = new ArrayList<Note>();
private ArrayAdapter<Note> listViewAdapter;

private Button add;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_begriffe);


    // Get ListView object from xml
    listView = (ListView) findViewById(R.id.listView);
    add = (Button)findViewById(R.id.add2);

    MyDatabaseHelper db = new MyDatabaseHelper(this);
    db.createDefaultNotesIfNeed();

    List<Note> list =  db.getAllNotes();
    this.noteList.addAll(list);
    Collections.sort(noteList, new NameComparator());



    add.setOnClickListener(new View.OnClickListener() {
        public void onClick(View v) {
            Intent intent = new Intent(Begriffe.this, AddEditNoteActivity.class);

            // Start AddEditNoteActivity, (with feedback).
            Begriffe.this.startActivityForResult(intent, MY_REQUEST_CODE);
        }
    });


    this.listViewAdapter = new ArrayAdapter<Note>(this,
            android.R.layout.simple_list_item_1, android.R.id.text1, this.noteList);


    // Assign adapter to ListView
    this.listView.setAdapter(this.listViewAdapter);

    // Register the ListView for Context menu
    registerForContextMenu(this.listView);
}

class MyDarabaseHelper

public class MyDatabaseHelper extends SQLiteOpenHelper {

private static final String TAG = "SQLite";

// Database Version
private static final int DATABASE_VERSION = 3;

// Database Name
private static final String DATABASE_NAME = "Note_Manager";

// Table name: Note.
private static final String TABLE_NOTE = "Note";

private static final String COLUMN_NOTE_ID ="Note_Id";
private static final String COLUMN_NOTE_TITLE ="Note_Title";
private static final String COLUMN_NOTE_CONTENT = "Note_Content";
private static final String COLUMN_NOTE_THEMA = "Note_Thema";

public MyDatabaseHelper(Context context)  {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Create table
@Override
public void onCreate(SQLiteDatabase db) {
    Log.i(TAG, "MyDatabaseHelper.onCreate ... ");
    // Script.
    String script = "CREATE TABLE " + TABLE_NOTE + "("
            + COLUMN_NOTE_ID + " INTEGER PRIMARY KEY," + COLUMN_NOTE_TITLE + " TEXT,"
            + COLUMN_NOTE_CONTENT + " TEXT" + COLUMN_NOTE_THEMA + "INTEGER" + ")";
    // Execute Script.
    db.execSQL(script);
}


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    Log.i(TAG, "MyDatabaseHelper.onUpgrade ... ");
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTE);

    // Create tables again
    onCreate(db);
}


// If Note table has no data
// default, Insert 2 records.
public void createDefaultNotesIfNeed()  {
    int count = this.getNotesCount();
    if(count ==0 ) {

        Note note1 = new Note("Intermembranraum", "Erklären", 2);
        Note note2 = new Note("Katalysatoren", "Erklären", 2);


        this.addNote(note1);
        this.addNote(note2);


    }
}


public void addNote(Note note) {
    Log.i(TAG, "MyDatabaseHelper.addNote ... " + note.getNoteTitle());

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(COLUMN_NOTE_TITLE, note.getNoteTitle());
    values.put(COLUMN_NOTE_CONTENT, note.getNoteContent());
    values.put(COLUMN_NOTE_THEMA, note.getNoteThema());

    // Inserting Row
    db.insert(TABLE_NOTE, null, values);

    // Closing database connection
    db.close();
}


public List<Note> getAllNotes() {
    Log.i(TAG, "MyDatabaseHelper.getAllNotes ... " );

    List<Note> noteList = new ArrayList<Note>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_NOTE;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            Note note = new Note();
            note.setNoteId(Integer.parseInt(cursor.getString(0)));
            note.setNoteTitle(cursor.getString(1));
            note.setNoteContent(cursor.getString(2));
            note.setNoteThema(Integer.parseInt(cursor.getString(3)));
            // Adding note to list
            noteList.add(note);
        } while (cursor.moveToNext());
    }

    // return note list
    return noteList;
}

public int getNotesCount() {
    Log.i(TAG, "MyDatabaseHelper.getNotesCount ... " );

    String countQuery = "SELECT  * FROM " + TABLE_NOTE;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);

    int count = cursor.getCount();

    cursor.close();

    // return count
    return count;
}


public int updateNote(Note note) {
    Log.i(TAG, "MyDatabaseHelper.updateNote ... "  + note.getNoteTitle());

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(COLUMN_NOTE_TITLE, note.getNoteTitle());
    values.put(COLUMN_NOTE_CONTENT, note.getNoteContent());
    values.put(COLUMN_NOTE_THEMA, note.getNoteThema());

    // updating row
    return db.update(TABLE_NOTE, values, COLUMN_NOTE_ID + " = ?",
            new String[]{String.valueOf(note.getNoteId())});
}

public void deleteNote(Note note) {
    Log.i(TAG, "MyDatabaseHelper.updateNote ... " + note.getNoteTitle() );

    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_NOTE, COLUMN_NOTE_ID + " = ?",
            new String[] { String.valueOf(note.getNoteId()) });
    db.close();
}

}

part of class Spielen

public class Spielen extends AppCompatActivity {

private TextView TextView1;
private TextView TextView2;
private Button skip;
private Button weiter;
private TextView score2;
private int score;
private Button restart;
private ProgressBar simpleProgressBar;
private ProgressBar simpleProgressBar2;

private int COUNTDOWN_IN_MILLIS;
private CountDownTimer countDownTimer;
private long timeLeftInMillis;
private List<Integer> myList = new ArrayList<Integer>();
private int i2;
private Note begriff;
private List<Note> list;
private Boolean skip2;


@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_spielen);
    score = 0;
    skip = (Button)findViewById(R.id.button_save);
    weiter = (Button)findViewById(R.id.button_cancel);
    score2 = (TextView)findViewById(R.id.Score);
    restart = (Button)findViewById(R.id.restart);
    COUNTDOWN_IN_MILLIS = getIntent().getExtras().getInt("time");
    skip2 = getIntent().getExtras().getBoolean("skip");
    simpleProgressBar=(ProgressBar)findViewById(R.id.time3);
    simpleProgressBar2=(ProgressBar)findViewById(R.id.time4);
    simpleProgressBar.setMax(COUNTDOWN_IN_MILLIS);
    simpleProgressBar.setProgress(COUNTDOWN_IN_MILLIS);

    start();


}

public void start(){
    timeLeftInMillis = COUNTDOWN_IN_MILLIS;

    if (skip2 == true){
        skip.setEnabled(false);
        skip.setVisibility(View.GONE);
    }else{

    }

    score2.setText("Score: 0");
    score = 0;
    startCountDown();
    erstellen();
}

public void erstellen() {

    TextView1 = (TextView) findViewById(R.id.begriff);
    TextView2 = (TextView) findViewById(R.id.art);

    MyDatabaseHelper db = new MyDatabaseHelper(this);
    db.createDefaultNotesIfNeed();

    list = db.getAllNotes();
    int i = list.size();
    int i3 = i - 30;
    int i4 = myList.size();
    Random rand = new Random();
    i2 = rand.nextInt(i);

    if (i3 == i4){
        myList.clear();
        erstellen2();
    }else{
        erstellen2();
    }
}

public void erstellen2(){
    boolean check = contains(myList, i2);


    if(check == true){
        erstellen();
    }else {
        begriff = list.get(i2);
        String begriff2 = begriff.getNoteTitle();
        String begriff3 = begriff.getNoteContent();

        TextView1.setText(begriff2);
        TextView2.setText(begriff3);

        userEingabe();
    }


}

When defining your script in MyDatabaseHelper, I think you need to add a space at the beginning of your "INTEGER" String after COLUMN_NOTE_THEMA variable — Josh Sullivan, May 17 '19 at 19:09
and a column proceeding the previous column's type declaration. — Josh Sullivan, May 17 '19 at 19:10
Sorry, meant "comma" instead of "column" but looks like you figured it out. You're welcome! — Josh Sullivan, May 17 '19 at 20:04
Possible duplicate of [Unfortunately MyApp has stopped. How can I solve this?](https://stackoverflow.com/questions/23353173/unfortunately-myapp-has-stopped-how-can-i-solve-this) — Zoe, May 17 '19 at 20:15

score 0 · Accepted Answer · answered May 17 '19 at 20:13

Using

String script = "CREATE TABLE " + TABLE_NOTE + "("
            + COLUMN_NOTE_ID + " INTEGER PRIMARY KEY," + COLUMN_NOTE_TITLE + " TEXT,"
            + COLUMN_NOTE_CONTENT + " TEXT" + COLUMN_NOTE_THEMA + "INTEGER" + ")";

Has 2 issues a comma has been omitted between the 2nd an third columns and a space has been omitted between the last column name and the type.

Change to use :-

String script = "CREATE TABLE " + TABLE_NOTE + "(" + 
    COLUMN_NOTE_ID + " INTEGER PRIMARY KEY," + 
    COLUMN_NOTE_TITLE + " TEXT," + 
    COLUMN_NOTE_CONTENT + " TEXT," + //<<<<<<<<<< COMMA ADDED TO seperate column definitions
    COLUMN_NOTE_THEMA + " INTEGER" + //<<<<<<<<<< SPACE ADDED between column name and column type
    ")";

Note after making the changes delete the App's data or uninstall the App, or increase the value of DATABASE_VERSION. After doing one these rerun the App.
- Note that any existing data would be lost, if you need to keep existing data then you'd need to use ALTER commands to add the column.

score 0 · Answer 2 · answered May 19 '19 at 06:15

You can use String script = "CREATE TABLE " + TABLE_NOTE + "(" + COLUMN_NOTE_ID + " INTEGER PRIMARY KEY," + COLUMN_NOTE_TITLE + " TEXT," + COLUMN_NOTE_CONTENT + " TEXT," + COLUMN_NOTE_THEMA + " INTEGER" + ")";. This does two things, it add a comma to between the Note_content and the Note_thema and add space after Note_thema before INTEGER.

Adding a new attribute to sqlite database

2 Answers2