Error inserting data into SQLite

Question

I'm trying to upload some data to an SQLite database, but when I click submit, I continuously receive the "Data not Inserted" Toast message.

I'm unsure why my data isn't being properly passed?

DatabaseHelper.java:

public class DatabaseHelper extends SQLiteOpenHelper{

    public static final String DATABASE_NAME = "streaks.db"; // Name of DB
    public static final String TABLE_NAME = "streak_table";
    public static final String COL_1 = "ID";
    public static final String COL_2 = "STREAKNAME";
    public static final String COL_3 = "STREAKCATEGORY";
    public static final String COL_4 = "DATESTARTED";
    public static final String COL_5 = "DAYSKEPT";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, STREAKNAME TEXT, STREAKCATEGORY TEXT, DATESTARTED TEXT, DAYSKEPT INTEGER)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
        onCreate(db);
    }

    public boolean insertData(String STREAKNAME, String STREAKCATEGORY, String DATESTARTED, int DAYSKEPT){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_2, STREAKNAME);
        contentValues.put(COL_3, STREAKCATEGORY);
        contentValues.put(COL_4, DATESTARTED);
        contentValues.put(COL_5, DAYSKEPT);
        long result = db.insert(TABLE_NAME,null,contentValues);
        if(result == -1){
            return false;
        } else return true;
    }
}

Snippet of MainActivity.java:

// Declaring variables from private variables above
db = new DatabaseHelper(MainActivity.this);

addStreakName = chooseName.getText().toString();
addCategory = prefs.getString("Category", "");
addToday = new SimpleDateFormat("dd-MM-yyyy").format(new Date());

if(prefs.getInt("todayOrChosen", 1) == 1){
    addStartedZero = 0;
    streakList.add(new Streak(addStreakName, addCategory, addToday, addStartedZero));
    boolean isInserted = db.insertData(addStreakName, addCategory, addToday, 0);
    if(isInserted){
        Toast.makeText(MainActivity.this, "Data Inserted", Toast.LENGTH_LONG).show();
    }
    else {
        Toast.makeText(MainActivity.this, "Data not Inserted", Toast.LENGTH_LONG).show();
    }
}

Error Log:

 08-04 17:46:41.409 22835-22835/? E/SQLiteLog: (20) statement aborts at 5: [INSERT INTO streak_table(STREAKNAME,STREAKCATEGORY,DAYSKEPT,DATESTARTED) VALUES (?,?,?,?)] datatype mismatch
08-04 17:46:41.410 22835-22835/? D/AndroidRuntime: Shutting down VM

08-04 17:46:41.413 22835-22835/? E/AndroidRuntime: FATAL EXCEPTION: main
                                               Process: carter.streakly, PID: 22835
                                               android.database.sqlite.SQLiteDatatypeMismatchException: datatype mismatch (code 20)
                                                   at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
                                                   at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:780)
                                                   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:1471)
                                                   at android.database.sqlite.SQLiteDatabase.insertOrThrow(SQLiteDatabase.java:1367)
                                                   at carter.streakly.DatabaseHelper.insertData(DatabaseHelper.java:43)
                                                   at carter.streakly.MainActivity$11.onClick(MainActivity.java:301)
                                                   at android.view.View.performClick(View.java:5198)
                                                   at android.view.View$PerformClick.run(View.java:21147)
                                                   at android.os.Handler.handleCallback(Handler.java:739)
                                                   at android.os.Handler.dispatchMessage(Handler.java:95)
                                                   at android.os.Looper.loop(Looper.java:148)
                                                   at android.app.ActivityThread.main(ActivityThread.java:5417)
                                                   at java.lang.reflect.Method.invoke(Native Method)
                                                   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
                                                   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)

change the insert method to insertOrThrow and get the error log then post the result — Bryan Mills, Aug 04 '16 at 22:04
Did you update the column data type in onCreate() method? If yes try uninstalling the app first — Rohan Arora, Aug 05 '16 at 00:39

score 1 · Accepted Answer · answered Aug 05 '16 at 00:47

1

I just ran your code, created the tables it works fine.

You might have edited the type of one of the columns but the table hasn't been updated. Try deleting the data of the app or uninstall it once and build it again.

answered Aug 05 '16 at 00:47

Rohan Arora

661
5
15

Yeah, I made a few changes and just uninstalled and reinstalled the app and its working fine now. haha – Carter Klein Aug 05 '16 at 00:48
1

Maybe you want to accept the right answer :) @CarterKlein – Rohan Arora Aug 05 '16 at 00:51

score 0 · Answer 2 · answered Aug 04 '16 at 22:13

0

Oh found the error. In your execSQL method where you create your table, you forgot to add the ';' E.G.

db.execSQL("create table " + 
             TABLE_NAME + 
            " (ID INTEGER PRIMARY KEY AUTOINCREMENT, 
               STREAKNAME TEXT,
               STREAKCATEGORY TEXT,  
               DATESTARTED TEXT, 
               DAYSKEPT INTEGER);"); //right here add the semicolon

answered Aug 04 '16 at 22:13

Bryan Mills

115
9

There's no need to comment to tell someone to accept (or upvote) an answer. – OneCricketeer Aug 04 '16 at 22:27
@cricket_007 says who? Obama? – Bryan Mills Aug 05 '16 at 00:06
It works for me - I just had to uninstall and reinstall the app. Thanks! – Carter Klein Aug 05 '16 at 00:48
1

The semicolon wasn't the issue because the execSQL statement automatically appends it. The table schema wasnt properly updated. – Rohan Arora Aug 05 '16 at 00:50
Oh, thank for letting me know :) – Bryan Mills Aug 05 '16 at 01:22

Error inserting data into SQLite

2 Answers2