Insert into SQLite DB fails using ContentValues

Question

When attempting to insert a new entry into a SQLite database in Android, the insert fails due to a syntax error. It seems that the query formed does not contain the values added to the ContentValues instance, but I'm not sure why. Can anyone point me to the error?

Here is the error message:

E/SQLiteLog: (1) near "TEXT": syntax error
E/SQLiteDatabase: Error inserting TIME_DATE TEXT=barbaz TRANSCRIPT TEXT=foobar
android.database.sqlite.SQLiteException: near "TEXT": syntax error (code 1): , while compiling: INSERT INTO approvals_t(TIME_DATE TEXT,TRANSCRIPT TEXT) VALUES (?,?)

And here is the code I'm using to test it:

public boolean insertData(String transStr, String timeStr) {

SQLiteDatabase db = this.getWritableDatabase();

ContentValues cValues = new ContentValues();
cValues.put(TRANS_COL,"foobar");
cValues.put(TIME_COL,"barbaz");

// res is -1, the values couldn't be inserted
long res = db.insert(APVLS_TABLE_NAME,null,cValues);

if(res != -1)
    return true;
else
    return false;
}

(Edit) The rest of the code

public class DBHelper extends SQLiteOpenHelper implements BaseColumns {

public static final String DB_NAME = "Transcripts.db";
public static final String APVLS_TABLE_NAME = "approvals_t";
public static final String ID_COL = BaseColumns._ID;
public static final String TRANS_COL = "TRANSCRIPT TEXT";
public static final String TIME_COL = "TIMEDATE TEXT";

public DBHelper(Context context) {
    super(context, DB_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    // Create a sql command that builds a table
    String SQL_CREATE_APVLS_TABLE = "CREATE TABLE " + APVLS_TABLE_NAME + " ("
            + ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + TRANS_COL + ", "
            + TIME_COL + ");";

    // Execute sql statement
    db.execSQL(SQL_CREATE_APVLS_TABLE);
}

Could you post your create table method and the declaration of the variables? — KoalaKoalified
– KoalaKoalified, Commented Feb 5, 2017 at 20:56
@VictorBrunell The problem is because you are using the constants TRANS_COL and TIME_COL when inserting. And those constants have the TYPE of the field in the table, while inserting should only contains the name of the field, and not name and type. — Jonas452
– Jonas452, Commented Feb 5, 2017 at 21:14

KoalaKoalified · Accepted Answer · 2017-02-05 21:16:19Z

Some changes to your declarations:

public static final String TRANS_COL = "TRANSCRIPT";
public static final String TIME_COL = "TIMEDATE";

To your create table:

String SQL_CREATE_APVLS_TABLE = "CREATE TABLE " + APVLS_TABLE_NAME + " ("
        + ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + TRANS_COL + " TEXT, "
        + TIME_COL + " TEXT);";

The reason its not working is that you have appended TEXT to those variables which makes a field with the names as type TEXT but doesn't include the words " TEXT" at the end of the column name, ergo you can't select from a column named "TRANSCRIPT TEXT".

Also some other changes I would suggest are to change this:

public DBHelper(Context context) {
    super(context, DB_NAME, null, 1);
}

To:

private static final int dbVersion = 1;

public DBHelper(Context context) {
    super(context, DB_NAME, null, dbVersion);
}

which ensures if you ever want to alter your versions, it's just by changing a variable instead of the constructor.

and when testing after backing data up:

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

EDIT:

Also you're going to run into some problems while inserting eventually... make sure your default ID of the class your inserting via is -1 so the ID gets overwritten via the next incrementation and change your insert statement to:

db.insertWithOnConflict(TABLE_NAME, null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);

Then it's just a matter of including your IDs whenever you want to update an entry instead of writing a whole bunch of update statements.

Collectives™ on Stack Overflow

Insert into SQLite DB fails using ContentValues

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related