0

When I try to delete an item in list view ,it removes it from list view but when I add a new item or run the application again the items is still there.I can't delete it from database.

I am using this code:

...
SqlHandler sqlHandler;
ListView myListView;
myAdapter adapter;
ArrayList<myItems> items;
 ...
 myListView = (ListView) findViewById(R.id.myListView);
 sqlHandler = new SqlHandler(this);
 items = getItemsFromDatabase();

adapter = new myAdapter(this, items);
myListView.setAdapter(adapter);
myListView.setOnItemLongClickListener(this);
 ...
public boolean onItemLongClick(AdapterView<?> adapterView, View view,
        int position, long id) {

    final myItems selectedItem = items.get(position);

    if (selectedItem != null) {
        AlertDialog.Builder alt_bld = new AlertDialog.Builder(this);
        alt_bld.setMessage("Do you want to delete this item?")
                .setCancelable(false)
                .setPositiveButton("Yes",
                        new DialogInterface.OnClickListener() {
                            public void onClick(DialogInterface dialog,
                                    int id) {

                                sqlHandler=new SqlHandler(getApplicationContext());
                                sqlHandler.deleteRecord(id);
                                items.remove(selectedItem);
                                adapter.notifyDataSetChanged();

For the database I use :

public class SqlHandler {
    public static final String DATABASE_NAME = "MY_DATABASE";
    public static final String DATABASE_TABLE = "MEM";
    public static final String KEY_ROWID = "_id";

        public static final int DATABASE_VERSION = 1;
            Context context;
            SQLiteDatabase sqlDatabase;
            SqlDbHelper dbHelper;

            public SqlHandler(Context context) {

                 dbHelper = new SqlDbHelper(context, DATABASE_NAME, null,
                   DATABASE_VERSION);
                 sqlDatabase = dbHelper.getWritableDatabase();
                }

    public void executeQuery(String query) {
     try {

      if (sqlDatabase.isOpen()) {
       sqlDatabase.close();
      }

      sqlDatabase = dbHelper.getWritableDatabase();
      sqlDatabase.execSQL(query);

     } catch (Exception e) {

      System.out.println("DATABASE ERROR " + e);
     }

}

        public Cursor selectQuery(String query) {
         Cursor c1 = null;
         try {

          if (sqlDatabase.isOpen()) {
           sqlDatabase.close();

          }
          sqlDatabase = dbHelper.getWritableDatabase();
          c1 = sqlDatabase.rawQuery(query, null);

         } catch (Exception e) {

          System.out.println("DATABASE ERROR " + e);

         }
         return c1;

}
        public SqlHandler open() throws SQLException
        {
            sqlDatabase = dbHelper.getWritableDatabase();
        return this;
        }

        public void Close(){
            dbHelper.close();

        }
        public void  deleteRecord(long rowId){
            try {
             sqlDatabase.delete(DATABASE_TABLE,KEY_ROWID + "="+rowId,null);
            }
            catch (Exception e)
            {
                Log.e("DB ERROR", e.toString());
                e.printStackTrace();
            }
        }

and 

public class SqlDbHelper extends SQLiteOpenHelper {
public static final String DATABASE_TABLE = "MEM";



public static final String TAG="DbHandler";

 public static final String KEY_ROWID = "_id";
 ...


 private static final String SCRIPT_CREATE_DATABASE = "create table "
   + DATABASE_TABLE + " (" + KEY_ROWID
   + " integer primary key autoincrement, " + ....);";

 public SqlDbHelper(Context context, String name, CursorFactory factory,
   int version) {
  super(context, name, factory, version);
  // TODO Auto-generated constructor stub

 }

 @Override
 public void onCreate(SQLiteDatabase db) {
  // TODO Auto-generated method stub
  db.execSQL(SCRIPT_CREATE_DATABASE);

 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // TODO Auto-generated method stub
     Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
             + newVersion + ", which will destroy all old data");
  db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
  onCreate(db);
 }

----------UPDATE---------------------

It should be sth like:

myItems theItems =(myItems)adapterView.getItemAtPosition(button_id);
String _id = theItems.getID();
String delQuery = "DELETE FROM MEM WHERE _id='"+_id+"' ";
sqlHandler.executeQuery(delQuery);

but I have a problem with the (myItems)adapterView.. I am not sure how to handle this.

George
  • 5,808
  • 15
  • 83
  • 160

2 Answers2

1

I believe the error is here: 

    new DialogInterface.OnClickListener() {
        public void onClick(DialogInterface dialog, int id) { //<--- rename this to buttonId
          sqlHandler=new SqlHandler(getApplicationContext());
          sqlHandler.deleteRecord(id);  //<---- This "id" is the button that was clicked, not your item ID

I suppose that you wanted to use the other id variable, but even then, I'm not sure that's the one you really want either (it might be). From your code I couldn't quickly tell where the real ID is though.

dmon
  • 30,048
  • 8
  • 87
  • 96
  • Beat me to it. Do some simple debugging, what "row id" is to your adapter may certainly vary from your database row id (what exactly it is depends on the adapter). – Charlie Collins Apr 11 '13 at 13:38
  • You're using `button_id`, you need to use `position`: `myItems item =(myItems)adapterView.getItemAtPosition(position);` – dmon Apr 11 '13 at 14:35
  • @dmon:If I use position it says "Cannot refer to a non final...".I am inside "new DialogInterface.OnClickListener() { public void onClick(DialogInterface dialog, int button_id) {" which is inside "public boolean onItemLongClick(AdapterView> adapterView, View view, int position, long id) { final myItems selectedItem = items.get(position);" – George Apr 11 '13 at 16:10
  • Make position final... it means that it can't change at any point later in the scope, which is true. – dmon Apr 11 '13 at 17:11
  • @dmon:No, I can't do the position final "public boolean onItemLongClick(AdapterView> adapterView, View view, int final position, long id) {" .It shows problem then with onItemLongClick method. – George Apr 11 '13 at 19:03
  • It's **final** int position. – dmon Apr 11 '13 at 19:15
  • @dmon:Sorry.It says "Cannot refer to a non-final variable adapterView inside an inner class defined in a different "If I change to "final AdapterView> adapterView" then it doesn't delete the items.. – George Apr 11 '13 at 19:19
  • Ok last one. Mark that one as final. Alternatively you can also declare myItem as final outside of the anonymous class and then only that one needs to be final. – dmon Apr 11 '13 at 20:48
1

the problem you have is the call of your method delete, it should be like this : 

public long deleteItem(myItems itemToDelete) {
    try {
             return sqlDatabase.delete(DATABASE_TABLE,COLUMN_ID + " = ?",new String[]{itemToDelete.getID()});
            }
            catch (Exception e)
            {
                Log.e("DB ERROR", e.toString());
                e.printStackTrace();
                return -1;
            }
}

and when you press the YES Button of AlertDialog to confirm delete you should delete the item from database, and then reload items from database and notify the adapter of your list to refresh it with the new data : 

myItems selectedItem = adapter.getItem(position);
long rows = sqlHandler.deleteItem(selectedItem);
if(rows>0) {
    //get new items from database 
    adapter.setItems(getItemsFromDatabase());
    adapter.notifyDataSetChanged();
}

the item will be deleted from database, and the listView will be refreshed with the new data

Houcine
  • 24,001
  • 13
  • 56
  • 83