Trying to insert into my Accounts table through my AddAccount class. The data is collected fine and the thing runs smooth up until after it confirms that the account is added. At which point the operation is aborted due to a Foreign Key Constraint Failure shown here :
LogCat
04-01 22:58:58.750 23053-23053/? E/SQLiteLog﹕ (787) abort at 37 in [INSERT INTO Accounts(Terms,Amount,AccountName,DateCreated,Status,Balance,Purpose,PayPeriod) VALUES (?,?,?,?,?,?,?,?)]: FOREIGN KEY constraint failed
04-01 22:58:58.999 23053-23053/? E/SQLiteDatabase﹕ Error inserting Terms=0 Amount=10000 AccountName=Acc 1 DateCreated=04-01-2015 22:58:58 Status=1 Balance=10000 Purpose=test PayPeriod=1
This was all working fine before, it's a long story but it just popped up after I added an entry into one of the tables Accounts references. After reverting the change to see if the error would appear again it still persisted and from there on I can't seem to fix it. I've tried adding ON DELETE CASCADE since it first popped up when I upgraded my database to register the additional entry into my Status and it did but then popped up now when I tried inserting an entry into my Accounts.
I've pulled my dbfile and opened it to check if the tables Accounts referenced we're populating properly and they are. Now I'm lost and don't know what I should be looking for, if anyone can point me in the direction of my mistake it would be greatly appreciated.
Database Helper
onCreate
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + termsTable + " (" + colTermsID + " INTEGER PRIMARY KEY , " + colTermsClass + " TEXT)");
db.execSQL("CREATE TABLE " + periodTable + " (" + colPeriodID + " INTEGER PRIMARY KEY , " + colPeriodClass + " TEXT)");
db.execSQL("CREATE TABLE " + statTable + " (" + colStatusID + " INTEGER PRIMARY KEY , " + colStatClass + " TEXT)");
db.execSQL("CREATE TABLE " + accountsTable + " (" + colID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
colName + " TEXT, " +
colAmount + " Integer, " +
colPurpose + " TEXT, " +
colTerms + " INTEGER NOT NULL, " +
colPeriod +" INTEGER NOT NULL, " +
colBalance +" INTEGER, "+
colStatus + " INTEGER DEFAULT '1'," +
colDate + " TEXT, " +
colEditDate + " TEXT, " +
"FOREIGN KEY (" + colTerms + ") REFERENCES " + termsTable + " (" + colTermsID + ") " + "," +
"FOREIGN KEY (" + colPeriod + ") REFERENCES " + periodTable + " (" + colPeriodID + ") " + "," +
"FOREIGN KEY (" + colStatus + ") REFERENCES " + statTable + " (" + colStatusID + "));");
db.execSQL("CREATE TABLE " + payTable + " (" + colPayID + " INTEGER PRIMARY KEY , " +
colGroupID + " INTEGER NOT NULL, " +
colPayBal + " TEXT, " +
colInterest + " TEXT, " +
colPayDue + " TEXT, " +
colDateDue + " TEXT, " +
colPaid + " Integer, " +
"FOREIGN KEY (" + colGroupID + ") REFERENCES " + accountsTable + " (" + colID + ") ON DELETE CASCADE);");
db.execSQL("CREATE VIEW " + viewAccs +
" AS SELECT " + accountsTable + "." + colID + " AS _id," +
" " + accountsTable + "." + colName + "," +
" " + accountsTable + "." + colAmount + "," +
" " + accountsTable + "." + colPurpose + "," +
" " + termsTable + "." + colTermsClass + "," +
" " + periodTable + "." + colPeriodClass + "," +
" " + accountsTable+ "." + colBalance + "," +
" " + statTable + "." + colStatClass + "," +
" " + accountsTable + "." + colDate + "," +
" " + accountsTable + "." + colEditDate + "" +
" FROM " + accountsTable +
" JOIN " + termsTable + " ON " + accountsTable + "." + colTerms + " = " + termsTable + "." + colTermsID +
" JOIN " + periodTable + " ON " + accountsTable + "." + colPeriod + " = " + periodTable + "." + colPeriodID +
" JOIN " + statTable + " ON " + accountsTable + "." + colStatus + " = " + statTable + "." + colStatusID );
db.execSQL("CREATE VIEW " + viewPmnts +
" AS SELECT " + payTable + "." + colPayID + " AS _id," +
" " + accountsTable + "." + colID + "," +
" " + payTable + "." + colGroupID + "," +
" " + payTable + "." + colPayBal + "," +
" " + payTable + "." + colInterest + "," +
" " + payTable + "." + colPayDue + "," +
" " + payTable + "." + colDateDue + "," +
" " + payTable + "." + colPaid + "" +
" FROM " + payTable +
" JOIN " + accountsTable + " ON " + payTable + "." + colGroupID + " = " + accountsTable + "." + colID );
InsertTerms(db);
InsertPeriods(db);
InsertStatus(db);
}
AddAccount ( method within DatabaseHelper )
void AddAccount(Account acc) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(colName, acc.getName());
cv.put(colAmount, acc.getAmt());
cv.put(colPurpose, acc.getPurpose());
cv.put(colTerms, acc.getTerms());
cv.put(colPeriod, acc.getPeriod());
cv.put(colBalance, acc.getAmt());
cv.put(colDate, acc.getDate());
cv.put(colStatus, acc.getStatus());
db.insert(accountsTable, colName, cv);
db.close();
}
AddAccount ( actual class )
public class AddAccount extends Activity {
@Nullable
@Override
public ActionBar getActionBar() {
return super.getActionBar();
}
EditText txtName;
EditText txtAmount;
EditText txtPurpose;
DatabaseHelper dbHelper;
Spinner spinTerm;
Spinner spinPeriod;
Spinner spinStat;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.addaccount);
txtName = (EditText) findViewById(R.id.txtName);
txtAmount = (EditText) findViewById(R.id.txtAmt);
txtPurpose = (EditText) findViewById(R.id.txtPurpose);
spinTerm = (Spinner) findViewById(R.id.spinTerm);
spinPeriod = (Spinner) findViewById(R.id.spinPeriod);
spinStat = (Spinner) findViewById(R.id.spinStat);
spinTerm.setVisibility(View.GONE);
spinStat.setVisibility(View.GONE);
}
@Override
public void onStart() {
try {
super.onStart();
dbHelper = new DatabaseHelper(this);
Cursor c = dbHelper.getAllTerms();
startManagingCursor(c);
SimpleCursorAdapter ca = new SimpleCursorAdapter(this, R.layout.termspinnerrow, c, new String[]{DatabaseHelper.colTermsClass, "_id"}, new int[]{R.id.txtTermClass});
spinTerm.setAdapter(ca);
spinTerm.setOnItemSelectedListener(new OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View selectedView, int position, long id) {
}
@Override
public void onNothingSelected(AdapterView<?> arg0) {
}
});
Cursor d = dbHelper.getAllStatus();
SimpleCursorAdapter da = new SimpleCursorAdapter(this, R.layout.statspinnerrow, d, new String[]{DatabaseHelper.colStatClass, "_id"}, new int[]{R.id.txtStatClass});
spinStat.setAdapter(da);
spinStat.setOnItemSelectedListener(new OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
});
Cursor e = dbHelper.getAllPeriods();
SimpleCursorAdapter ea = new SimpleCursorAdapter(this, R.layout.periodspinnerrow, e, new String[]{DatabaseHelper.colPeriodClass, "_id"}, new int[]{R.id.txtPeriodClass});
spinPeriod.setAdapter(ea);
spinPeriod.setOnItemSelectedListener(new OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
});
//never close cursor
} catch (Exception ex) {
CatchError(ex.toString());
}
}
public void btnAddEmp_Click(View view) {
boolean ok = true;
try {
Spannable spn = txtAmount.getText();
String name = txtName.getText().toString();
int amount = Integer.valueOf(spn.toString());
String purpose = txtPurpose.getText().toString();
int balance = 0;
int termID = Integer.valueOf((int) spinTerm.getSelectedItemId());
int periodID = Integer.valueOf((int) spinPeriod.getSelectedItemId());
String date = Account.getDate();
String editdate = Account.getEditDate();
int statID = Integer.valueOf((int) spinStat.getSelectedItemId());
Account acc = new Account(name, amount, purpose, balance, termID, periodID, date, editdate, statID);
dbHelper.AddAccount(acc);
Intent i = new Intent();
setResult(0, i);
} catch (Exception ex) {
ok = false;
CatchError(ex.toString());
} finally {
if (ok) {
Alerts.ShowAccAddedAlert(this);
}
}
finish();
}
void CatchError(String Exception) {
Dialog diag = new Dialog(this);
diag.setTitle("Account creation");
TextView txt = new TextView(this);
txt.setText(Exception);
diag.setContentView(txt);
diag.show();
}
}
Aucun commentaire:
Enregistrer un commentaire