thing2 0 Newbie Poster

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.