Android Databases: Help need with deleting items from database-populated ListView

Question

I have a ListView populated with a locally stored database. When you click on a list item it brings you to the viewArtist activity which fills textView boxes with row entry information based on the ROW_ID of the item you click. This is done using putExtra(). The viewArtist activity has a button to delete an entry. When you delete an entry the list doesn't refresh so you've to close the app and go back in.

But my main problem is upon deleting an item, the list no longer shows the entry but when you click on the one that's in place of the deleted one, no information is diplayed in the editText views in the next screen. As well as that, when you click on any list entry that follows this one, it fills the textViews with information on the previous list entry.

Here's a picture to illustrate what I'm trying to explain.

Picture 1: https://i.sstatic.net/PbyI7.jpg

It seems as though the row is being deleted but list is still matching each ROW_ID to a list item and no autodecrementing.

Any help would be much appreciated as I've been trying to figure this out for days! Thanks in advance!

Here is the mainActivity class that calls the viewArtist class:

package com.example.msdproject;

import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.content.Intent;
import android.widget.ListView;  
import android.widget.Button;
import android.widget.ArrayAdapter;
import android.view.View;
import java.sql.SQLException;
import java.util.ArrayList;


public class MainActivity extends ListActivity {

public ArrayList<String> data_list=new ArrayList<String>();

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    Button btn = (Button)findViewById(R.id.addConcertButton);
    btn.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            Intent i = new Intent(MainActivity.this, AddArtist.class);
            startActivity(i);
        }
    });

    DBManager db =  new DBManager(this);

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

    ListView listView = (ListView) findViewById(android.R.id.list);
    Cursor c = db.getAllConcerts();
    if (c.moveToFirst())
    {
        do {
            data_list.add(c.getString(0));
        } while (c.moveToNext());
    }
    ArrayAdapter<String> concertList=new ArrayAdapter<String>(getApplicationContext(),       android.R.layout.simple_list_item_1, data_list);
    listView.setAdapter(concertList);
    concertList.notifyDataSetChanged();

    db.close();
}

protected void onListItemClick(ListView l, View v, int position, long id) {
    super.onListItemClick(l, v, position, id);

    Intent i =  new Intent(MainActivity.this, ViewArtist.class);
    String strLong = Long.toString(id);
    //String idString = Objects.toString(id, null);

    if(strLong == "null")
    {
        id = id + 1;
        i.putExtra("id", id);
        startActivity(i);

    }

    else
    {
        i.putExtra("id", id);
        startActivity(i);
    }


}

}

Here is my ViewArtist class:

package com.example.msdproject;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import java.sql.SQLException;


public class ViewArtist extends Activity {

    DBManager db = new DBManager(this);

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_view_artist);

        Intent intent = getIntent();
        final long num = intent.getLongExtra("id", 1);

        Button btn = (Button)findViewById(R.id.updateConcertButton);
        btn.setOnClickListener(new View.OnClickListener()
        {

            @Override
            public void onClick(View v)
            {
                Intent i = new Intent(ViewArtist.this, UpdateArtist.class);
                i.putExtra("id", num);
                startActivity(i);
            }
        });

        String name;
        String venue;
        String date;
        String comments;

        try {
            db.open();
            Cursor c = db.getConcert(num + 1);
            //The reason I have num + 1 is because List Item 1 has a ROW_ID of 0
            if (c.moveToFirst())
            {
                do
                {
                    name = (c.getString(1));
                    venue = (c.getString(2));
                    date = (c.getString(3));
                    comments = (c.getString(4));

                    TextView nameTxt = (TextView)findViewById(R.id.viewName);
                    TextView titleTxt = (TextView)findViewById(R.id.viewTitle);
                    TextView venueTxt = (TextView)findViewById(R.id.viewVenue);
                    TextView dateTxt = (TextView)findViewById(R.id.viewDate);
                    TextView commentsTxt = (TextView)findViewById(R.id.viewComments);

                    nameTxt.setText(name);
                    titleTxt.setText("ROW_ID: " + String.valueOf(num));
                    venueTxt.setText(venue);
                    dateTxt.setText(date);
                    commentsTxt.setText(comments);

                    Button btn2 = (Button)findViewById(R.id.deleteConcertButton);
                    btn2.setOnClickListener(new View.OnClickListener()
                    {

                        @Override
                        public void onClick(View v) {
                            db.deleteConcert(num);

                        }
                    });

                } while (c.moveToNext());
            }
        }

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

        db.close();

    }
}

and here is my DBManager class:

package com.example.msdproject;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.content.Context;

import java.sql.SQLException;


public class DBManager {

    public static final String COL_ROWID = "_id";
    public static final String COL_NAME = "name";
    public static final String COL_VENUE = "venue";
    public static final String COL_DATE = "date";
    public static final String COL_COMMENTS = "comments";

    private static final String DB_NAME = "Concerts";
    private static final String DB_TABLE = "Concert_Info";
    private static final int DB_VERSION = 1;

    private static final String DB_CREATE =
            "create table " + DB_TABLE + 
    " (_id integer primary key autoincrement, " +
            "name text not null, " +
            "venue text not null, " +
            "comments text not null, " +
            "date text not null);";

    private final Context context;

    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBManager(Context ctx)
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    //embedded class
    public static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context)
        {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            db.execSQL(DB_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            /*
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("drop table if exists Concert_Info");
            onCreate(db);*/
        }
    }

    public DBManager open() throws SQLException
    {
        db = DBHelper.getWritableDatabase();
        //db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE + ";");
        return this;
    }

    public void close()
    {
        DBHelper.close();
    }

    public long getId(String name)
    {
        long x = 10;
        return x;
    }
    public long insertConcert(String name, String venue, String date, String comments)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(COL_NAME, name);
        initialValues.put(COL_VENUE, venue);
        initialValues.put(COL_DATE, date);
        initialValues.put(COL_COMMENTS, comments);
        return db.insert(DB_TABLE, null, initialValues);
    }

    public boolean deleteConcert(long ROW_ID)
    {
        return db.delete(DB_TABLE, COL_ROWID + "=" + ROW_ID, null) > 0;
    }

    public Cursor getAllConcerts() {
        return db.query(DB_TABLE, new String[]
                        {
                                COL_NAME,
                                COL_VENUE,
                                COL_DATE,
                                COL_COMMENTS
                        },
                        null,
                        null,
                        null,
                        null,
                        null
                );
    }

    public Cursor getConcert(long ROW_ID) throws SQLException
    {
        Cursor mCursor =
                db.query(DB_TABLE, new String[]
                        {
                                COL_ROWID,
                                COL_NAME,
                                COL_VENUE,
                                COL_DATE,
                                COL_COMMENTS
                        },
                        COL_ROWID + "=" + ROW_ID,
                        null,
                        null,
                        null,
                        null
                );
        if (mCursor != null)
        {
            mCursor.moveToFirst();
        }

        return mCursor;

    }


    public boolean updateConcert(long ROW_ID, String name, String venue, String date, String comments)
    {
        ContentValues args = new ContentValues();
        args.put(COL_NAME, name);
        args.put(COL_VENUE, venue);
        args.put(COL_DATE, date);
        args.put(COL_COMMENTS, comments);
        return db.update(DB_TABLE, args, COL_ROWID  + "=" + ROW_ID, null) > 0;

    }

}

Answer 1

You will need to add code to the onResume in the fragment or activity that renders the list to update the cursor and so also update the list view to remove the deleted values

it also seems you may be adding a new list to the existing list. in the OnResume what you need to do is update the data_list and call concertList.notifyDataSetChanged();. For lists coming from the DB i use a CursorAdapter and not an Array adapter as you are doing so the actual mechanics not 100% familiar

Answer 2

Two things, the comment made as to the db id versus the list position is correct. you will need to query the db not based on the position i the list but based on the value in the list. ( that is if it is unique) in order to get the detail view/ record from the database.

The second/first is that the list activity did not go away/was not destroyed when you selected the list and went to the detail view and so when you get back to your list it would still show the values that were previously queried.

You will need to add code to the onResume in the fragment or activity that renders the list to update the cursor and so also update the list view to remove the deleted values