Victor Brunell Victor Brunell - 4 years ago 127
Android Question

Insert into SQLite DB fails using ContentValues

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);
}

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download