One of the columns of my SQLite database doesn't get created

Question

I have a problem when I try to read my table in my database with a cursor. The error says:

no such column.

In this database, I have two tables. The first one works well but the second one doesn't create one of the columns.

Here is the error:

E/AndroidRuntime: FATAL EXCEPTION: main
              Process: com.app.a2.studio.android.aprendiendo.appmanejarcuentas, PID: 2895
              android.database.sqlite.SQLiteException: no such column: Apagar (code 1): , while compiling: SELECT _ID, Nombre, Apagar, Pagado, Folder FROM Usuarios
                  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
                  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
                  at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                  at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
                  at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
                  at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
                  at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1163)
                  at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1034)
                  at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1202)
                  at com.app.a2.studio.android.aprendiendo.appmanejarcuentas.DataBase.BaseDeDatos.getUsersData(BaseDeDatos.java:105)
                  at com.app.a2.studio.android.aprendiendo.appmanejarcuentas.DataBase.BaseDeDatos.deleteFolder(BaseDeDatos.java:111)
                  at com.app.a2.studio.android.aprendiendo.appmanejarcuentas.Screens.MainScreen$3.onClick(MainScreen.java:174)
                  at android.view.View.performClick(View.java:4780)
                  at android.view.View$PerformClick.run(View.java:19866)
                  at android.os.Handler.handleCallback(Handler.java:739)
                  at android.os.Handler.dispatchMessage(Handler.java:95)
                  at android.os.Looper.loop(Looper.java:135)
                  at android.app.ActivityThread.main(ActivityThread.java:5254)
                  at java.lang.reflect.Method.invoke(Native Method)
                  at java.lang.reflect.Method.invoke(Method.java:372)
                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

And my database class:

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


public class BaseDeDatos extends SQLiteOpenHelper {

private static final String DB_NAME = "AppCuentas.db";

private static final String TABLE_FOLDERS = "Folders";

private static final String TF_COLOM_ID = "_ID";
private static final String TF_COLOM_NUMBER_USERS = "NumberUsers";
private static final String TF_COLOM_NAME = "Nombre";

private static final String StringTableFolders = "CREATE TABLE " + TABLE_FOLDERS + " ("
        + TF_COLOM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + TF_COLOM_NAME + " TEXT, "
        + TF_COLOM_NUMBER_USERS + " INTEGER) ";

private static final String TABLE_USUARIOS = "Usuarios";
private static final String TU_COLOM_PRIMARY_ID = "_ID";
private static final String TU_COLOM_FOLDER = "Folder";
private static final String TU_COLOM_NAME = "Nombre";
private static final String TU_COLOM_APAGAR = "Apagar";
private static final String TU_COLOM_PAGADO = "Pagado";

private static final String StringTableUsuarios = "CREATE TABLE " + TABLE_USUARIOS +" ("
        + TU_COLOM_PRIMARY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + TU_COLOM_NAME + " TEXT, "
        + TU_COLOM_APAGAR + " INTEGER, "
        + TU_COLOM_PAGADO + " INTEGER, "
        + TU_COLOM_FOLDER + " INTEGER ) ";


public BaseDeDatos(Context context) {
    super(context, DB_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
    sqLiteDatabase.execSQL(StringTableFolders);
    sqLiteDatabase.execSQL(StringTableUsuarios);

}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
    sqLiteDatabase.execSQL("DROP TABLE IF EXIST" + TABLE_FOLDERS);
    sqLiteDatabase.execSQL("DROP TABLE IF EXIST" + TABLE_USUARIOS);
    onCreate(sqLiteDatabase);

}

public boolean addData(String name, int numberUsers){
    SQLiteDatabase db = getWritableDatabase();

    ContentValues valuesFolders = new ContentValues();

    valuesFolders.put(TF_COLOM_NAME, name);
    valuesFolders.put(TF_COLOM_NUMBER_USERS, numberUsers);

    long resultFolders = db.insert(TABLE_FOLDERS, null, valuesFolders);

    if(resultFolders == -1){
        return false;
    }
    else {
        return true;
    }
}

public boolean addData(int folder, String name, float aPagar, float pagado){
    SQLiteDatabase db = getWritableDatabase();

    ContentValues valuesUsuarios = new ContentValues();

    valuesUsuarios.put(TU_COLOM_FOLDER, folder);
    valuesUsuarios.put(TU_COLOM_NAME, name);
    valuesUsuarios.put(TU_COLOM_APAGAR, aPagar);
    valuesUsuarios.put(TU_COLOM_PAGADO, pagado);

    long resultUsuarios = db.insert(TABLE_USUARIOS, null, valuesUsuarios);

    if(resultUsuarios == -1){
        return false;
    }
    else {
        return true;
    }
}

public Cursor getAllFoldersData(){
    String folderColoms[] = {TF_COLOM_ID, TF_COLOM_NAME, TF_COLOM_NUMBER_USERS};
    Cursor c = this.getReadableDatabase().query(TABLE_FOLDERS, folderColoms, null, null, null, null, null);
    return c;
}

public Cursor getUsersData(){
    String usersColoms[] = {TU_COLOM_PRIMARY_ID, TU_COLOM_NAME, TU_COLOM_APAGAR, TU_COLOM_PAGADO, TU_COLOM_FOLDER};
    Cursor cursorUsers = this.getReadableDatabase().query(TABLE_USUARIOS, usersColoms, null, null, null, null, null);
    return cursorUsers;
}

public Integer deleteFolder(String folderID){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = getUsersData();
    Integer users = 1;

    if(c.getCount() != 0){
          users = db.delete(TABLE_USUARIOS, "Folder = ?", new String[] { folderID });
    }

    Integer folders = db.delete(TABLE_FOLDERS, "_ID = ?", new String[] { folderID });
    if(users > 0){
        if(folders > 0){
            return 1;
        }
        else {
            return -1;
        }
    }
    else {
        return -1;
    }

}

public Integer deleteUser(String userID){
    SQLiteDatabase db = this.getWritableDatabase();

    Integer user = db.delete(TABLE_USUARIOS, "_ID = ?" , new String[] { userID });

    return user;
    }
}

If you see more errors in this class please comment below

Thanks

Are you sure your db schema is updated? Probably the Usuarios table has been created before you added the Apagar column, and that's causing the exception. If i'm correct, try changing the DB_VERSION at this line: super(context, DB_NAME, null, 2); using value 2 — MatPag, Feb 06 '17 at 20:33

score 0 · Accepted Answer · answered Feb 06 '17 at 20:36

0

In my opinion, you created table TABLE_USUARIOS first without 'Apagar' column. Then later you modify the code to add 'Apagar' column.

if that is the case, reinstall your app or increase the Sqlite version

answered Feb 06 '17 at 20:36

Ajay Shrestha

2,433
1
21
25

I increased the SQLite version from 2 but now the compiler says this error: android.database.sqlite.SQLiteException: near "EXIST": syntax error (code 1): , while compiling: DROP TABLE IF EXIST Folders And this: Caused by: android.database.sqlite.SQLiteException: near "EXIST": syntax error (code 1): , while compiling: DROP TABLE IF EXIST Folders – ProRiderZ115 Feb 06 '17 at 21:10
don't call drop in onUpgrade. for better understanding follow this link https://thebhwgroup.com/blog/how-android-sqlite-onupgrade – Ajay Shrestha Feb 06 '17 at 21:21
thx so much now mi app works – ProRiderZ115 Feb 06 '17 at 22:11

One of the columns of my SQLite database doesn't get created

1 Answers1