Hello I modified tutorial code found at http://www.codeproject.com/Articles/119293/Using-SQLite-Database-with-Android?fid=1592519&df=90&mpp=25&noise=3&prof=False&sort=Position&view=Quick&spc=Relaxed&fr=1#xx0xx
to create an assignment list app, and when I click modify, delete, or cancel it force closes.
Here is my DatabaseHelper.java code:
public class DatabaseHelper extends SQLiteOpenHelper {
static final String dbName="assignDB";
static final String employeeTable="Assignments";
static final String colID="assignID";
static final String colName="assignName";
static final String colAge="dueDate";
static final String colDept="Course";
static final String deptTable="Course";
static final String colDeptID="CourseID";
static final String colDeptName="CourseName";
static final String viewEmps="ViewAssignments";
public DatabaseHelper(Context context) {
super(context, dbName, null,33);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE "+deptTable+" ("+colDeptID+ " INTEGER PRIMARY KEY , "+
colDeptName+ " TEXT)");
db.execSQL("CREATE TABLE "+employeeTable+" ("+colID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
colName+" TEXT, "+colAge+" TEXT, "+colDept+" INTEGER NOT NULL ,FOREIGN KEY ("+colDept+") REFERENCES "+deptTable+" ("+colDeptID+"));");
db.execSQL("CREATE TRIGGER fk_empdept_deptid " +
" BEFORE INSERT "+
" ON "+employeeTable+
" FOR EACH ROW BEGIN"+
" SELECT CASE WHEN ((SELECT "+colDeptID+" FROM "+deptTable+" WHERE "+colDeptID+"=new."+colDept+" ) IS NULL)"+
" THEN RAISE (ABORT,'Foreign Key Violation') END;"+
" END;");
db.execSQL("CREATE VIEW "+viewEmps+
" AS SELECT "+employeeTable+"."+colID+" AS _id,"+
" "+employeeTable+"."+colName+","+
" "+employeeTable+"."+colAge+","+
" "+deptTable+"."+colDeptName+""+
" FROM "+employeeTable+" JOIN "+deptTable+
" ON "+employeeTable+"."+colDept+" ="+deptTable+"."+colDeptID
);
//Inserts pre-defined departments
InsertDepts(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS "+employeeTable);
db.execSQL("DROP TABLE IF EXISTS "+deptTable);
db.execSQL("DROP TRIGGER IF EXISTS dept_id_trigger");
db.execSQL("DROP TRIGGER IF EXISTS dept_id_trigger22");
db.execSQL("DROP TRIGGER IF EXISTS fk_empdept_deptid");
db.execSQL("DROP VIEW IF EXISTS "+viewEmps);
onCreate(db);
}
void AddEmployee(Employee emp)
{
SQLiteDatabase db= this.getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(colName, emp.getName());
cv.put(colAge, emp.getAge());
cv.put(colDept, emp.getDept());
//cv.put(colDept,2);
db.insert(employeeTable, colName, cv);
db.close();
}
int getEmployeeCount()
{
SQLiteDatabase db=this.getWritableDatabase();
Cursor cur= db.rawQuery("Select * from "+employeeTable, null);
int x= cur.getCount();
cur.close();
return x;
}
Cursor getAllEmployees()
{
SQLiteDatabase db=this.getWritableDatabase();
//Cursor cur= db.rawQuery("Select "+colID+" as _id , "+colName+", "+colAge+" from "+employeeTable, new String [] {});
Cursor cur= db.rawQuery("SELECT * FROM "+viewEmps,null);
return cur;
}
Cursor getAllDepts()
{
SQLiteDatabase db=this.getReadableDatabase();
Cursor cur=db.rawQuery("SELECT "+colDeptID+" as _id, "+colDeptName+" from "+deptTable,new String [] {});
return cur;
}
void InsertDepts(SQLiteDatabase db)
{
ContentValues cv=new ContentValues();
cv.put(colDeptID, 1);
cv.put(colDeptName, "English");
db.insert(deptTable, colDeptID, cv);
cv.put(colDeptID, 2);
cv.put(colDeptName, "Math");
db.insert(deptTable, colDeptID, cv);
cv.put(colDeptID, 3);
cv.put(colDeptName, "Social Studies");
db.insert(deptTable, colDeptID, cv);
cv.put(colDeptID, 4);
cv.put(colDeptName, "History");
db.insert(deptTable, colDeptID, cv);
cv.put(colDeptID, 5);
cv.put(colDeptName, "Computer Science");
db.insert(deptTable, colDeptID, cv);
cv.put(colDeptID, 6);
cv.put(colDeptName, "Biology");
db.insert(deptTable, colDeptID, cv);
cv.put(colDeptID, 7);
cv.put(colDeptName, "Chemistry");
db.insert(deptTable, colDeptID, cv);
cv.put(colDeptID, 8);
cv.put(colDeptName, "Social Studies");
db.insert(deptTable, colDeptID, cv);
cv.put(colDeptID, 9);
cv.put(colDeptName, "Geography");
db.insert(deptTable, colDeptID, cv);
cv.put(colDeptID, 10);
cv.put(colDeptName, "Foreign Language");
db.insert(deptTable, colDeptID, cv);
db.insert(deptTable, colDeptID, cv);
}
public String GetDept(int ID)
{
SQLiteDatabase db=this.getReadableDatabase();
String[] params=new String[]{String.valueOf(ID)};
Cursor c=db.rawQuery("SELECT "+colDeptName+" FROM"+ deptTable+" WHERE "+colDeptID+"=?",params);
c.moveToFirst();
int index= c.getColumnIndex(colDeptName);
return c.getString(index);
}
public Cursor getEmpByDept(String Dept)
{
SQLiteDatabase db=this.getReadableDatabase();
String [] columns=new String[]{"_id",colName,colAge,colDeptName};
Cursor c=db.query(viewEmps, columns, colDeptName+"=?", new String[]{Dept}, null, null, null);
return c;
}
public int GetDeptID(String Dept)
{
SQLiteDatabase db=this.getReadableDatabase();
Cursor c=db.query(deptTable, new String[]{colDeptID+" as _id",colDeptName},colDeptName+"=?", new String[]{Dept}, null, null, null);
//Cursor c=db.rawQuery("SELECT "+colDeptID+" as _id FROM "+deptTable+" WHERE "+colDeptName+"=?", new String []{Dept});
c.moveToFirst();
return c.getInt(c.getColumnIndex("_id"));
}
public int UpdateEmp(Employee emp)
{
SQLiteDatabase db=this.getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(colName, emp.getName());
cv.put(colAge, emp.getAge());
cv.put(colDept, emp.getDept());
return db.update(employeeTable, cv, colID+"=?", new String []{String.valueOf(emp.getID())});
}
public void DeleteEmp(Employee emp)
{
SQLiteDatabase db=this.getWritableDatabase();
db.delete(employeeTable,colID+"=?", new String [] {String.valueOf(emp.getID())});
db.close();
}
}
And the code for the alert incase it is helpful
public class Alerts {
public static void ShowEmpAddedAlert(Context con)
{
AlertDialog.Builder builder=new AlertDialog.Builder(con);
builder.setTitle("Add new Assignment");
builder.setIcon(android.R.drawable.ic_dialog_info);
DialogListner listner=new DialogListner();
builder.setMessage("Assignment Added successfully");
builder.setPositiveButton("ok", listner);
AlertDialog diag=builder.create();
diag.show();
}
public static AlertDialog ShowEditDialog(final Context con,final Employee emp)
{
AlertDialog.Builder b=new AlertDialog.Builder(con);
b.setTitle("Assignment Details");
LayoutInflater li=LayoutInflater.from(con);
View v=li.inflate(R.layout.editdialog, null);
b.setIcon(android.R.drawable.ic_input_get);
b.setView(v);
final TextView txtName=(TextView)v.findViewById(R.id.txtDelName);
final TextView txtAge=(TextView)v.findViewById(R.id.txtDelAge);
final Spinner spin=(Spinner)v.findViewById(R.id.spinDiagDept);
Utilities.ManageDeptSpinner(con, spin);
for(int i=0;i<spin.getCount();i++)
{
long id=spin.getItemIdAtPosition(i);
if(id==emp.getDept())
{
spin.setSelection(i, true);
break;
}
}
txtName.setText(emp.getName());
txtAge.setText(String.valueOf(emp.getAge()));
b.setPositiveButton("Modify", new OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
// TODO Auto-generated method stub
emp.setName(txtName.getText().toString());
emp.setAge(Integer.valueOf(txtAge.getText().toString()));
emp.setDept((int)spin.getItemIdAtPosition(spin.getSelectedItemPosition()));
try
{
DatabaseHelper db=new DatabaseHelper(con);
db.UpdateEmp(emp);
}
catch(Exception ex)
{
CatchError(con, ex.toString());
}
}
});
b.setNeutralButton("Delete", new OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
// TODO Auto-generated method stub
DatabaseHelper db=new DatabaseHelper(con);
db.DeleteEmp(emp);
}
});
b.setNegativeButton("Cancel", null);
return b.create();
//diag.show();
}
}
`
Any ideas? Any help would be appreciated, thanks!