Reputation: 614

Row not deleting from SQLite Database

I have an ArrayList that is part of a training log that I am working on. I have set it up so that when I click on one of the cells of the ListView (in TrainingLog.java), it should delete the row from the database, and thus the cell when the table is reloaded, but it is not having any effect. What am I doing wrong (is this an issue with my database DBAdapter.java?), and is there any better way to approach the issue?

TrainingLog.java :

public class TrainingLog extends Fragment {

    ListView lv;
    ArrayList<String> players = new ArrayList<String>();
    ArrayAdapter<String> adapter;
    ArrayAdapter<String> clearAdapter;
    ArrayList<String> details = new ArrayList<String>();
    ArrayList<String> dates = new ArrayList<String>();



    public TrainingLog() {
        // Required empty public constructor
    }



    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container,
                             Bundle savedInstanceState) {
        // Inflate the layout for this fragment

        View rootView = inflater.inflate(R.layout.fragment_training_log, container, false);
        lv = (ListView) rootView.findViewById(R.id.myListView);
        final DBAdapter db = new DBAdapter(this.getActivity());


        setHasOptionsMenu(true);

        players.clear();
        details.clear();
        dates.clear();

        //OPEN
        db.openDB();



        //RETRIEVE
        Cursor c=db.getAllNames();

        while(c.moveToNext())
        {
            String name=c.getString(1);
            players.add(name);
            String pos=c.getString(2);
            details.add(pos);
            String date=c.getString(3);
            dates.add(date);
        }

        db.close();

        String[] playerArray = new String[players.size()];
        playerArray = players.toArray(playerArray);

        String[] detailArray =  new String[details.size()];
        detailArray = details.toArray(detailArray);

        String[] datesArray = new String[dates.size()];
        datesArray = dates.toArray(datesArray);

        ListAdapter myAdapter = new TrainingAdapter(this.getActivity(), playerArray, detailArray, datesArray);
        lv.setAdapter(myAdapter);

        final String[] finalPlayerArray = playerArray;
        final String[] finalDetailArray = detailArray;
        final String[] finalDateArray = datesArray;

        lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

                String player = finalPlayerArray[position];
                String detail = finalDetailArray[position];
                String onedate = finalDateArray[position];


                 //Toast.makeText(getActivity(),player, Toast.LENGTH_LONG).show();

                //OPEN
                db.openDB();

                //Remove

                db.deleteInterestID(position);
                Toast.makeText(getActivity(), position, Toast.LENGTH_SHORT).show();



                //CLOSE DB
                db.close();

                //Reload

                players.clear();
                details.clear();
                dates.clear();

                //OPEN
                db.openDB();

                //RETRIEVE
                Cursor c=db.getAllNames();

                while(c.moveToNext())
                {
                    String name=c.getString(1);
                    players.add(name);
                    String pos=c.getString(2);
                    details.add(pos);
                    String date=c.getString(3);
                    dates.add(date);
                }

                db.close();

                String[] playerArray = new String[players.size()];
                playerArray = players.toArray(playerArray);

                String[] detailArray =  new String[details.size()];
                detailArray = details.toArray(detailArray);

                String[] datesArray = new String[dates.size()];
                datesArray = dates.toArray(datesArray);

                ListAdapter myAdapter = new TrainingAdapter(getActivity(), playerArray, detailArray, datesArray);
                lv.setAdapter(myAdapter);

            }
        });

        return rootView;
    }


    @Override
    public void onResume() {
        super.onResume();


        final DBAdapter db = new DBAdapter(this.getActivity());

        players.clear();
        details.clear();
        dates.clear();

        ///OPEN
        db.openDB();



        //RETRIEVE
        Cursor c=db.getAllNames();

        while(c.moveToNext())
        {
            String name=c.getString(1);
            players.add(name);
            String pos=c.getString(2);
            details.add(pos);
            String date=c.getString(3);
            dates.add(date);
        }

        db.close();

        String[] playerArray = new String[players.size()];
        playerArray = players.toArray(playerArray);

        String[] detailArray =  new String[details.size()];
        detailArray = details.toArray(detailArray);

        String[] datesArray = new String[dates.size()];
        datesArray = dates.toArray(datesArray);

        ListAdapter myAdapter = new TrainingAdapter(this.getActivity(), playerArray, detailArray, datesArray);
        lv.setAdapter(myAdapter);

        final String[] finalPlayerArray = playerArray;

        lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

                String player = finalPlayerArray[position];
                //Toast.makeText(getActivity(),player, Toast.LENGTH_LONG).show();

            }
        });

    }

    @Override
    public void onCreateOptionsMenu(
            Menu menu, MenuInflater inflater) {
        inflater.inflate(R.menu.traning_menu_itemdetail, menu);
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // handle item selection
        switch (item.getItemId()) {
            case R.id.action_add:
                Intent trainingCreateIntent = new Intent (getContext(), TrainingLogCreate.class);
                startActivity(trainingCreateIntent);
                return true;
            default:
                return super.onOptionsItemSelected(item);
        }
    }
}

DBAdapter:

public class DBAdapter {

    //COLUMNS
    static final String ROWID="id";
    static final String NAME = "name";
    static final String POSITION = "position";
    static final String DATE = "date";

    //DB PROPERTIES
    static final String DBNAME="m_DB";
    static final String TBNAME="m_TB";
    static final int DBVERSION='1';

    static final String CREATE_TB="CREATE TABLE m_TB(id INTEGER PRIMARY KEY AUTOINCREMENT,"
            + "name TEXT NOT NULL,position TEXT NOT NULL,date TEXT NOT NULL);";

    final Context c;
    SQLiteDatabase db;
    DBHelper helper;

    public DBAdapter(FragmentActivity ctx) {
        // TODO Auto-generated constructor stub

        this.c=ctx;
        helper=new DBHelper(c);
    }

    // INNER HELPER DB CLASS
    private static class DBHelper extends SQLiteOpenHelper
    {

        public DBHelper(Context context ) {
            super(context, DBNAME, null, DBVERSION);
            // TODO Auto-generated constructor stub
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            try
            {
                db.execSQL(CREATE_TB);
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub

            Log.w("DBAdapetr","Upgrading DB");

            db.execSQL("DROP TABLE IF EXISTS m_TB");

            onCreate(db);
        }

    }

    // OPEN THE DB
    public DBAdapter openDB()
    {
        try
        {
            db=helper.getWritableDatabase();

        }catch(SQLException e)
        {
            Toast.makeText(c, e.getMessage(), Toast.LENGTH_LONG).show();
        }

        return this;
    }


    //CLOSE THE DB
    public void close()
    {
        helper.close();
    }

    //INSERT INTO TABLE
    public long add(String name,String pos, String date)
    {
        try
        {
            ContentValues cv=new ContentValues();
            cv.put(NAME, name);
            cv.put(POSITION, pos);
            cv.put(DATE, date);

            return db.insert(TBNAME, ROWID, cv);

        }catch(SQLException e)
        {
            e.printStackTrace();
        }

        return 0;
    }

    //REMOVE FROM TABLE
    public void deleteInterestID(int id) {
        /* SQLiteDatabase db=helper.getWritableDatabase();
        String[] whereArgs = {name};
        db.delete(TBNAME, NAME+"=?", whereArgs); */

        SQLiteDatabase db=helper.getWritableDatabase();
        db.delete(TBNAME, NAME + "=?", new String[]{String.valueOf(id)});
        db.close();



    }

    //GET ALL VALUES

    public Cursor getAllNames()
    {
        String[] columns={ROWID,NAME,POSITION,DATE};

        return db.query(TBNAME, columns, null, null, null, null, null);
    }





}

EDIT: CRASH ERROR:

02-27 12:57:00.985 20189-20189/com.hardingsoftware.hrcfitness E/AndroidRuntime: FATAL EXCEPTION: main
                                                                            Process: com.hardingsoftware.hrcfitness, PID: 20189
                                                                            android.content.res.Resources$NotFoundException: String resource ID #0x6
                                                                                at android.content.res.Resources.getText(Resources.java:299)
                                                                                at android.widget.Toast.makeText(Toast.java:285)
                                                                                at com.hardingsoftware.hrcfitness.TrainingLog$1.onItemClick(TrainingLog.java:104)
                                                                                at android.widget.AdapterView.performItemClick(AdapterView.java:305)
                                                                                at android.widget.AbsListView.performItemClick(AbsListView.java:1146)
                                                                                at android.widget.AbsListView$PerformClick.run(AbsListView.java:3053)
                                                                                at android.widget.AbsListView$3.run(AbsListView.java:3860)
                                                                                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)

Upvotes: 0

Answers (3)

Rohit5k2

Reputation: 18112

Change your delete method signature to public void deleteInterestID(string pos) { and query to

db.delete(TBNAME, POSITION + "=?", new String[]{pos});

and then

 db.deleteInterestID(position);

 db.deleteInterestID(finalDetailArray[position]);

Update

Remove method onResume completely. You already have that code in onCreateView. No need to write twice. You are again adding listener here, which is removing one added in onCreateView in which you are doing the delete operation.

Upvotes: 1

John Joe

Reputation: 12803

By looking at DBAdapter, you have a table with column id, name, position and date.

In TrainingLog, you passing the position and want the column which has value same as position get deleted in DBAdapter. Which column you pointed to ? position or id ?

If you want delete the value in position column, change this line db.delete(TBNAME, NAME + "=?", new String[]{String.valueOf(id)}); to db.delete(TBNAME, POSITION + "=?", new String[]{String.valueOf(id)}); if id, then change POSITION to ROWID.

Upvotes: 1

Henning Luther

Reputation: 2075

All cruds in the database use transaction -> db.beginTransaction() -> db.setTransactionSuccessful() -> db.endTransaction(), so the final commit can happen.

Upvotes: -1

Row not deleting from SQLite Database

Answers (3)

Related Questions