John Harding John Harding - 4 years ago 101
Android Question

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)

Answer Source

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);

to

 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download