Android Studio SQLiteDatabase - no such column

Question

I have just recently started working with SQlite database and have ran into a problem. Whenever I try to update or delete data, the app crashes and displays and error "no such column: s_id". I have tried to fix it for days but have had no luck. I would appreciate if someone could tell me what the problem is and why it is occurring. Thank you.

Here is my database:

public class DatabaseHelper extends SQLiteOpenHelper{

public static final String DATABASE_NAME = "sqlite.db";
public static final int VERSION = 1;

public static final String TABLE_NAME = "tbl_reminder";
public static final String S_ID = "s_id";
public static final String S_TITLE = "s_title";
public static final String S_DATE = "s_date";

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

@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            S_ID +" TEXT)";
    db.execSQL(CREATE_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP_TABLE" + TABLE_NAME);
    onCreate(db);
}

public void insertData (String s_title){
    ContentValues contentValues = new ContentValues();
    contentValues.put(S_TITLE, s_title);

    SQLiteDatabase sqLiteDB = this.getWritableDatabase();
    sqLiteDB.insert(TABLE_NAME, null, contentValues);
    sqLiteDB.close();
}

public ArrayList<ViewHolderHelper> getAllData() {
    ArrayList<ViewHolderHelper> list = new ArrayList<>();
    String SQL = "SELECT * FROM " + TABLE_NAME;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(SQL, null);
    if (cursor.moveToFirst()) {
        do {
            ViewHolderHelper vhh = new ViewHolderHelper();
            vhh.setiD(cursor.getInt(0) + "");
            vhh.setTitle(cursor.getString(1));
            list.add(vhh);
        } while (cursor.moveToNext());
    }
    return list;
}

public void updateData(int id, String title){
    ContentValues contentValues = new ContentValues();
    contentValues.put(S_TITLE, title);

    SQLiteDatabase sqliteDB = this.getWritableDatabase();
    sqliteDB.update(TABLE_NAME, contentValues, S_ID + "=" + id, null);
    sqliteDB.close();
}

public void deleteData(int id){
    SQLiteDatabase sqliteDB = this.getWritableDatabase();
    sqliteDB.delete(TABLE_NAME, S_ID + "=" + id, null);
    sqliteDB.close();
}
}

Here is also the error that is displayed:

FATAL EXCEPTION: main

    Process: json.google_services.newreminderapp, PID: 23419                                                                              
android.database.sqlite.SQLiteException: no such column: s_id (code 1): , while compiling: UPDATE tbl_reminder SET s_title=? WHERE s_id=1

Answer 1

Delete

public void deletUser(String name) {
    String selection = NewUserInfo.USER_NAME + " LIKE ?";
    String[] selection_args = {name};

    getWritableDatabase().delete(NewUserInfo.TABLE_NAME, selection, selection_args);
}

Update

public int updateInformation(String oldName, String newName, String newAge, String newMobile, int newGender) {
    String selection = NewUserInfo.USER_NAME + " LIKE ?";
    String[] selection_args = {oldName};

    ContentValues contentValues = new ContentValues();
    contentValues.put(NewUserInfo.USER_NAME, newName);
    contentValues.put(NewUserInfo.USER_AGE, newAge);
    contentValues.put(NewUserInfo.USER_MOB, newMobile);
    contentValues.put(NewUserInfo.USER_GENDER, newGender);

    int count = getWritableDatabase().update(NewUserInfo.TABLE_NAME, contentValues, selection, selection_args);
    return count;
}

Answer 2

@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " ("+S_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, " +
        S_ID +" TEXT)";
db.execSQL(CREATE_TABLE);
}

Answer 3

The problem is in your createTable, you defined ID as the primary key and s_id as a seperate column. then later in your insert you never gave any data to you s_id column, so therefore your column never truely exists.

To corrrect this you should use s_id as the auto incremented primary key.

Also you insert S_TITLE and I don't see that column in your onCreate aswell.

Based on the rest of the class i believe your CREATE statement should be:

"CREATE TABLE " +TABLE_NAME + " ( " + S_ID + " INTEGER AUTOINCREMENT PRIMARY KEY + ", " + S_TITLE + " TEXT"