Data deleted from an SQLite Database appear again

Question

I have a bug in my code. Whenever I delete items from my SQ Lite database, the itens are deleted. Although, when I insert a new item, the items who were removed appear again. Can you help me? Sorry to bother, but i don't know what to do.

Here it is my MainActivity.

MainActivity.java

    public class MainActivity extends Activity
{

    private InputDbHelper mHelper;
    private ListView mListView;
    private EditText mEditText;
    private Button mButton;
    ArrayList<String> list = new ArrayList<String>();
    ArrayAdapter<String> adapter;

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        mButton = (Button) findViewById(R.id.button);
        mEditText = (EditText) findViewById(R.id.editText);
        adapter = new ArrayAdapter<String>(this, android.R.layout.simple_expandable_list_item_1, list);
        mListView=(ListView)findViewById(R.id.listView);  
        mListView.setAdapter(adapter);
        mHelper = new InputDbHelper(this);
        updateUI();

        mButton.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                String input = mEditText.getText().toString();

                if (input.length() > 0) {
                SQLiteDatabase db = mHelper.getWritableDatabase();
                ContentValues values = new ContentValues();
                values.put(InputContract.TaskEntry.COL_TASK_TITLE,   input);
                db.insertWithOnConflict(InputContract.TaskEntry.TABLE, null, values, SQLiteDatabase.CONFLICT_REPLACE);                                        
                db.close();
                updateUI();
                }
            }
        });

        mListView.setOnItemClickListener(new OnItemClickListener() {
        public void onItemClick(AdapterView<?> a, View v, final int position, long id) {
        AlertDialog.Builder adb=new AlertDialog.Builder(MainActivity.this);
        adb.setTitle("Delete?");
        adb.setMessage("Are you sure you want to delete this note?");
        final int positionToRemove = position;
        adb.setNegativeButton("Cancel", null);
        adb.setPositiveButton("Ok", new AlertDialog.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                SQLiteDatabase db = mHelper.getWritableDatabase();
                db.delete(InputContract.TaskEntry.TABLE, InputContract.TaskEntry._ID + " = ?", new String[] { String.valueOf(positionToRemove)});
                list.remove(positionToRemove);
                adapter.remove(String.valueOf(positionToRemove));
                adapter.notifyDataSetChanged();
            }});
        adb.show();
        }
    });
    }

    private void updateUI() {

    ArrayList<String> taskList = new ArrayList<String>();
    SQLiteDatabase db = mHelper.getReadableDatabase();
    Cursor cursor = db.query(InputContract.TaskEntry.TABLE,
        new String[]{InputContract.TaskEntry._ID, InputContract.TaskEntry.COL_TASK_TITLE},
        null, null, null, null, null);
    while (cursor.moveToNext()) {
        int idx = cursor.getColumnIndex(InputContract.TaskEntry.COL_TASK_TITLE);
        taskList.add(cursor.getString(idx));
    }

    if (adapter== null) {
        adapter= new ArrayAdapter<String>(this,  android.R.layout.simple_expandable_list_item_1,
            taskList);
        mListView.setAdapter(adapter);
    } else {
        adapter.clear();
        adapter.addAll(taskList);
        adapter.notifyDataSetChanged();
    }

    cursor.close();
    db.close();
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // Handle action bar item clicks here. The action bar will
        // automatically handle clicks on the Home/Up button, so long
        // as you specify a parent activity in AndroidManifest.xml.
        int id = item.getItemId();

        //noinspection SimplifiableIfStatement
        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }
}

InputContract.java

    public class InputContract {
public static final String DB_NAME = "com.example.db";
public static final int DB_VERSION = 1;

public class TaskEntry implements BaseColumns {
    public static final String TABLE = "tasks";

    public static final String COL_TASK_TITLE = "title";
    }
}

My Database:

InputDbHelper.java

public class InputDbHelper extends SQLiteOpenHelper {

public InputDbHelper(Context context) {
    super(context, InputContract.DB_NAME, null, InputContract.DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String createTable = "CREATE TABLE " + InputContract.TaskEntry.TABLE + "    ( " +
            InputContract.TaskEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            InputContract.TaskEntry.COL_TASK_TITLE + " TEXT NOT NULL);";
    db.execSQL(createTable);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + InputContract.TaskEntry.TABLE);
    onCreate(db);
}
}

Answer 1

By doing this:

db.delete(InputContract.TaskEntry.TABLE,
    InputContract.TaskEntry._ID + " = ?", new String[] {
        String.valueOf(positionToRemove)
    }
);

you're coding it to use the position of the ListView item as a table ID, which it may work for the first rows when you create a new table, but when you'll start deleting items all things will get messed up.

You'll have to store the ID's either by creating a custom class for ArrayAdapter or by storing row ID to an Array/List and use positionToRemove to get the ID from that List, but that it can result to unexpected behaviur if you mess with the ListView and don't update the List data.

Check this question Custom Adapter for List View to see how you can create a custom adapter and save row ID to all ListView items along with the text.