android- error with creating database

Question

Below is the code i am using..and also the error it has displayed.. I have gone through each line of code, but i couldnt figure it out.. please help me..

public class CenterCollectionDatabase extends Activity {

public static final String KEY_ID = "id";
public static final String KEY_ROUNDID = "round";
public static final String KEY_PLAYERID = "playerid";
public static final String KEY_MAAL = "maal";
public static final String KEY_POINT = "point";
public static final String KEY_WON = "won";
private static final String DATABASE_NAME ="center_collection";
private static final String DATABASE_TABLE= "transaction";
private static final int DATABASE_VERSION = 1;
private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;

private static class DbHelper extends SQLiteOpenHelper{

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

        db.execSQL("CREATE TABLE " + DATABASE_TABLE +" ("+
                KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "+
                KEY_ROUNDID + " INTEGER, "+
                KEY_PLAYERID + " INTEGER, "+
                KEY_POINT + " INTEGER, "+
                KEY_MAAL + " INTEGER, "+
                KEY_WON+ " INTEGER );"          
            );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);
    }

}

public CenterCollectionDatabase(Context c)
{
    ourContext = c;
}

public CenterCollectionDatabase open() throws SQLException{
    ourHelper = new DbHelper(ourContext);
    ourDatabase = ourHelper.getWritableDatabase();
    return this;

}
public void close()
{
    ourHelper.close();
}

public long add_score(int round, int playerid, int point, int maal, int won) {

    ContentValues cv = new ContentValues();
    cv.put(KEY_ROUNDID, round);
    cv.put(KEY_PLAYERID, playerid);
    cv.put(KEY_POINT, point);
    cv.put(KEY_MAAL, maal);
    cv.put(KEY_WON, won);

    return ourDatabase.insert(DATABASE_TABLE, null, cv);
}
   }

below is the code used to save data in database

if(counter>0)
            {
                Toast.makeText(getApplicationContext(), "Oops Mike! please fill all fields", Toast.LENGTH_SHORT).show();
            }else{

                CenterCollectionDatabase db = new CenterCollectionDatabase(ScoreEntry.this);
                db.open();

                for(int i=0; i<number_of_player; i++)
                {
                    int round = 1;
                    int playerid = i;
                    int point = Integer.parseInt(Spoint[i]);
                    int maal = Integer.parseInt(Smaal[i]);
                    int won_who = 0;

                    db.add_score(round, playerid, point, maal, won_who);

                }
                db.close();

                Toast.makeText(getApplicationContext(), "Record Sucessfully Saved", Toast.LENGTH_SHORT).show();

                Intent intent = new Intent(ScoreEntry.this, ScoreBoard.class);
                startActivity(intent);

            }

Below is the error

10-09 23:25:52.342: E/AndroidRuntime(584): FATAL EXCEPTION: main
10-09 23:25:52.342: E/AndroidRuntime(584): android.database.sqlite.SQLiteException: near "transaction": syntax error: CREATE TABLE transaction (id INTEGER PRIMARY KEY AUTOINCREMENT, round INTEGER, playerid INTEGER, point INTEGER, maal INTEGER, won INTEGER );
10-09 23:25:52.342: E/AndroidRuntime(584):  at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method)
10-09 23:25:52.342: E/AndroidRuntime(584):  at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1727)
10-09 23:25:52.342: E/AndroidRuntime(584):  at com.vivek.marriage_center_collection.CenterCollectionDatabase$DbHelper.onCreate(CenterCollectionDatabase.java:41)
10-09 23:25:52.342: E/AndroidRuntime(584):  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:106)
10-09 23:25:52.342: E/AndroidRuntime(584):  at com.vivek.marriage_center_collection.CenterCollectionDatabase.open(CenterCollectionDatabase.java:68)
10-09 23:25:52.342: E/AndroidRuntime(584):  at com.vivek.marriage_center_collection.ScoreEntry$1.onClick(ScoreEntry.java:133)
10-09 23:25:52.342: E/AndroidRuntime(584):  at android.view.View.performClick(View.java:2408)
10-09 23:25:52.342: E/AndroidRuntime(584):  at android.view.View$PerformClick.run(View.java:8816)
10-09 23:25:52.342: E/AndroidRuntime(584):  at android.os.Handler.handleCallback(Handler.java:587)
10-09 23:25:52.342: E/AndroidRuntime(584):  at android.os.Handler.dispatchMessage(Handler.java:92)
10-09 23:25:52.342: E/AndroidRuntime(584):  at android.os.Looper.loop(Looper.java:123)
10-09 23:25:52.342: E/AndroidRuntime(584):  at android.app.ActivityThread.main(ActivityThread.java:4627)
10-09 23:25:52.342: E/AndroidRuntime(584):  at java.lang.reflect.Method.invokeNative(Native Method)
10-09 23:25:52.342: E/AndroidRuntime(584):  at java.lang.reflect.Method.invoke(Method.java:521)
10-09 23:25:52.342: E/AndroidRuntime(584):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
10-09 23:25:52.342: E/AndroidRuntime(584):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
10-09 23:25:52.342: E/AndroidRuntime(584):  at dalvik.system.NativeStart.main(Native Method)

Answer 1

I think the problem is the name of your table. That is a keyword in sqlite, so you can't use it as a table name.

From the link: "... The SQL standard specifies a huge number of keywords which may not be used as the names of tables ..."

SQLite docs

Answer 2

Not sure if this is it yet

public static final String KEY_ID = "id";

should be

public static final String KEY_ID = "_id";

in the sqlite world.