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

Question

my problem is about my database inserting statement. i added a parameter to te inserting statement, and now it gives me this error below every time i try to add a person. i have already changed the database version , but still it gives me an "data mismatch" error. i added this paramter to the inserting statement beaucaus it didn't saved the ID ( String) and every time i logged out, the id i made (username+password) changed in an integer.. so i guess my database thinks that id an integer is but i want it as a String. i actually think that every type is right.

Here is my code , thanks for helping!

Database:

public class DatabaseHelper extends SQLiteOpenHelper {

private static DatabaseHelper instance;

//Database Versie
public static final int database_version = 3;

//Database Naam
public static final String DATABASE_NAME = "Biercafevlaanderen.db";

//Tabel Namen
public static final String TABLE_NAME_PROFIELEN = "profielen";
public static final String TABLE_NAME_BIERCAFES = "biercafes";

//Kollom namen
// voor tabel profiles
public static String COL_1_PROFIELEN= "profile_id";
public static final String COL_2_PROFIELEN= "username";
public static final String COL_3_PROFIELEN = "firstname";
public static final String COL_4_PROFIELEN = "email";
public static final String COL_5_PROFIELEN = "password";

//voor tabel
public static final String COL_1_BIERCAFES = "biercafe_id";
public static final String COL_2_BIERCAFES = "biercafe_name";
public static final String COL_3_BIERCAFES = "biercafe_postcode";
public static final String COL_4_BIERCAFES = "biercafe_plaats";
public static final String COL_5_BIERCAFES = "biercafe_gemeente";
public static final String COL_6_BIERCAFES = "biercafe_omschrijving";
public static final String COL_7_BIERCAFES = "biercafe_phone";
public static final String COL_8_BIERCAFES = "biercafe_website";
public static final String COL_9_BIERCAFES = "biercafe_email";
public static final String COL_10_BIERCAFES = "profielid";


public static synchronized DatabaseHelper getInstance(Context context) {
    if (instance == null) {
        instance = new DatabaseHelper(context.getApplicationContext());
    }
    return instance;
}

public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, database_version);
    SQLiteDatabase db = this.getWritableDatabase();
}

//HERE FALLS THE ERROR
public int insertProfiel (String id,String username, String password) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL_1_PROFIELEN,id);
    contentValues.put(COL_2_PROFIELEN, username);
    contentValues.put(COL_5_PROFIELEN, password);

    // de insert methode geeft -1 terug als het niet gelukt is en de row value als het wel gelukt is
    long result = db.insert(TABLE_NAME_PROFIELEN, null, contentValues);

    return (int)result;
}
@Override
public void onCreate(SQLiteDatabase db) {
    // de tabel creëren voor de profielen
    db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_PROFIELEN + " (" +     COL_1_PROFIELEN + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COL_2_PROFIELEN + " VARCHAR(255), " + COL_3_PROFIELEN + " VARCHAR(255), " + COL_4_PROFIELEN + " VARCHAR(255), " + COL_5_PROFIELEN + " VARCHAR(255))");

    // de tabel creëren voor de locaties met foreign key profile_id
    db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_BIERCAFES + " (" + COL_1_BIERCAFES + " INTEGER PRIMARY KEY, " + COL_2_BIERCAFES + " VARCHAR(255), " + COL_3_BIERCAFES + " VARCHAR(255), " + COL_4_BIERCAFES + " VARCHAR(255), " + COL_5_BIERCAFES + " VARCHAR(255), " + COL_6_BIERCAFES +
            " VARCHAR(255), " + COL_7_BIERCAFES + " VARCHAR(255), " + COL_8_BIERCAFES + " VARCHAR(255), " + COL_9_BIERCAFES + " VARCHAR(255), " + COL_10_BIERCAFES + " VARCHAR(255), FOREIGN KEY (" + COL_10_BIERCAFES + ") REFERENCES " + TABLE_NAME_PROFIELEN + "(" + COL_1_PROFIELEN + "))");

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_PROFIELEN);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_BIERCAFES);
    onCreate(db);
}
}

this is where i insert the user in the db Registration:

Profiel p = new Profiel(etUsername.getText().toString()+""+etPassword.getText().toString(),etUsername.getText().toString(),"","",etConfPass.getText().toString());
                                mListener.getMyDB().insertProfiel(p.getId(),p.getUsername(), p.getPassword());
                                System.out.println(mListener.getMyDB().insertProfiel(p.getId(),p.getUsername(), p.getPassword()));
                                Intent intent = new Intent(getActivity(), MainActivity.class);
                                intent.putExtra("profiel", p.toJson());
                                Snackbar.make(v, "Registered succesfully", Snackbar.LENGTH_LONG)
                                        .setAction("Action", null).show();
                                startActivity(intent);
                                getActivity().finish();

if you need other relavent classes, ask me. this is the error:

E/SQLiteLog: (20) statement aborts at 5: [INSERT INTO profielen(password,username,profile_id) VALUES (?,?,?)] datatype mismatch
E/SQLiteDatabase: Error inserting password=zzz username=zzz profile_id=zzzzzz
              android.database.sqlite.SQLiteDatatypeMismatchException: datatype mismatch (code 20)
                  at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
                  at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
                  at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
                  at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
                  at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1474)
                  at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1343)
                  at com.example.cedri.bcv.DB.DatabaseHelper.insertProfiel(DatabaseHelper.java:144)
                  at com.example.cedri.bcv.Fragments.RegisterFragment$2.onClick(RegisterFragment.java:66)
                  at android.view.View.performClick(View.java:5637)
                  at android.view.View$PerformClick.run(View.java:22429)
                  at android.os.Handler.handleCallback(Handler.java:751)
                  at android.os.Handler.dispatchMessage(Handler.java:95)
                  at android.os.Looper.loop(Looper.java:154)
                  at android.app.ActivityThread.main(ActivityThread.java:6121)
                  at java.lang.reflect.Method.invoke(Native Method)
                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:889)
                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:779)

the insertstatement gives me this:

I/System.out: -1

Thanks for help!

Florent · Accepted Answer · 2017-06-03 10:33:36Z

1

public int insertProfiel (String id,String username, String password) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues contentValues = new ContentValues(); contentValues.put(COL_1_PROFIELEN,id); contentValues.put(COL_2_PROFIELEN, username); contentValues.put(COL_5_PROFIELEN, password); //rest of the code }

id shouldn't be String, but Integer instead. That's why you are getting this error, because when you created your table PROFILIEN, you declared COL_1_PROFILIEN (profilien_id) as an INTEGER.

db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_PROFIELEN + " (" + COL_1_PROFIELEN + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COL_2_PROFIELEN + " VARCHAR(255), " + COL_3_PROFIELEN + " VARCHAR(255), " + COL_4_PROFIELEN + " VARCHAR(255), " + COL_5_PROFIELEN + " VARCHAR(255))");

answered Jun 3, 2017 at 10:33

Florent

1,7751 gold badge15 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Peter Over a year ago

But i want it as a String, because my id= username+password , this combi is a string, where do i say that id must be an int?

Florent Over a year ago

I believe you should leave id AS IS (INTEGER PRIMARY KEY AUTOINCREMENT), because that's not a good practise what you are trying to do. You are trying to make redundant data on you database, because there is no need to put two fields in another one and still keep all of them.

Peter Over a year ago

Thank you alot! i changed the type indeed of my first column and now it words :)!!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related