Why doesn't SQLite recognize my columns?

Question

I keep getting the following logcat error when I try to insert into my database:

07-22 23:52:28.039: E/SQLiteLog(12345): (1) table feelings_needs_table has no column named needs
07-22 23:52:28.190: E/SQLiteDatabase(12345): Error inserting needs={need=peace} feelings={feeling=tired}
07-22 23:52:28.190: E/SQLiteDatabase(12345): android.database.sqlite.SQLiteException: table feelings_needs_table has no column named needs (code 1): , while compiling: INSERT INTO feelings_needs_table(needs,feelings) VALUES (?,?)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at com.example.feeling.DatabaseConnectToActivities.insertEmotions(DatabaseConnectToActivities.java:42)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at com.example.feeling.Needs$1.onItemClick(Needs.java:79)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.widget.AdapterView.performItemClick(AdapterView.java:298)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.widget.AbsListView.performItemClick(AbsListView.java:1100)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.widget.AbsListView$PerformClick.run(AbsListView.java:2749)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.widget.AbsListView$1.run(AbsListView.java:3423)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.os.Handler.handleCallback(Handler.java:725)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.os.Handler.dispatchMessage(Handler.java:92)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.os.Looper.loop(Looper.java:137)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at android.app.ActivityThread.main(ActivityThread.java:5041)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at java.lang.reflect.Method.invokeNative(Native Method)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at java.lang.reflect.Method.invoke(Method.java:511)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
07-22 23:52:28.190: E/SQLiteDatabase(12345):    at dalvik.system.NativeStart.main(Native Method)
07-22 23:52:28.333: I/Choreographer(12345): Skipped 464 frames!  The application may be doing too much work on its main thread.

Here is my database helper:

public class DatabaseHelp extends SQLiteOpenHelper {



    //set up database variables
    public static final int DATABASE_VERSION = 1;

    public static final String DATABASE_NAME = "feelings_needs";
    public static final String DATABASE_TABLE = "feelings_needs_table";
    public static final String ROW_ID = "_id";
    public static final String FEELING_ID = "feelings";
    public static final String NEED_ID = "needs";



    //creates the helper
    public DatabaseHelp(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }



    //set-up the table
    @Override
    public void onCreate(SQLiteDatabase database) {
        // TODO Auto-generated method stub
        String CREATE_TABLES = "CREATE TABLE  DATABASE_TABLE(ROW_ID  integer primary key autoincrement, "
                + "FEELING_ID text,"
                + "NEED_ID  text);";

        database.execSQL(CREATE_TABLES);
    }



    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

         database.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
            onCreate(database);
    }

}

Here is where I have the insert method:

public class DatabaseConnectToActivities {


    private SQLiteDatabase database;
    private DatabaseHelp databaseHelper; //instance of databasehelp 


    //public constructor for database connection
    public DatabaseConnectToActivities(Context context){
        //create a new database open helper
        databaseHelper = 
                new DatabaseHelp(context);

    }

    public void open()throws SQLException{

        database = databaseHelper.getWritableDatabase();
    }

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

    // inserts feeling and needs into database
    public void insertEmotions(String feeling, String need){

        ContentValues emotion = new ContentValues();
        emotion.put(DatabaseHelp.FEELING_ID, feeling);
        emotion.put(DatabaseHelp.NEED_ID, need);

        open(); //open the database
        database.insert(DatabaseHelp.DATABASE_TABLE, null, emotion);
        close();
        }


    public String getData(){
        //create columns and reference the rows with a cursor object
        String[] columns = new String[] {DatabaseHelp.ROW_ID, DatabaseHelp.FEELING_ID ,DatabaseHelp.NEED_ID};
        Cursor c = database.query(DatabaseHelp.DATABASE_TABLE, columns, null, null, null, null, null);

        String result = "";

        int iRow = c.getColumnIndex(DatabaseHelp.ROW_ID);
        int iFeeling = c.getColumnIndex(DatabaseHelp.FEELING_ID);
        int iNeed = c.getColumnIndex(DatabaseHelp.NEED_ID);

        for (c.moveToFirst(); !c.isAfterLast();c.moveToNext()){
                result = result + c.getString(iRow) + " " + c.getString(iFeeling)+ " " + c.getString(iNeed)+ "\n";};

            c.close();  
            return result;

}


}

And here is where I call the insert method in my activity:

public class Needs extends Activity {

    String gotTheFeeling;
    private DatabaseConnectToActivities database;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        // Receive string from feelingsmain

        Bundle gotFeeling = getIntent().getExtras();
        gotTheFeeling = gotFeeling.getString("aFeeling");

        // new database object
        database = new DatabaseConnectToActivities(this);
        database.open();
        initList();

        ListView lv = (ListView) findViewById(R.id.pageLayout);

        SimpleAdapter simpleAdpt = new SimpleAdapter(this, needsList,
                android.R.layout.simple_list_item_1, new String[] { "need" },
                new int[] { android.R.id.text1 });

        lv.setAdapter(simpleAdpt);

        lv.setOnItemClickListener(backToFeelings);

    }

    List<Map<String, String>> needsList = new ArrayList<Map<String, String>>();
    protected String feelingForDb;

    public void initList() {

        needsList.add(createNeed("need", "space"));
        needsList.add(createNeed("need", "peace"));
        needsList.add(createNeed("need", "calm"));
        needsList.add(createNeed("need", "understanding"));
        needsList.add(createNeed("need", "to be heard"));
        needsList.add(createNeed("need", "to be seen"));
        needsList.add(createNeed("need", "love"));

    }

    private HashMap<String, String> createNeed(String key, String name) {
        HashMap<String, String> need = new HashMap<String, String>();
        need.put(key, name);

        return need;

    }

    OnItemClickListener backToFeelings = new OnItemClickListener() {

        @Override
        public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
                long arg3) {
            String need = needsList.get(arg2).toString();
            database.insertEmotions(gotTheFeeling, need);
            database.close();
            Intent toFeelings = new Intent(Needs.this, FeelingsMain.class);
            startActivity(toFeelings);

        }

    };

Thanks for any help given. :)

Answer 1

Also, be sure to increment the DATABASE_VERSION variable when you modify the structure of your tables. This will clean your database and recreate it. Otherwise you could modify as many times you want the syntaxe of your table creation without any results.

Answer 2

In your DataBaseHelper class you need to change this

 + "FEELING_ID text,"
 + "NEED_ID  text);";

for this:

 + FEELING_ID +" text, "
 + NEED_ID +" text);";

You need to have the FEELING_ID and NEED_ID outside the ".

Answer 3

you need one space after text, in + "FEELING_ID text,". Also, your complete String CREATE_TABLES is wrong. Your string variables should be outside the quotes.

proper CREATE_TABLES string is

String CREATE_TABLES = "CREATE TABLE " + DATABASE_TABLE
            + "("
            + ROW_ID + " integer primary key autoincrement, "
            + FEELING_ID + " text, "
            + NEED_ID + " text" 
            +");";