如何更新数据库？

Question

在我的应用程序中我有一个数据库。虽然我可以成功插入表中，但我在更新字段时遇到问题。

此代码创建我的表：

public final static String[] TABLES_NAME = {"general", "toprecipes", "favorites", "shoppinglist"}; 
//-- Columns of table: shopping list 
    public final static String[] CLM_SHOPPINGLIST = {COLUMN_ID, "resepiId", "resepiName", "ingredient", "status"}; 
    private final String CRT_TBL_SHOPPINGLIST = "CREATE TABLE " + TABLES_NAME[3] + " (" + 
     CLM_SHOPPINGLIST[0] + " INTEGER PRIMARY KEY AUTOINCREMENT," + 
     CLM_SHOPPINGLIST[1] + " TEXT," + 
     CLM_SHOPPINGLIST[2] + " TEXT," + 
     CLM_SHOPPINGLIST[3] + " TEXT," + 
     CLM_SHOPPINGLIST[4] + " TEXT" + 
     ");"; 

我需要一个基于事件，从“T”到“F”设定项目的状态，反之亦然。

对于更新数据库中，我有这样的代码：

public void updateRecordStatus(String resepiName, String ingredientName, String status) { 
     ContentValues cv = new ContentValues(); 
     cv.put(DatabaseHelper.CLM_SHOPPINGLIST[4], status); 

     database.update(DatabaseHelper.TABLES_NAME[3], cv, DatabaseHelper.CLM_SHOPPINGLIST[2] + "=" + resepiName + " AND " + DatabaseHelper.CLM_SHOPPINGLIST[3] + "=" + ingredientName, null); 

     Log.i(TAG, "Status of ingredient '" + ingredientName + "' updated to '" + status + "' in table."); 
    } 

看来一切都很好，但我不知道为什么 - 当我运行的应用程序，并要更新，它崩溃。 logcat中显示这些消息，并指出database.update(...)：

08-07 08:08:03.289: I/Database(460): sqlite returned: error code = 1, msg = near "Bakar": syntax error 
08-07 08:08:03.289: E/Database(460): Error updating status=T using UPDATE shoppinglist SET status=? WHERE resepiName=Terung Bakar Mediterrinean AND ingredient=2 biji lemon 
08-07 08:08:03.289: W/dalvikvm(460): threadid=1: thread exiting with uncaught exception (group=0x4001d800) 
08-07 08:08:03.323: E/AndroidRuntime(460): FATAL EXCEPTION: main 
08-07 08:08:03.323: E/AndroidRuntime(460): android.database.sqlite.SQLiteException: near "Bakar": syntax error: , while compiling: UPDATE shoppinglist SET status=? WHERE resepiName=Terung Bakar Mediterrinean AND ingredient=2 biji lemon 
08-07 08:08:03.323: E/AndroidRuntime(460): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method) 
08-07 08:08:03.323: E/AndroidRuntime(460): at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91) 
08-07 08:08:03.323: E/AndroidRuntime(460): at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64) 
08-07 08:08:03.323: E/AndroidRuntime(460): at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80) 
08-07 08:08:03.323: E/AndroidRuntime(460): at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:36) 
08-07 08:08:03.323: E/AndroidRuntime(460): at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1145) 
08-07 08:08:03.323: E/AndroidRuntime(460): at android.database.sqlite.SQLiteDatabase.updateWithOnConflict(SQLiteDatabase.java:1671) 
08-07 08:08:03.323: E/AndroidRuntime(460): at android.database.sqlite.SQLiteDatabase.update(SQLiteDatabase.java:1622) 
08-07 08:08:03.323: E/AndroidRuntime(460): at com.astro.recipe.database.DBShoppingListHandler.updateRecordStatus(DBShoppingListHandler.java:107) 
08-07 08:08:03.323: E/AndroidRuntime(460): at com.astro.recipe.activities.ShoppingList$2.OnCheckChangedListener(ShoppingList.java:90) 

任何意见或建议，将不胜感激。

Answer 1

字符串文字必须用单引号引起来。

例如：

Where you='jox' AND authenticated='1' 

记住，你和认证是列名。

Answer 2

更换

database.update(DatabaseHelper.TABLES_NAME[3], cv, DatabaseHelper.CLM_SHOPPINGLIST[2] + "=" + resepiName + " AND " + DatabaseHelper.CLM_SHOPPINGLIST[3] + "=" + ingredientName, null); 

随着

database.update(DatabaseHelper.TABLES_NAME[3], cv, DatabaseHelper.CLM_SHOPPINGLIST[2] + "= '" + resepiName + "' AND " + DatabaseHelper.CLM_SHOPPINGLIST[3] + "='" + ingredientName + "'", null); 

Answer 3

database.update(DatabaseHelper.TABLES_NAME[3], cv, DatabaseHelper.CLM_SHOPPINGLIST[2] + "='" + resepiName + "' AND " + DatabaseHelper.CLM_SHOPPINGLIST[3] + "='" + ingredientName+"'", null); 

尝试了上述

把单引号的字符串。

Answer 4

变化：

database.update(DatabaseHelper.TABLES_NAME[3], cv, 
    DatabaseHelper.CLM_SHOPPINGLIST[2] + "=" + resepiName + " AND " + 
    DatabaseHelper.CLM_SHOPPINGLIST[3] + "=" + ingredientName, null); 

要：

database.update(DatabaseHelper.TABLES_NAME[3], cv, 
    DatabaseHelper.CLM_SHOPPINGLIST[2] + " = ?" + 
    " AND " + DatabaseHelper.CLM_SHOPPINGLIST[3] + " = ?", 
    new String[]{resepiName, ingredientName}); 

这是使用SQL语句与参数的推荐方式（使用?）