rowid of listview item and database doesn't match while deleting item of listview

Question

Here is MainActivity

I use a navigational drawer to populate a listview from a database. When I click the item in listview it starts a new fragment. But when I delete the retrieve note it doesn't match the rowid in the database and the listview.

mDrawerLayout = (DrawerLayout) findViewById(R.id.drawer_layout);
    list = (ListView) findViewById(R.id.left_drawer);
    mDbHelper = new NotesDbAdapter(this);

    mDbHelper.open();
    Log.d(tag, "connection succesfull");

    List<String> arraylist = mDbHelper.getData();

    arrayadapter = new ArrayAdapter<String>(this,

    R.layout.drawer_list_item, arraylist);

    list.setAdapter(arrayadapter);


    mDrawerLayout.setDrawerShadow(R.drawable.drawer_shadow,
            GravityCompat.START);
    list.setOnItemClickListener(new DrawerItemClickListener());

NotesdbAdapter

 public List<String> getData() {
    String[] columns = new String[] { KEY_TITLE};
    Cursor c = mDb.query(DATABASE_TABLE, columns, null, null, null, null,
        null);
    String results = "";
    List<String> results1 = new ArrayList<String>();
    int iCM = c.getColumnIndex(KEY_TITLE);

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        results1.add(c.getString(iCM));

    }
    return results1;

}

Fragment

 mDbHelper = new NotesDbAdapter(getActivity());
    mDbHelper.open();

    Log.i(tag, "connection sucessfull in planetfragment");

    Long i = getArguments().getLong(ARG_PLANET_NUMBER);

    TextView title = (TextView) rootView.findViewById(R.id.title);

    TextView body = (TextView) rootView.findViewById(R.id.body);

    Log.i(tag, "click item rowid is :[" + i + "]");

    if (i == 0||i!=0);


    {

        Log.i(tag,"row id after increment:["+i+"]");
        note = mDbHelper.fetchNote(i);
        startManagingCursor(note);
        {

            title.setText(note.getString(note
                    .getColumnIndexOrThrow(NotesDbAdapter.KEY_TITLE)));

            body.setText(note.getString(note
                    .getColumnIndexOrThrow(NotesDbAdapter.KEY_BODY)));

        }
    }
    id=i;
    Log.i(tag," id value is :["+id+"]");
    Button deletebutton = (Button) rootView.findViewById(R.id.delete_note);
    deletebutton.setOnClickListener(new Button.OnClickListener() {

        public void onClick(View arg0) {
            //Toast.makeText(getActivity(), R.string.item_click, Toast.LENGTH_LONG).show();
            Log.i(tag,"delete id value is :["+id+"]");
            if (note!=null) {
                note.close();
                note = null;
            }
            if (id!=null) {
                mDbHelper.deleteNote(id);
            }
            getActivity().finish();

        }
    });  

    return rootView;

}

private void startManagingCursor(Cursor note2) {
    // TODO Auto-generated method stub

}

public static class LineEditText extends TextView {
    // we need this constructor for LayoutInflater
    public LineEditText(Context context, AttributeSet attrs) {
        super(context, attrs);
        mRect = new Rect();
        mPaint = new Paint();
        mPaint.setStyle(Paint.Style.FILL_AND_STROKE);
        mPaint.setColor(Color.BLUE);
    }

    private Rect mRect;
    private Paint mPaint;

    @Override
    protected void onDraw(Canvas canvas) {

        int height = getHeight();
        int line_height = getLineHeight();

        int count = height / line_height;

        if (getLineCount() > count)
            count = getLineCount();

        Rect r = mRect;
        Paint paint = mPaint;
        int baseline = getLineBounds(0, r);

        for (int i = 0; i < count; i++) {

            canvas.drawLine(r.left, baseline + 1, r.right, baseline + 1,
                    paint);
            baseline += getLineHeight();

            super.onDraw(canvas);
        }

    }

}

}

`rowid of listview item and database doesn't match` That's pretty **obvious**. Make a custom row in which there is a hidden field where you store the **db** rowId. And use **that** rowId to perform deletions/updates — Phantômaxx, Jun 18 '14 at 07:43
i can retrive the click item from listView but i can't delete it because when i see log another item is deleted not not clicked one. — user3616028, Jun 18 '14 at 11:19
Have a look at this answer:http://stackoverflow.com/a/15832564/2649012. Just make set the "Header" cell `visibility = "GONE"` and use it to store your rowID. — Phantômaxx, Jun 18 '14 at 11:28
http://stackoverflow.com/questions/17638109/android-sql-delete-row-and-restore-rowid-autoincrement This is my exxact problem — user3616028, Jun 18 '14 at 11:37
OK, so you don't understand the problem. database **ids** `don't change`, while ListView **positions** are always recreated from 0, no gaps — Phantômaxx, Jun 18 '14 at 13:36
when we make custom row id. does listview item id doesn;t recreated from 0? — user3616028, Jun 18 '14 at 13:45
What you call "ListView row id" **doesn't exist**: it is **not an id**. It's a **position**. So, it's **always** starting from 0. in your database, the row id is a **real**, immutable, **id**. It's like a name. I.e.: The row named "45" is going to be deleted. But the rows named "46" and higher aren't affected. The db ids and the ListView positions aren't paired 1:1. By using a hidden field in a custom row, you will store the db row id at a certain position. So you can retrieve THAT id when you want to delete THAT row. It seems that you have no knowledge of databases, nor programming in general — Phantômaxx, Jun 18 '14 at 13:55
i am beginner in android development and slow learner boy that's awesome now i understand thank you for you great help really appreciated!! — user3616028, Jun 18 '14 at 14:00
I put my comments together and tried to make a decent answer, in case someone else has a similar question. — Phantômaxx, Jun 18 '14 at 18:05

score 2 · Answer 1 · answered Jun 18 '14 at 18:01

rowid of listview item and database doesn't match That's pretty obvious to someone who knows how a relational database works.

Database ids don't change (and aren't supposed to), while ListView positions are always recreated from 0, without leaving gaps

Since what you call "ListView row id" doesn't exist: it is not an id. It's a position.
And it's always starting from 0.
In your database, the row id is a real, immutable, id.

Imagine it's like a name.
I.e.: The row named "45" is going to be deleted.
But the rows named "46" and higher aren't affected (they don't change their name).

The db ids and the ListView positions aren't paired 1:1.

By using a hidden field in a custom row, you can store the db row id at a certain position.
So you can retrieve THAT id when you want to delete THAT row.

So, make a custom row in which there is a hidden field where you store the db rowId.
And use that rowId to perform deletions/updates

Have a look at this answer if you need to know how can you make a custom row item for your ListView: stackoverflow.com/a/15832564/2649012:
Just make set the "Header" cell visibility = "GONE" and use it to store your rowID.

rowid of listview item and database doesn't match while deleting item of listview

Here is MainActivity

NotesdbAdapter

Fragment

1 Answers1