android.database.sqlite.SQLiteException: near ": syntax error (code 1): , while compiling: android programing error

Question

I made an android database with two tables. The second one is not yet initialized but for the first one I get this error.

This is my logcat.

02-23 01:55:41.494      855-855/tubapps.budgetdatabase E/AndroidRuntime﹕ FATAL EXCEPTION: main
    java.lang.RuntimeException: Unable to start activity ComponentInfo{tubapps.budgetdatabase/tubapps.budgetdatabase.MainActivity}: android.database.sqlite.SQLiteException: near "create_income_table": syntax error (code 1): , while compiling: create_income_table income(_id INTEGER PRIMARY KEY AUTOINCREMENT, income_amount TEXT NOT NULL, income_payer TEXT NOT NULL, income_date TEXT NOT NULL, income_category TEXT NOT NULL, income_payments TEXT NOT NULL);
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2059)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
            at android.app.ActivityThread.access$600(ActivityThread.java:130)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:4745)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
            at dalvik.system.NativeStart.main(Native Method)
     Caused by: android.database.sqlite.SQLiteException: near "create_income_table": syntax error (code 1): , while compiling: create_income_table income(_id INTEGER PRIMARY KEY AUTOINCREMENT, income_amount TEXT NOT NULL, income_payer TEXT NOT NULL, income_date TEXT NOT NULL, income_category TEXT NOT NULL, income_payments TEXT NOT NULL);
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
            at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
            at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
            at tubapps.budgetdatabase.DBHelper.onCreate(DBHelper.java:55)
            at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
            at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
            at tubapps.budgetdatabase.SQLController.open(SQLController.java:21)
            at tubapps.budgetdatabase.MainActivity.onCreate(MainActivity.java:29)
            at android.app.Activity.performCreate(Activity.java:5008)
            at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
            at android.app.ActivityThread.access$600(ActivityThread.java:130)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:4745)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
            at dalvik.system.NativeStart.main(Native Method)

This is my dbhelper.

public class DBHelper extends SQLiteOpenHelper {

    // TABLE INFORMATTION
    public static final String TABLE_INCOME = "income";
    public static final String INCOME_ID = "_id";
    public static final String INCOME_AMOUNT = "income_amount";
    public static final String INCOME_PAYER = "income_payer";
    public static final String INCOME_DATE = "income_date";
    public static final String INCOME_CATEGORY = "income_category";
    public static final String INCOME_PAYMENTS = "income_payments";

    public static final String TABLE_EXPENSE = "expense";
    public static final String EXPENSE_ID = "_id";
    public static final String EXPENSE_AMOUNT = "expense_amount";
    public static final String EXPENSE_PAYEE = "expense_payee";
    public static final String EXPENSE_DATE = "expense_date";
    public static final String EXPENSE_CATEGORY = "expense_category";
    public static final String EXPENSE_PAYMENTS = "expense_payments";

    // DATABASE INFORMATION
    static final String DB_NAME = "BUDGET.DB";
    static final int DB_VERSION = 1;

    // TABLE CREATION STATEMENT
    private static final String CREATE_INCOME_TABLE = "createIncomeTable "
            + TABLE_INCOME + "(" + INCOME_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + INCOME_AMOUNT + " TEXT NOT NULL, "
            + INCOME_PAYER + " TEXT NOT NULL, "
            + INCOME_DATE + " TEXT NOT NULL, "
            + INCOME_CATEGORY + " TEXT NOT NULL, "
            + INCOME_PAYMENTS + " TEXT NOT NULL);";

    private static final String CREATE_EXPENSE_TABLE = "createExpenseTable "
            + TABLE_EXPENSE + "(" + EXPENSE_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + EXPENSE_AMOUNT + " TEXT NOT NULL, "
            + EXPENSE_PAYEE + " TEXT NOT NULL, "
            + EXPENSE_DATE + " TEXT NOT NULL, "
            + EXPENSE_CATEGORY + " TEXT NOT NULL, "
            + EXPENSE_PAYMENTS + " TEXT NOT NULL);";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_INCOME_TABLE);
        db.execSQL(CREATE_EXPENSE_TABLE);
    }

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

This is my sqlcontroller.

public class SQLController {

    private DBHelper dbhelper;
    private Context ourcontext;
    private SQLiteDatabase database;

    public SQLController(Context c) {
        ourcontext = c;
    }

    public SQLController open() throws SQLException {
        dbhelper = new DBHelper(ourcontext);
        database = dbhelper.getWritableDatabase();
        return this;

    }

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

    public void insertIncomeData(String incomeAmount, String incomePayer, String incomeDate,
                           String incomeCategory, String incomePayments) {
        ContentValues cv = new ContentValues();
        cv.put(DBHelper.INCOME_AMOUNT, incomeAmount);
        cv.put(DBHelper.INCOME_PAYER, incomePayer);
        cv.put(DBHelper.INCOME_DATE, incomeDate);
        cv.put(DBHelper.INCOME_CATEGORY, incomeCategory);
        cv.put(DBHelper.INCOME_PAYMENTS, incomePayments);
        database.insert(DBHelper.TABLE_INCOME, null, cv);
    }

    public void insertExpenseData(String expenseAmount, String expensePayee, String expenseDate,
                                  String expenseCategory, String expensePayments) {
        ContentValues cv = new ContentValues();
        cv.put(DBHelper.EXPENSE_AMOUNT, expenseAmount);
        cv.put(DBHelper.EXPENSE_PAYEE, expensePayee);
        cv.put(DBHelper.EXPENSE_DATE, expenseDate);
        cv.put(DBHelper.EXPENSE_CATEGORY, expenseCategory);
        cv.put(DBHelper.EXPENSE_PAYMENTS, expensePayments);
        database.insert(DBHelper.TABLE_EXPENSE, null, cv);
    }

    //Getting Cursor to read data from table
    public Cursor readIncomeData() {
        String[] allColumns = new String[] { DBHelper.INCOME_ID,
                DBHelper.INCOME_AMOUNT, DBHelper.INCOME_PAYER, DBHelper.INCOME_DATE,
                DBHelper.INCOME_CATEGORY, DBHelper.INCOME_PAYMENTS };
        Cursor c = database.query(DBHelper.TABLE_INCOME, allColumns, null,
                null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    //Getting Cursor to read data from table
    public Cursor readExpenseData() {
        String[] allColumns = new String[] { DBHelper.EXPENSE_ID,
                DBHelper.EXPENSE_AMOUNT, DBHelper.EXPENSE_PAYEE, DBHelper.EXPENSE_DATE,
                DBHelper.EXPENSE_CATEGORY, DBHelper.EXPENSE_PAYMENTS };
        Cursor c = database.query(DBHelper.TABLE_EXPENSE, allColumns, null,
                null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    //Updating record data into table by id
    public int updateIncomeData(long incomeID, String newIncomeAmount, String newIncomePayer, String newIncomeDate,
                          String newIncomeCategory, String newIncomePayments) {
        ContentValues cvUpdate = new ContentValues();
        cvUpdate.put(DBHelper.INCOME_AMOUNT, newIncomeAmount);
        cvUpdate.put(DBHelper.INCOME_PAYER, newIncomePayer);
        cvUpdate.put(DBHelper.INCOME_DATE, newIncomeDate);
        cvUpdate.put(DBHelper.INCOME_CATEGORY, newIncomeCategory);
        cvUpdate.put(DBHelper.INCOME_PAYMENTS, newIncomePayments);
        int i = database.update(DBHelper.TABLE_INCOME, cvUpdate,
                DBHelper.INCOME_ID + " = " + incomeID, null);
        return i;
    }

    public int updateExpenseData(long expenseID, String newExpenseAmount, String newExpensePayer, String newExpenseDate,
                                String newExpenseCategory, String newExpensePayments) {
        ContentValues cvUpdate = new ContentValues();
        cvUpdate.put(DBHelper.EXPENSE_AMOUNT, newExpenseAmount);
        cvUpdate.put(DBHelper.EXPENSE_PAYEE, newExpensePayer);
        cvUpdate.put(DBHelper.EXPENSE_DATE, newExpenseDate);
        cvUpdate.put(DBHelper.EXPENSE_CATEGORY, newExpenseCategory);
        cvUpdate.put(DBHelper.EXPENSE_PAYMENTS, newExpensePayments);
        int i = database.update(DBHelper.TABLE_EXPENSE, cvUpdate,
                DBHelper.EXPENSE_ID + " = " + expenseID, null);
        return i;
    }

    // Deleting record data from table by id
    public void deleteIncomeData(long incomeID) {
        database.delete(DBHelper.TABLE_INCOME, DBHelper.INCOME_ID + "="
                + incomeID, null);
    }

    public void deleteExpenseData(long expenseID) {
        database.delete(DBHelper.TABLE_EXPENSE, DBHelper.EXPENSE_ID + "="
                + expenseID, null);
    }
}

And this is my main activity.

public class MainActivity extends ActionBarActivity {

    ListView lv;
    SQLController dbcon;
    TextView incomeID_tv, incomePayer_tv, incomeAmount_tv;
    TextView incomeDate_tv, incomeCategory_tv, incomePayments_tv;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbcon = new SQLController(this);
        dbcon.open();
        lv = (ListView) findViewById(R.id.incomeList_id);

        Cursor cursor = dbcon.readIncomeData();
        String[] from = new String[] { DBHelper.INCOME_ID, DBHelper.INCOME_AMOUNT, DBHelper.INCOME_PAYER,
                DBHelper.INCOME_DATE, DBHelper.INCOME_CATEGORY, DBHelper.INCOME_PAYMENTS};
        int[] to = new int[] { R.id.income_id, R.id.income_amount, R.id.income_payer, R.id.income_date,
                R.id.income_category, R.id.income_payments};

        SimpleCursorAdapter adapter = new SimpleCursorAdapter(
                MainActivity.this, R.layout.income_entry, cursor, from, to);

        adapter.notifyDataSetChanged();
        lv.setAdapter(adapter);

        lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view,
                                    int position, long id) {
                incomeID_tv = (TextView) view.findViewById(R.id.income_id);
                incomeAmount_tv = (TextView) view.findViewById(R.id.income_amount);
                incomePayer_tv = (TextView) view.findViewById(R.id.income_payer);
                incomeDate_tv = (TextView) view.findViewById(R.id.income_date);
                incomeCategory_tv = (TextView) view.findViewById(R.id.income_category);
                incomePayments_tv = (TextView) view.findViewById(R.id.income_payments);

                String incomeID = incomeID_tv.getText().toString();
                String incomeAmount = incomeAmount_tv.getText().toString();
                String incomePayer = incomePayer_tv.getText().toString();
                String incomeDate = incomeDate_tv.getText().toString();
                String incomeCategory = incomeCategory_tv.getText().toString();
                String incomePayments = incomePayments_tv.getText().toString();

                Intent modify_intent = new Intent(getApplicationContext(),
                        IncomeEdit.class);
                modify_intent.putExtra("incomeID", incomeID);
                modify_intent.putExtra("newIncomeAmount", incomeAmount);
                modify_intent.putExtra("newIncomePayer", incomePayer);
                modify_intent.putExtra("newIncomeDate", incomeDate);
                modify_intent.putExtra("newIncomeCategory", incomeCategory);
                modify_intent.putExtra("newIncomePayments", incomePayments);
                startActivity(modify_intent);
            }
        });
    }

Line 55 from DBHelper is this:

db.execSQL(CREATE_INCOME_TABLE);

Line 21 from SQLController is this:

database = dbhelper.getWritableDatabase();

And line 29 from MainActivity is this:

dbcon.open();

I've seen in other questions that it might be from table name. It was with spaces between them but after I changed it, it still not working.

Answer 1

You are executing the command:

create_income_table income(
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    income_amount TEXT NOT NULL,
    income_payer TEXT NOT NULL,
    income_date TEXT NOT NULL,
    income_category TEXT NOT NULL,
    income_payments TEXT NOT NULL
);

Replace create_income_table with CREATE TABLE. You need to execute this SQL:

CREATE TABLE income(
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    income_amount TEXT NOT NULL,
    income_payer TEXT NOT NULL,
    income_date TEXT NOT NULL,
    income_category TEXT NOT NULL,
    income_payments TEXT NOT NULL
);

Answer 2

exception is clearly shown in this line

Caused by: android.database.sqlite.SQLiteException: near "create_income_table": syntax error (code 1): , while compiling: create_income_table income(_id INTEGER PRIMARY KEY AUTOINCREMENT, income_amount TEXT NOT NULL, income_payer TEXT NOT NULL, income_date TEXT NOT NULL, income_category TEXT NOT NULL, income_payments TEXT NOT NULL);

use this line to create table

 // TABLE CREATION STATEMENT
private static final String CREATE_INCOME_TABLE = "create table "
        + TABLE_INCOME + "(" + INCOME_ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + INCOME_AMOUNT + " TEXT NOT NULL, "
        + INCOME_PAYER + " TEXT NOT NULL, "
        + INCOME_DATE + " TEXT NOT NULL, "
        + INCOME_CATEGORY + " TEXT NOT NULL, "
        + INCOME_PAYMENTS + " TEXT NOT NULL)";

also modify your code for second table create command also