delete item from custom listview with sqliteDatabase

Question

Whenever I try to delete using the deletedata method, apps other features work good but while deleting it force stops. I am trying to make an app for my daily expense.

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "users.db";
public static final String TABLE_NAME = "users_data";
public static final String COL1 = "ID";
public static final String COL2 = "FIRSTNAME";
public static final String COL3 = "LASTNAME";
public static final String COL4 = "FAVFOOD";
public static final String TABLE_NAMEA = "receivable";
public static final String COL5 = "ID";
public static final String COL6 = "FIRSTNAME";
public static final String COL7 = "LASTNAME";
public static final String COL8 = "FAVFOOD";

private SQLiteDatabase sqLiteDatabase;

public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

@Override
    public void onCreate(SQLiteDatabase db) {
    String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER 
PRIMARY KEY AUTOINCREMENT, " +
            " FIRSTNAME TEXT, LASTNAME TEXT, FAVFOOD INTEGER)";
    String createTablea = "CREATE TABLE " + TABLE_NAMEA + " (ID INTEGER 
PRIMARY KEY AUTOINCREMENT, " +
            " FIRSTNAME TEXT, LASTNAME TEXT, FAVFOOD INTEGER)";
    db.execSQL(createTable);
    db.execSQL(createTablea);
}

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

public boolean addData(String fName, String lName, int fFood) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL2, fName);
    contentValues.put(COL3, lName);
    contentValues.put(COL4, fFood);

    long result = db.insert(TABLE_NAME, null, contentValues);

    //if date as inserted incorrectly it will return -1
    if (result == -1) {
        return false;
    } else {
        return true;
    }
}

public boolean addDataa(String fName, String lName, int fFood) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL6, fName);
    contentValues.put(COL7, lName);
    contentValues.put(COL8, fFood);

    long result = db.insert(TABLE_NAMEA, null, contentValues);

    //if date as inserted incorrectly it will return -1
    if (result == -1) {
        return false;
    } else {
        return true;
    }
   }

  //query for 1 week repeats
public Cursor getListContents() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor data = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
    return data;
}

public Cursor getListContent() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor data = db.rawQuery("SELECT * FROM " + TABLE_NAMEA, null);
    return data;
}

public int sumofcolumn() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery("SELECT SUM(FAVFOOD) FROM users_data", 
null);
    int total = 0;
    if (cursor.moveToFirst()) {

        total = cursor.getInt(0);
    }

    return total;
}
public int sumofcolumn2() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery("SELECT SUM(FAVFOOD) FROM receivable", 
null);
    int total = 0;
    if (cursor.moveToFirst()) {

        total = cursor.getInt(0);
    }

    return total;
}

public Cursor getItemId(String name){
    SQLiteDatabase db = this.getWritableDatabase();
    String query = 
"SELECT"+COL1+"FROM"+TABLE_NAME+"WHERE"+COL2+"='"+name+"'";
    Cursor data = db.rawQuery(query,null);
    return data;


 }
public void deletedata(int id){
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_NAME,ID+ "=" +id,null);
    db.close();

 }
}`

tab1.java

`public class tab1 extends Fragment {
 public static final String TAG = "tab1";

 DatabaseHelper myDB;
 ArrayList<User> userList;
 ListView listView;
 User user;
 TextView num;



  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setHasOptionsMenu(true);


    myDB = new DatabaseHelper(getActivity());

    userList = new ArrayList<>();
    Cursor data = myDB.getListContents();
    int numRows = data.getCount();
    if (numRows == 0) {
        Toast.makeText(getActivity(), "Click On Red Button To Add new 
     Expence ", Toast.LENGTH_LONG).show();
     } else {
        int i = 0;
        while (data.moveToNext()) {
            user = new User(data.getString(1), data.getString(2), 
     data.getString(3));
            userList.add(i, user);
            System.out.println(data.getString(1) + " " + data.getString(2) + 
    " " + data.getString(3));
            System.out.println(userList.get(i).getFirstName());
            i++;
        }


    }

   @Nullable
   @Override
   public View onCreateView(LayoutInflater inflater, @Nullable ViewGroup 
 container, @Nullable Bundle savedInstanceState) {
    View view = inflater.inflate(R.layout.tab1, container, false);


    final ThreeColumn_ListAdapter adapter = new 
 ThreeColumn_ListAdapter(getActivity(), R.layout.list_adapter_view, 
 userList);
    listView = (ListView) view.findViewById(R.id.listview);
    listView.setAdapter(adapter);
    registerForContextMenu(listView);
    num = (TextView) view.findViewById(R.id.num3);
    myDB = new DatabaseHelper(getActivity());


    int tot = myDB.sumofcolumn();
    num.setText("" + tot);

       listView.setOnItemLongClickListener(new 
  AdapterView.OnItemLongClickListener() {
           @Override
           public boolean onItemLongClick(AdapterView<?> parent, View view, 
 final int position, long id) {

               final int deleteId = position;


               AlertDialog alert = new 
  AlertDialog.Builder(view.getContext())
                       .setTitle("Delete " )
                       .setPositiveButton("Ok",
                        new DialogInterface.OnClickListener() {
                        public voidonClick(DialogInterfacedialog,
                        int whichButton) {

                         myDB.deletedata(position);


                         dialog.dismiss();
                                   }
                               })
                       .setNegativeButton("Cancel",
                        new DialogInterface.OnClickListener() {
                        public void onClick(DialogInterface dialog,
                        int whichButton) {


                                    dialog.cancel();
                                   }
                               }).show();
               return false;
           }
       });

     return view;
 }

 }`

User .java

public class User {
public Integer ID;
public String FirstName;
public String LastName;
public String FavFood;

public User(String fName,String lName, String fFood){

    FirstName = fName;
    LastName = lName;
    FavFood = fFood;
}



public String getFirstName() {
    return FirstName;
}

public void setFirstName(String firstName) {
    FirstName = firstName;
}

public String getLastName() {
    return LastName;
}

public void setLastName(String lastName) {
    LastName = lastName;
}

public String getFavFood() {
    return FavFood;
}

public void setFavFood(String favFood) {
    FavFood = favFood;
}

public Integer getID() {
    return ID;
}
public void setID(Integer ID) {
    this.ID = ID;
}
 }`

i dont know what am i doing wrong. i get stuck at this point.i want to delete data from database when user longclick on selected item.but when i run it, & long click on item, & try to delete, app forced closes.

here is logcat...

12-21 10:09:20.723 1482-1482/? W/EGL_genymotion: eglSurfaceAttrib not implemented

12-21 10:09:23.019 1482-1482/? E/SQLiteLog: (1) no such column: KTU84P

12-21 10:09:23.019 1482-1482/? D/AndroidRuntime: Shutting down VM

12-21 10:09:23.019 1482-1482/? W/dalvikvm: threadid=1: thread exiting with uncaught exception (group=0xa4d22b20)

12-21 10:09:23.023 1482-1482/? E/AndroidRuntime: FATAL EXCEPTION:main

Process: com.scriptit.hsl, PID: 1482

android.database.sqlite.SQLiteException: no such column: KTU84P (code 1): , while compiling: DELETE FROM users_data WHERE KTU84P=0

at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)

at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)

at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)

at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)

at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58)

at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31)

at android.database.sqlite.SQLiteDatabase.delete(SQLiteDatabase.java:1494)

at com.scriptit.hsl.DatabaseHelper.deletedata(DatabaseHelper.java:136)

at com.scriptit.hsl.tab1$1$2.onClick(tab1.java:121)

at android.support.v7.app.AlertController$ButtonHandler.handleMessage(AlertController.java:161)

at android.os.Handler.dispatchMessage(Handler.java:102)

at android.os.Looper.loop(Looper.java:136)

at android.app.ActivityThread.main(ActivityThread.java:5001)

at java.lang.reflect.Method.invokeNative(Native Method)

at java.lang.reflect.Method.invoke(Method.java:515)

at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)

at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)

at dalvik.system.NativeStart.main(Native Method)

12-21 10:09:23.043 595-915/? W/ActivityManager: Force finishing activity com.scriptit.hsl/.MainActivity

Share your error log?

Ramesh sambu
– Ramesh sambu

2017-12-21 14:38:07 +00:00
Commented Dec 21, 2017 at 14:38 — Ramesh sambu
– Ramesh sambu, Commented Dec 21, 2017 at 14:38
Post crash logs

Sachin Chandil
– Sachin Chandil

2017-12-21 14:51:18 +00:00
Commented Dec 21, 2017 at 14:51 — Sachin Chandil
– Sachin Chandil, Commented Dec 21, 2017 at 14:51

Sachin Chandil · Accepted Answer · 2017-12-21 16:22:47Z

1

You have used wrong constant (ID) in where condition. It should be COL1 constant according to your DatabaseHelper.

As your logcat says:

no such column: KTU84P

So you are mistakenly passing id value in place of "ID" constant.

Use following code:

public void deletedata(int id){ 
    SQLiteDatabase db = this.getWritableDatabase(); 
    db.delete(TABLE_NAME,COL1+ "=" +id,null); 
    db.close();      
 }

Hope it helps.

answered Dec 21, 2017 at 16:22

Sachin Chandil

18k8 gold badges49 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

delete item from custom listview with sqliteDatabase

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related