Cannot insert values into SQLite database in android

Question

I have three classes. One is an activity which sends the text of EditTexts to another class:

String[] comments = new String[]{ number.getText().toString(),
                  vessel_name.getText().toString(), ... } //20 such values
comment = datasource.createComment(comments);

This is the class which handles the comments:

private String[] allColumns = { MySQLiteHelper.COLUMN_ID,
        MySQLiteHelper.COLUMN_IMO_NUMBER,
        MySQLiteHelper.COLUMN_VESSEL_NAME,...} //21 such values

public VesproModel createComment(String comment[]) {
    long insertId = 0;
    int i = 1, j = 0;
    Cursor cursor = null;
    ContentValues values = new ContentValues();
    values.put(allColumns[0],insertId);
    for (; i < allColumns.length && j < comment.length ; i++ , j++) {
        values.put(allColumns[i], comment[j]);
        insertId = database.insert(MySQLiteHelper.TABLE_TPCS_VESSEL, null, values);
        cursor = database.query(MySQLiteHelper.TABLE_TPCS_VESSEL, allColumns, 
                MySQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null);
    }

    cursor.moveToFirst();
    VesproModel newComment = cursorToComment(cursor);
    cursor.close();
    return newComment;
}


private VesproModel cursorToComment(Cursor cursor) {
    VesproModel comment = new VesproModel();
    Log.d("cursorToComment", "Before if cursorToComment");
    if( cursor != null && cursor.moveToFirst() ){
        Log.d("cursorToComment", "inside cursorToComment");
    comment.setId(cursor.getLong(0));
    comment.setImo_number(cursor.getString(1));
    comment.setVessel_name(cursor.getString(2));
    comment.setVessel_type(cursor.getString(3));
        ...
        ...
        comment.setNo_engines(cursor.getInt(20));
    }
    else Log.d("cursorToComment", "NULL evaluation");
    return comment;
  }

VesproModel contains variables and their getters and setters. MySQLiteHelper class exxtends the SQLiteOpenHelper class which handles the query of creating the table named TPC_VESSEL. Some error messages that logcat shows:

06-21 10:45:03.826: E/SQLiteDatabase(24135): Error inserting _id=0 imo_number=YB
06-21 10:45:03.826: E/SQLiteDatabase(24135): android.database.sqlite.SQLiteConstraintException: TPCS_VESSEl.vessel_name may not be NULL (code 19)
06-21 10:45:03.826: E/SQLiteDatabase(24135):    at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
06-21 10:45:03.826: E/SQLiteDatabase(24135):    at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:775)
06-21 10:45:03.826: E/SQLiteDatabase(24135):    at com.example.pcs.VesproDataSource.createComment(VesproDataSource.java:60)
06-21 10:45:03.846: E/SQLiteDatabase(24135): Error inserting _id=0 imo_number=YB vessel_name=ego
06-21 10:45:03.846: E/SQLiteDatabase(24135): android.database.sqlite.SQLiteConstraintException: TPCS_VESSEl.vessel_type may not be NULL (code 19)
06-21 10:45:03.846: E/SQLiteDatabase(24135):    at com.example.pcs.VesproDataSource.createComment(VesproDataSource.java:60)
06-21 10:45:03.856: E/SQLiteDatabase(24135): Error inserting _id=0 vessel_type=Container imo_number=YB vessel_name=ego

Edit 1: the create table command in MySQLiteHelper class:

database.execSQL("create table "
            + TABLE_TPCS_VESSEL
            + "("+ COLUMN_ID + " integer primary key autoincrement, " +
                   COLUMN_IMO_NUMBER +" text not null, " +
                   COLUMN_VESSEL_NAME + " text not null, " +
                   COLUMN_VESSEL_TYPE +  " text not null, " +
                   COLUMN_SR_CERTIFICATE_NO +" text not null," +
                               ...
                              ....
                              COLUMN_NO_ENGINES + " integer not null);");

score 3 · Answer 1 · answered Jun 21 '13 at 05:56

3

The logcat says it all

TPCS_VESSEl.vessel_name can not be null. It seems your have declare it as not null when creating. So better set a default value. .Or while inserting put a value to this variable

answered Jun 21 '13 at 05:56

stinepike

54,068
14
92
112

actually I'm sending the value for vessel_name from an editText. There are other errors also like this , telling me that other fields may not be null also. – no10downingstreet Jun 21 '13 at 06:08

score 0 · Answer 2 · answered Jun 21 '13 at 06:02

0

As per your logcat, you are inserting null value of vessel_name into database. and you set NOT NULL in for vessel_name in your table. so better to insert values in that OR set default value for that column.

answered Jun 21 '13 at 06:02

Sagar Maiyad

12,655
9
63
99

score 0 · Answer 3 · answered Jun 21 '13 at 10:11

Okay so I found the solution. I moved the database.insert and database.query commands outside of the for loop like so :

for (; i < allColumns.length && j < comment.length ; i++ , j++) {
        values.put(allColumns[i], comment[j]);
         } 

insertId = database.insert(MySQLiteHelper.TABLE_TPCS_VESSEL, null, values);
cursor = database.query(MySQLiteHelper.TABLE_TPCS_VESSEL, allColumns, 
        MySQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null);

I also commented out this line : `

values.put(allColumns[0],insertId);

as insertId was set to autoincrement. `

Cannot insert values into SQLite database in android

3 Answers3