android.database.sqlite.SQLiteDatatypeMismatchException: datatype mismatch (code 20 SQLITE_MISMATCH)

Question

I have an issue with inserting data date into the database. I try to make the ID auto increment and the date is using datepicker. Is that other way can store the date into database or is that others wrong on my code?

Below is dbhelper.java

public class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context) {
    super(context, "Budget.db", null, 1);
}

@Override
public void onCreate(SQLiteDatabase DB) {
    DB.execSQL("create Table Budget(id INTEGER primary key autoincrement not null, date TEXT)");
}

@Override
public void onUpgrade(SQLiteDatabase DB, int i, int i1) {
    DB.execSQL("drop Table if exists Budget");

}

private String getDateTime() {

    SimpleDateFormat dateFormat = new SimpleDateFormat(

            "yyyy-MM-dd ", Locale.getDefault());

    Date date = new Date();

    return dateFormat.format(date);

}
public Boolean insertbudget(String id, String date)
{
    SQLiteDatabase DB = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("id", id);
    contentValues.put("date", date);
    long result=DB.insert("Budget",null, contentValues);
    if(result==-1){
        return false;
    }else{
        return true;
    }
}

public Cursor getdata() {
    SQLiteDatabase DB = this.getWritableDatabase();

    Cursor cursor = DB.rawQuery("Select * from Budget", null);
    return cursor;
}

Below is the code I declare date and database is main

btnAdd = findViewById(R.id.btnAdd);
    id = findViewById(R.id.id);
    DB = new DBHelper(this);

    btnAdd.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            String idTXT = id.getText().toString();
            String dateTXT = mDisplayDate.getText().toString();


            Boolean checkAdddata = DB.insertbudget(idTXT, dateTXT);
            if(checkAdddata==true)
                Toast.makeText(Add.this, "New Entry Inserted", Toast.LENGTH_SHORT);
            else
                Toast.makeText(Add.this, "New Entry Not Inserted", Toast.LENGTH_SHORT);
        }
    });

    //date
    mDisplayDate = (TextView) findViewById(R.id.date);

    mDisplayDate.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            Calendar cal = Calendar.getInstance();
            int year = cal.get(Calendar.YEAR);
            int month = cal.get(Calendar.MONTH);
            int day = cal.get(Calendar.DAY_OF_MONTH);

            DatePickerDialog dialog = new DatePickerDialog(
                    Add.this,
                    android.R.style.Theme_Holo_Light_Dialog_MinWidth,
                    mDateSetListener,
                    year, month, day);

            dialog.getWindow().setBackgroundDrawable(new ColorDrawable(Color.TRANSPARENT));
            dialog.show();
        }
    });
    mDateSetListener = new DatePickerDialog.OnDateSetListener() {
        @Override
        public void onDateSet(DatePicker datePicker, int year, int month, int day) {
            month = month + 1;
            Log.d(TAG, "onDateSet: dd/mm/yyyy:" + day + "/" + month + "/" + year);

            String date = day + "/" + month + "/" + year;
            mDisplayDate.setText(date);
        }

    };

This is the error that show error

user_4744514 · Answer

The datatype mismatch is quite specific you are trying to insert a value that is not an integer value into the rowid column or an alias of the rowid column

INTEGER PRIMARY KEY (with or without AUTOINCREMENT) results in an alias of the rowid column.

According to public Boolean insertbudget(String id, String date) then you are passing a non-integer string.

You can eliminate this from happening by using public Boolean insertbudget(long id, String date) and then adjusting the code to suit (i.e. convert the string to long). The parse error that you will then get, should point to the real cause.

However, the simplest solution, would be to remove the line contentValues.put("id", id); SQLite will then assign an appropriate value. You could then adjust the method's signature as the id does not need to be passed. So you may wish to use :-

public Boolean insertbudget(String date)
{
    SQLiteDatabase DB = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("date", date);
    return result=(DB.insert("Budget",null, contentValues) > 0);
}

android.database.sqlite.SQLiteDatatypeMismatchException: datatype mismatch (code 20 SQLITE_MISMATCH)

1 Answers