SQLLite Android table not acknowledging database changes - "no such column: type (code 1):"

Question

I am trying to add a key to my SQLite DB but when I try to access the new column, the program throws "no such column type (code 1)". I verified in SQLite Editor that the new column is in fact present. Am I missing something?

SOLUTION FOUND My Database creation was NOT taking in the DATABASE_VERSION, I had simply set it to use "1". As soon as I set it to use that variable, and changed it from 1 to 2, it updated my database!

Here is my class

package com.example.blizz_000.lureorganizer;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

import java.sql.SQLException;

public class LureOrganizer {

  public com.example.blizz_000.lureorganizer.DatabaseHelper ourHelper;
  private final Context ourContext;
  private SQLiteDatabase ourDatabase;

  public static final String KEY_NAME = "model";
  public static final String KEY_MAKER = "company";
  public static final String KEY_SIZE = "size";
  public static final String KEY_COLOR = "color";
  public static final String KEY_TYPE = "TEST";
  public static final String KEY_ROWID = "_id";
  private static final String DATABASE_NAME = "fishing_db";
  private static final String DATABASE_TABLE = "LURES";
  public static int DATABASE_VERSION = 1;


    // DATABASE HELPER CLASS*********************************************************
private static class DatabaseHelper extends SQLiteOpenHelper {

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

    @Override
    public void onCreate(SQLiteDatabase db) {


        db.execSQL("CREATE TABLE " + DATABASE_TABLE +" (" +
                KEY_ROWID +"INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_NAME + " TEXT, " +
                KEY_MAKER + " TEXT, " +
                KEY_COLOR + " TEXT, " +
                "TEST text, " +
                KEY_SIZE + " TEXT);");

        //db.execSQL("ALTER TABLE LURES ADD COLUMN TEST TEST"); // Won't find the table

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);
    }
}
// END DATABASE HELPER CLASS *********************************************************


//*********************BEGIN MY METHODS**************************
    public LureOrganizer(Context c) {
        ourContext = c;}


    //Opens Db
    public LureOrganizer open() throws SQLException{
        ourHelper = new com.example.blizz_000.lureorganizer.DatabaseHelper(ourContext);
        ourDatabase = ourHelper.getWritableDatabase();
        return this;}

    //Opens Db NO EXCEPTION
    public LureOrganizer opennoe() {
        ourHelper = new com.example.blizz_000.lureorganizer.DatabaseHelper(ourContext);
        ourDatabase = ourHelper.getWritableDatabase();
        return this;}


    //Closes Db
    public void close() {
        ourHelper.close();}


    //Entries
    public long createEntry(String name, String maker, String color, String size, String type) {
        ContentValues cv = new ContentValues();
        cv.put(KEY_NAME, name);
        cv.put(KEY_MAKER, maker);
        cv.put(KEY_COLOR, color);
        cv.put(KEY_SIZE, size);
        cv.put(KEY_TYPE, type);
        return ourDatabase.insert(DATABASE_TABLE, null, cv);}






//RETRIEVE ALL PIECES OF THE DATA *****************************************************************************************************//
    //Retrive Data for SINGLE PIECE
   public String[] getData(String id) {
       String[] columns = new String[]{KEY_ROWID, KEY_NAME, KEY_MAKER, KEY_COLOR, KEY_SIZE};
       Cursor d = ourDatabase.rawQuery("select * from LURES WHERE _id = ?",
               new String[]{id});

       String[] resultarray = new String[5];
       int iName = d.getColumnIndex(KEY_NAME);
       int iMaker = d.getColumnIndex(KEY_MAKER);
       int iColor = d.getColumnIndex(KEY_COLOR);
       int iSize = d.getColumnIndex(KEY_SIZE);
       int iType = d.getColumnIndex(KEY_TYPE);

       for (d.moveToFirst(); !d.isAfterLast(); d.moveToNext()) {
           resultarray[0] = d.getString(iMaker);
           resultarray[1] = d.getString(iName);
           resultarray[2] = d.getString(iColor);
           resultarray[3] = d.getString(iSize);
           resultarray[4] = d.getString(iType);
       }
       return resultarray;
   }

    //Retrieve Data
    public String[] getName() {
        String[] columns = new String[]{KEY_ROWID, KEY_NAME, KEY_MAKER, KEY_COLOR, KEY_SIZE, KEY_TYPE};
        Cursor d = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
        String result ="";

        String[] resultarray = new String[5];
        int iRow = d.getColumnIndex(KEY_ROWID);
        int iName = d.getColumnIndex(KEY_NAME);
        int iMaker = d.getColumnIndex(KEY_MAKER);
        int iColor = d.getColumnIndex(KEY_COLOR);
        int iSize = d.getColumnIndex(KEY_SIZE);
        int iType = d.getColumnIndex(KEY_TYPE);

        for(d.moveToFirst(); !d.isAfterLast(); d.moveToNext()){
            resultarray[0] = d.getString(iMaker);
            resultarray[1] = d.getString(iName);
            resultarray[2] = d.getString(iColor);
            resultarray[3] = d.getString(iSize);
            resultarray[4] = d.getString(iType);



        }

        return resultarray;
    }

    //CURSOR READER
    public Cursor readEntry() {

        String[] columns = new String[]{KEY_ROWID, KEY_NAME, KEY_MAKER, KEY_COLOR, KEY_SIZE, KEY_TYPE};
        Cursor d = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
        return d;

    }

    public void DeleteRow(String id) {
        ourDatabase.delete(DATABASE_TABLE, "_id = ?",
                new String[]{id});

   }
}

I know that the code works other than the KEY_TYPE stuff, so I don't feel it is necessary to include other classes, but I will if it helps.

http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run — laalto, Dec 28 '14 at 08:16
if you have added that col LATER, then you have to increase the db-version so onUpgrade() got called, and right there you have to add the ALTER statement, and BTW, your alter qry is NOT correct, i think it has a typo `COLUMN TEST TEST` test test should be test text — Yazan, Dec 28 '14 at 08:22

score 1 · Answer 1 · answered Dec 28 '14 at 05:25

Whenever you are creating the object to Database open helper it wont call onCreate of that class. only first time that too when you call getWritableDatabse() or getReadableDatabase() at that time only it will call if the database file not exist in your application. why i am saying this because you have written some alter command over there.

If you run the application with out uninstalling at that time database file will not be deleted so it wont call oncreate of that class.

while writing database related code better to uninstall and run the application.

and you are missing one space after the id coloumn.

db.execSQL("CREATE TABLE " + DATABASE_TABLE +" (" +
                KEY_ROWID +" INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_NAME + " TEXT, " +
                KEY_MAKER + " TEXT, " +
                KEY_COLOR + " TEXT, " +
                "TEST text, " +
                KEY_SIZE + " TEXT);");

I hope this will help you.

I tried uninstalling the app and it still didn't work. – Tyler Klein Dec 28 '14 at 15:03 — Tyler Klein, Dec 28 '14 at 15:03

score 1 · Accepted Answer · answered Dec 28 '14 at 10:42

1

When you change your database structure (i.e.: altering a table), you have to inform it that your db has changed.

This is done by setting the DATABASE_VERSION constant to a higher value, i.e.: 2.

So:

public static int DATABASE_VERSION = 2;

This will let the onUpgrade() method fire and recreate the table.

And you miss a space after KEY_ROWID:

db.execSQL("CREATE TABLE " + DATABASE_TABLE +" (" +
    KEY_ROWID +" INTEGER PRIMARY KEY AUTOINCREMENT, " +
    KEY_NAME + " TEXT, " + KEY_MAKER + " TEXT, " +
    KEY_COLOR + " TEXT, " + "TEST text, " + KEY_SIZE + " TEXT)");

answered Dec 28 '14 at 10:42

Phantômaxx

37,901
21
84
115

Still won't work.... I tried simply changing the version to 2 right at the top of the document and it didn't fix it. – Tyler Klein Dec 28 '14 at 14:11
And what about adding the **space**? – Phantômaxx Dec 28 '14 at 14:12
No dice either. I have tried everything I can think of. I even re-did the class in a separate file and still won't do it. – Tyler Klein Dec 28 '14 at 15:00
Could you please add your logcat error (the **complete** one, not the thing you wrote in the title) to your question? – Phantômaxx Dec 28 '14 at 15:11
Added logcat. I can add my DatabaseHelper class too if needed. – Tyler Klein Dec 28 '14 at 15:15
For curiosity: what do you see in the database, if you open it with a tool such as the SQLite Manager addon for FireFox? – Phantômaxx Dec 28 '14 at 15:18
I see the columns other than Type, which is not there. The database and table function fine without me trying to add and use the row that will hold the type (a string) of the object. – Tyler Klein Dec 28 '14 at 15:39
`other than Type` But the column name is **Test**, **not Type**. `trying to add and use the row that will hold the type` **All rows are created equal**... – Phantômaxx Dec 28 '14 at 15:43
`public static final String KEY_TYPE = "TEST";` You should see the column **TEST**. There's no column named **TYPE**. – Phantômaxx Dec 28 '14 at 15:54
I have it set to TEST because it wasn't working when it was type, so I changed it. Still didn't work. – Tyler Klein Dec 28 '14 at 15:56
It would be a good idea to delete the db from your emulator or device and re-run the app. – Phantômaxx Dec 28 '14 at 16:12
How can I do that? I tried uninstalling the app, but it didn't change anything. – Tyler Klein Dec 28 '14 at 16:20
1

What I ended up doing was changing the database title to fishing_db2 and it created a new database, then went back to fishing_db, and it worked... thanks! – Tyler Klein Dec 28 '14 at 16:24
It still won't update the database, it works if I create a new database, but that seems like it shouldn't be necessary... – Tyler Klein Dec 28 '14 at 21:01
No it shouldn't be. Changing the `DATABASE_VERSION` constant should be enough. – Phantômaxx Dec 29 '14 at 07:47

SQLLite Android table not acknowledging database changes - "no such column: type (code 1):"

2 Answers2