SQLiteException: table X has no column named Y

Question

I am trying to use the SQLDatabase as a of objects of class Todo(int id, String name, String date, String note). After creating helper by following piece of code:

package com.example.todoistodo;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class TodoSQLiteHelper extends SQLiteOpenHelper {

    public TodoSQLiteHelper(Context context) {
        // Databse: todos_db, Version: 1
        super(context, "todos_db", null, 1);
    }

    // todos
    //      _id     - key
    //      todo    - todo text
    //      date    - todo date
    //      note    - todo note

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Execute create table SQL
        db.execSQL("CREATE TABLE todos (_id INTEGER PRIMARY KEY AUTOINCREMENT , todo TEXT NOT NULL , date TEXT NOT NULL , note TEXT NOT NULL);");
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {
        db.execSQL("DROP TABLE IF EXISTS todos");
        onCreate(db);
    }

}

..and creating object to query database:

package com.example.todoistodo;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class TodoDAO {

    private SQLiteDatabase db;
    private TodoSQLiteHelper dbHelper;

    public TodoDAO(Context context) {
        dbHelper = new TodoSQLiteHelper(context);
        db = dbHelper.getWritableDatabase();
    }

    public void close() {
        db.close();
    }

    public void createTodo(String todoName, String todoDate, String todoNote) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("todo", todoName);
        contentValues.put("date", todoDate);
        contentValues.put("note", todoNote);
        db.insert("todos", null, contentValues); //lane 30
    }

    public void deleteTodo(int todoId) {
        db.delete("todos", "_id = " + todoId, null);
    }

    //get every single Todo
    public List<Todo> getTodos() {
        List<Todo> todoList = new ArrayList<Todo>();

        String[] tableColumns = new String[] {"_id","todo","date","note"};

        Cursor cursor = db.query("todos", null, null, null, null, null, null); // null as second param instead of tableColumns
        cursor.moveToFirst();

        while (!cursor.isAfterLast()) {
            Todo todo = new Todo();

            todo.setId(cursor.getInt(0));
            todo.setName(cursor.getString(1));
            todo.setDate(cursor.getString(2));
            todo.setNote(cursor.getString(3));

            todoList.add(todo);

            cursor.moveToNext();
        }

        return todoList;
    }

}

I get an SQLiteException in db.insert("todos", null, contentValues); of createTodo method. What's wrong with my code? Here is the related log:

08-06 22:43:34.902: I/SqliteDatabaseCpp(23902): sqlite returned: error code = 1, msg = table todos has no column named date, db=/data/data/com.example.todoistodo/databases/todos_db
08-06 22:43:34.922: E/SQLiteDatabase(23902): Error inserting date=Tomorrow  note= todo=Sleep out
08-06 22:43:34.922: E/SQLiteDatabase(23902): android.database.sqlite.SQLiteException: table todos has no column named date: , while compiling: INSERT INTO todos(date,note,todo) VALUES (?,?,?)
08-06 22:43:34.922: E/SQLiteDatabase(23902):    at com.example.todoistodo.TodoDAO.createTodo(TodoDAO.java:30)

Thank you!

Answer 1

This line has something wrong:

db.execSQL("CREATE TABLE todos (_id INTEGER PRIMARY KEY AUTOINCREMENT , todo TEXT NOT NULL , date TEXT NOT NULL , note TEXT NOT NULL);");

It should be without the ';'

db.execSQL("CREATE TABLE todos (_id INTEGER PRIMARY KEY AUTOINCREMENT , todo TEXT NOT NULL , date TEXT NOT NULL , note TEXT NOT NULL)");