error inserting android.database.sqlite.sqlitecons

Question

I know there are so lot of threads about this problem but none of my own problem. I have a spinner connected in the database that displaying the foreign key of the specific table. And it appears error inserting.

02-18 11:44:34.500: E/SQLiteDatabase(20811): Error inserting ConsumerName=android.database.sqlite.SQLiteCursor@4144fa58 kWh=801.0 _id=65324 Date=2013 -2-18 Previous=98 Current=899

02-18 11:44:34.500: E/SQLiteDatabase(20811): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed

public class ElectricMeterReader extends Activity {

    private long rowID;

    private EditText meterNoEt;
    private EditText currentEt;
    private EditText previousEt;
    private EditText kWhEt;
    private TextView dateTv;
    private Spinner spinner;
    private int mSpinnerSpeciesId;

  private ElectricMeterReader mContext;

  @Override
  protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.electric_meter_reader);

    Bundle extras = getIntent().getExtras();

    // rowID = extras.getLong(ConsumerList.ROW_ID);

    dateTv = (TextView) findViewById(R.id.dateEmr);
    meterNoEt = (EditText) findViewById(R.id.meterNumberEmr);
    currentEt = (EditText) findViewById(R.id.currentReadingEmr);
    previousEt = (EditText) findViewById(R.id.passReadingEmr);
    kWhEt = (EditText) findViewById(R.id.kwhEmr);

    spinner = (Spinner) findViewById(R.id.spinner);

    // Loading spinner data from database
    SQLiteDatabase db = new DatabaseOpenHelper(this).getWritableDatabase();

    // Spinner Drop down elements
    // List<String> lables = db.getAllLabels();
    Cursor c = db.rawQuery(
            "SELECT AccountID AS _id, ConsumerName FROM Consumers", null);
    // Creating adapter for spinner
    startManagingCursor(c);
    String[] from = new String[] { "_id" };
    int[] to = new int[] { android.R.id.text1 };
    SimpleCursorAdapter dataAdapter = new SimpleCursorAdapter(this,
            android.R.layout.simple_spinner_item, c, from, to);

    // Drop down layout style - list view with radio button
    dataAdapter
            .setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);

    // attaching data adapter to spinner
    spinner.setAdapter(dataAdapter);
    // set spinner listener to display the selected item id
    mContext = this;

    spinner.setOnItemSelectedListener(new OnItemSelectedListener() {
        public void onItemSelected(AdapterView<?> parent, View view,
                int position, long id) {
            // On selecting a spinner item
            //String label = parent.getItemAtPosition(position).toString();

             Cursor c = (Cursor) parent.getItemAtPosition(position);
              mSpinnerSpeciesId = c.getInt(c
              .getColumnIndexOrThrow("ConsumerName"));


            // Showing selected spinner item

        /*  Toast.makeText(parent.getContext(), "You selected: " + label,
                    Toast.LENGTH_LONG).show();*/

        }

        public void onNothingSelected(AdapterView<?> parent) {
            // TODO Auto-generated method stub
        }
    });

    final Calendar ca = Calendar.getInstance();
    int yy = ca.get(Calendar.YEAR);
    int mm = ca.get(Calendar.MONTH);
    int dd = ca.get(Calendar.DAY_OF_MONTH);
    dateTv.setText(new StringBuilder()
            // Month is 0 based, just add 1
            .append(yy).append(" ").append("-").append(mm + 1).append("-")
            .append(dd));

    if (extras != null) {

        rowID = extras.getLong("_id");
        // meterNoEt.setText(extras.getString("MeterNumber"));
        // set current date into textview
        // Spinner click listener

        currentEt.setText(extras.getString("Current"));
        previousEt.setText(extras.getString("Previous"));
        kWhEt.setText(extras.getString("kWh"));
        // dateTv.setText(extras.getString("Date"));

    }

    Button calButton = (Button) findViewById(R.id.calculateDef);
    calButton.setOnClickListener(new OnClickListener() {

        public void onClick(View v) {
            // TODO Auto-generated method stub
            float result = Float.parseFloat(currentEt.getText().toString())
                    - Float.parseFloat(previousEt.getText().toString());
            kWhEt.setText(Float.toString(result));

        }

    });

    Button saveButton = (Button) findViewById(R.id.addConsumption);

    saveButton.setOnClickListener(new OnClickListener() {

        public void onClick(View v) {
            if (kWhEt.getText().length() != 0) {

                AsyncTask<Object, Object, Object> saveContactTask = new AsyncTask<Object, Object, Object>() {

                    @Override
                    protected Object doInBackground(Object... params) {

                        saveContact();
                        return null;

                    }

                    @Override
                    protected void onPostExecute(Object result) {
                        /*
                         * Intent addContact = new Intent(
                         * ElectricMeterReader.this,
                         * ConsumerReadList.class);
                         * startActivity(addContact);
                         */
                        finish();
                    }
                };

                saveContactTask.execute((Object[]) null);

            }

            else {
                AlertDialog.Builder alert = new AlertDialog.Builder(
                        ElectricMeterReader.this);
                alert.setTitle(R.string.errorTitle);
                alert.setMessage(R.string.errorMessage);
                alert.setPositiveButton(R.string.errorButton, null);
                alert.show();
            }
        }
    });
}



public boolean onCreateOptionsMenu(Menu menu) {
    super.onCreateOptionsMenu(menu);
    MenuInflater inflater = getMenuInflater();
    inflater.inflate(R.menu.consumer_menu, menu);
    return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
    switch (item.getItemId()) {
    case R.id.addConsumerItem:
        Intent addContact = new Intent(ElectricMeterReader.this,
                AddEditConsumer.class);
        startActivity(addContact);
        return true;

    case R.id.consumerInfo:
        Intent electricMeterReader = new Intent(ElectricMeterReader.this,
                ViewConsumer.class);
        startActivity(electricMeterReader);
        return true;

    default:
        return super.onOptionsItemSelected(item);
    }

}

private void saveContact() {

    DatabaseConnector dbConnector = new DatabaseConnector(this);

    if (getIntent().getExtras() == null) {

        dbConnector.insertConsumption(meterNoEt.getText().toString(),
                currentEt.getText().toString(), previousEt.getText()
                        .toString(), kWhEt.getText().toString(), dateTv
                        .getText().toString(),
                spinner.getItemAtPosition(mSpinnerSpeciesId).toString()

        );

    } else {
        dbConnector.updateConsumption(rowID,
                currentEt.getText().toString(), previousEt.getText()
                        .toString(), kWhEt.getText().toString(), dateTv
                        .getText().toString(),
                spinner.getItemAtPosition(mSpinnerSpeciesId).toString());

    }
}

}

Insert method coded into my databaseconnector

    public void insertConsumption(String meter_number, String current,
        String previous, String kWh, String date, String consumer_name) {

    ContentValues newCons = new ContentValues();

    newCons.put("_id", meter_number);
    newCons.put("Current", current);
    newCons.put("Previous", previous);
    newCons.put("kWh", kWh);
    newCons.put("Date", date);
    newCons.put("ConsumerName", consumer_name);

    open();

    database.insert("Consumptions", null, newCons);

    close();
}

My database Openhelper

public class DatabaseOpenHelper extends SQLiteOpenHelper {

    static final String dbName = "ElectricMeterDB";

    static final String tableLabels = "labels";
    static final String consumptionsTable = "Consumptions";
    static final String colMeterNumber = "_id";
    static final String colCurrent = "Current";
    static final String colPrevious = "Previous";
    static final String colkWh = "kWh";
    static final String colDate = "Date";
    static final String colConsumer = "ConsumerName";

    static final String consumersTable = "Consumers";
    static final String colAccountID = "AccountID";
    static final String colName = "ConsumerName";
    static final String colAddress = "Address";

    public static final String viewReads = "ViewReading";

    public DatabaseOpenHelper(Context context) {
        super(context, dbName, null, 2);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL("CREATE TABLE " + consumersTable + " (" + colAccountID
            + " INTEGER PRIMARY KEY NOT NULL," + colName + " TEXT NOT NULL, "
            + colAddress + " TEXT NOT NULL);");

    db.execSQL("CREATE TABLE " + consumptionsTable + " (" + colMeterNumber
            + " INTEGER PRIMARY KEY NOT NULL," + colCurrent + " INTEGER, "
            + colPrevious + " INTEGER, " + colkWh + " INTEGER, " + colDate
            + " TEXT, " + colConsumer + " INTEGER NOT NULL, FOREIGN KEY ("
            + colConsumer + ") REFERENCES " + consumersTable + " ("
            + colAccountID + "));");

    db.execSQL("CREATE TRIGGER fk_cmpcons_consid" + " BEFORE INSERT "
            + " ON " + consumptionsTable + " FOR EACH ROW BEGIN"
            + " SELECT CASE WHEN ((SELECT " + colAccountID + " FROM "
            + consumersTable + " WHERE " + colAccountID + " =new."
            + colConsumer + ") IS NULL)"
            + "THEN RAISE (ABORT, 'Foreign Key Violation') END;" + "END;");

    db.execSQL("CREATE VIEW " + viewReads + " AS SELECT "
            + consumptionsTable + " . " + colMeterNumber + " AS _id, "
            + consumptionsTable + " . " + colCurrent + " ,"
            + consumptionsTable + " . " + colPrevious + " ,"
            + consumptionsTable + " . " + colkWh + ", " + consumptionsTable
            + " . " + colDate + "," + consumersTable + "." + colConsumer
            + " FROM " + consumptionsTable + " JOIN " + consumersTable
            + " ON " + consumptionsTable + " . " + colConsumer + " = "
            + consumersTable + "." + colAccountID);
    // Inserts pre-defined consumers

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL("DROP TABLE IF EXISTS" + consumptionsTable);
    db.execSQL("DROP TABLE IF EXISTS" + consumersTable);

    db.execSQL("DROP TRIGGER IF EXISTS consumers_id_trigger");
    db.execSQL("DROP TRIGGER IF EXISTS consumers_id_trigger22");
    db.execSQL("DROP TRIGGER IF EXISTS fk_cmpcons_consid");
    db.execSQL("DROP VIEW IF EXISTS" + viewReads);
    onCreate(db);
}

public void insertLabel(String label) {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(colName, label);

    // Inserting Row
    db.insert(tableLabels, null, values);
    db.close(); // Closing database connection
}

    /**
     * Getting all labels returns list of labels
     * */
    public List<String> getAllLabels() {

        List<String> labels = new ArrayList<String>();

        // Select All Query
        String selectQuery = "SELECT * FROM " + consumersTable;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                labels.add(cursor.getString(1));
            } while (cursor.moveToNext());
        }

        // closing connection
        cursor.close();
        db.close();

        // returning lables
        return labels;
    }
}

I'm stuck with this problem. Please help me. Thanks for the future response.

log.d

02-18 13:49:59.870: D/saveContact(30881): _id: 36698521 ConsumerName: android.database.sqlite.SQLiteCursor@4144f700

My arrayadapter;

public void arrayAdapterList() {

    final String[] from = new String[] { DatabaseOpenHelper.colAccountID };
    // int[] to = new int[] { android.R.id.text1 };

    ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
            android.R.layout.simple_spinner_item, from);
    adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
    spinner = (Spinner) findViewById(R.id.spinner);
    // attaching data adapter to spinner
    spinner.setAdapter(adapter);

    spinner.setOnItemSelectedListener(new OnItemSelectedListener() {
        public void onItemSelected(AdapterView<?> parent, View view,
                int position, long id) {
            // On selecting a spinner item
            // String col= c.getString(c.getColumnIndex("_id"));
            int item = spinner.getSelectedItemPosition();
            Toast.makeText(getBaseContext(), "You Selected: " + from[item],
                    Toast.LENGTH_SHORT).show();

        }

        public void onNothingSelected(AdapterView<?> parent) {
            // TODO Auto-generated method stub
        }
    });

}

Answer 1

Seems like some values you are inserting may be NULL for NOT NULL fields (ConsumerName in your case) or PK(_id in your case) already exists. Error code 19 means a table constraint (NOT NULL, UNIQUE, etc.) was violated during the operation (INSERT, etc.). Here are the list of SQLITE Error Codes

android.database.sqlite.SQLiteCursor@4144fa58 is junk value assigned to ConsumerName. So please do some workaround to get your proper FK for ConsumerName from cursor.

There is something wrong with you SimpleCursorAdapter. So please look on it. If you are getting correct values in spinner then please get selected value from spineer like this spinner.getSelectedItem().toString(); instead of spinner.getItemAtPosition(mSpinnerSpeciesId).toString().

Hope this helps you.

Answer 2

I got the same error and solved it by setting ID NULL in code. Since ID is PK SqlLite autoincrements its value, it does not need to be set.

public void insertConsumption(String meter_number, String current,
    String previous, String kWh, String date, String consumer_name) {

    ContentValues newCons = new ContentValues();

    //newCons.put("_id", meter_number);
    newCons.put("Current", current);
    newCons.put("Previous", previous);
    newCons.put("kWh", kWh);
    newCons.put("Date", date);
    newCons.put("ConsumerName", consumer_name);

    open();

    database.insert("Consumptions", null, newCons);

    close();
}

Answer 3

you declared the column "colAccountID" as PRIMARY KEY, so the SQLite db throw error while you trying to insert same id again and again.

Answer 4

Constraint failed usually indicates that you did something like pass a null value into a column that you declare as not null when you create your table.

So, Remove not null and place null while creating table.