使用ContentValues插入SQLite DB失败

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?

尝试在Android中的SQLite数据库中插入新条目时,插入因语法错误而失败。似乎所形成的查询不包含添加到ContentValues实例的值,但我不确定原因。谁能指出我的错误?

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

1 个解决方案

#1

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".

它不起作用的原因是你已经将TEXT附加到那些变量,这些变量使得一个字段的名称为TEXT类型但在列名末尾不包含单词“TEXT”,你不能从中选择列名为“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:

你也将碰到一些问题,同时将最终...确保您的默认ID之类的你插入通过为-1,所以ID被通过下一级增量覆盖,改变你的INSERT语句:

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.

然后,只需要在需要更新条目时包含ID,而不是编写一大堆更新语句。

#1