How can I remove data in database?

Question

android.database.sqlite.SQLiteException: no such column: pos (code 1 SQLITE_ERROR): , while compiling: DELETE FROM pre_table WHERE _id = pos;

I try few things. But it doesn't work.

enter code here
/* Main Activity */

// member
Context context;
ContentValues value = new ContentValues();
ListView listView;
PreviewDBHelper dbHelper;
PreviewDBManager previewDBManager;
Cursor cursor = null;
AlertDialog.Builder builder;
private ArrayList<Preview> list = null;
private newAdapter myAdapter;
private mood_dialog mCustomDialog;
private weather_dialog wCustomDialog;
EditText etTitle;
EditText etContent;


AdapterView.OnItemLongClickListener longClickListener
        = new AdapterView.OnItemLongClickListener() {
    @Override
    public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
        // remove data
        final Integer selectedPos = position;

        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String pos = list.get(selectedPos).toString();

        list.get(position);
        db.execSQL("DELETE FROM " + PreviewDBHelper.TABLE_NAME
                + " WHERE _id = pos;");
        Toast.makeText(getApplicationContext(), "삭제 되었습니다", Toast.LENGTH_LONG).show();
        dbHelper.close();
        myAdapter.notifyDataSetChanged();

        return true;
    }


};

// DBHelper public class PreviewDBHelper extends SQLiteOpenHelper {

final static String TAG = "PreviewDBHelper";

final static String DB_NAME = "preview.db";
public final static String TABLE_NAME = "pre_table";

public final static String COL_ID = "_id";
public final static String COL_TITLE = "title";
public final static String COL_WEATHER = "weather";

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


@Override
public void onCreate(SQLiteDatabase db) {
    String sql = "CREATE TABLE " + TABLE_NAME + " (" + COL_ID + " integer primary key autoincrement, " +
            COL_TITLE + " TEXT, " + COL_WEATHER + " TEXT)";
    Log.d(TAG, sql);
    db.execSQL(sql);

}

}

I want remove data when I long click my list

Change `" WHERE _id = pos;"` to `" WHERE _id = " + pos` – B001ᛦ Jun 26 '19 at 10:15 — B001ᛦ, Jun 26 '19 at 10:15

Skizo-ozᴉʞS ツ · Answer 1 · 2019-06-26T10:37:53.377

0

You are trying to remove "pos" so, I guess your problem is on this line

db.execSQL("DELETE FROM " + PreviewDBHelper.TABLE_NAME
            + " WHERE _id = pos;");

It should be

db.execSQL("DELETE FROM " + PreviewDBHelper.TABLE_NAME
            + " WHERE _id = " +pos);

So you are deleting the position.

A cool idea you can use this method also to be more clear when deleting stuff

https://stackoverflow.com/a/17917422/4385913

Edit

But still it doesn't remove on screen

Perhaps you can use myAdapter.notifyItemRemoved(position); Or just remove it from your list and then use the notifyDataSetChanged() method from your adapter.

edited Jun 26 '19 at 10:37

answered Jun 26 '19 at 10:18

Skizo-ozᴉʞS ツ

19,464
18
81
148

Thanks! Error is gone! I can see Toast message But still it doesn't remove on screen – Kathleen Jun 26 '19 at 10:20
I can't use only that sentence. What factors should I add in front of it? // Cannot resolve method 'notifyItemRemoved(java.lang.String)' – Kathleen Jun 26 '19 at 10:27
but I already use 'notifyDataSetChanged()'; under the Toast Message Are they different? – Kathleen Jun 26 '19 at 10:30
oh i already tried both. when i tried first thing : Cannot resolve method 'notifyItemRemoved(java.lang.String)'. And second thing is already in my code :( ... – Kathleen Jun 26 '19 at 10:46
No, you are not using my last edit... copy it and paste it in your project, then compile and try. – Skizo-ozᴉʞS ツ Jun 26 '19 at 10:53
when I copy and paste -> myAdapter.notifyItemRemoved(position); has this problem :: 'notifyItemRemoved(java.lang.String)'. Should I add some method in myAdapter Class? – Kathleen Jun 26 '19 at 11:01
I don't understand why it says `java.lang.String` if position it's an `int`.... no it's fine you don't have to add anything to your adapter class... let me write other method... – Skizo-ozᴉʞS ツ Jun 26 '19 at 11:02
Are you sure `list.get(selectedPos).toString()` this is the Id that you want to remove?.... it will go to the `toString()` method, is that ok? shouldn't be like `list.get(position).getId()` or something like that? – Skizo-ozᴉʞS ツ Jun 26 '19 at 11:03
I'm not sure. Is there a better way? – Kathleen Jun 26 '19 at 11:08
Can you share the code so I can take a look upload it to Github or something? – Skizo-ozᴉʞS ツ Jun 26 '19 at 11:09
@Kathleen where is the github? – Skizo-ozᴉʞS ツ Jun 26 '19 at 15:33

score 0 · Answer 2 · answered Jun 26 '19 at 12:07

As mentioned in another answers, that delete query is wrong and should be updated to:

db.execSQL("DELETE FROM " + PreviewDBHelper.TABLE_NAME + " WHERE _id = " + pos);

As you said, however, the list is not being updated. This is happening because you must delete the item in the Adapter as well.

So, I suggests following changes:

@Override
public boolean onItemLongClick(....) {

    // Delete using db.delete. This way, you can confirm the number of rows deleted
    int rowsDeleted = db.delete(PreviewDBHelper.TABLE_NAME, "_id=?", new String[]{Integer.toString(pos)});

    if(rowsDeleted > 0) {
        // Remove the item from the list. Adapter render items according to the items in your list (and not according to the items in the database)
        list.remove(pos);

        // Then, notify the adapter that the list was changed
        myAdapter.notifyDataSetChanged();
    }
}

I'm sorry, but error said it can't applied integer type. – Kathleen Jun 26 '19 at 12:41 — Kathleen, Jun 26 '19 at 12:41
new String[]{Integer.toString(pos)}); :: here – Kathleen Jun 26 '19 at 12:51 — Kathleen, Jun 26 '19 at 12:51
they said pos cannot be applied – Kathleen Jun 26 '19 at 12:52 — Kathleen, Jun 26 '19 at 12:52

How can I remove data in database?

2 Answers2

Edit