Error in creation of table

Question

I am encountering an error for my app such that I can't even launch it.

SqliteDatabase.java

public class SqliteDatabase extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 5;
    private static final String DATABASE_NAME = "myexpenditure";
    private static final String TABLE_EXPENDITURE = "myexpenditures";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_PLACE = "place";
    private static final String COLUMN_DATE = "date";
    private static final String COLUMN_AMOUNT = "amount";
    private static final String COLUMN_CATEGORIES = "categories";
    private static final String COLUMN_ADDITIONAL_INFO = "additional_info";


    public SqliteDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String CREATE_EXPENDITURE_TABLE = "CREATE TABLE " + TABLE_EXPENDITURE + "(" + COLUMN_ID + " INTEGER PRIMARY," + COLUMN_PLACE + " TEXT," + COLUMN_DATE + " TEXT," + COLUMN_AMOUNT + " INTEGER," + COLUMN_CATEGORIES + " TEXT," + COLUMN_ADDITIONAL_INFO + " TEXT" + ")" ;
        sqLiteDatabase.execSQL(CREATE_EXPENDITURE_TABLE);
    }

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

    public List<Expenditure> listExpenditure() {
        String sql = "select * from " + TABLE_EXPENDITURE;
        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        List<Expenditure> storeExpenditure = new ArrayList<>();
        Cursor cursor = sqLiteDatabase.rawQuery(sql, null);
        if (cursor.moveToFirst()) {
            do {
                int id = Integer.parseInt(cursor.getString(0));
                String place = cursor.getString(1);
                String date = cursor.getString(2);
                int amount = Integer.parseInt(cursor.getString(3));
                String category = cursor.getString(4);
                String additional_info = cursor.getString(5);
                storeExpenditure.add(new Expenditure(id, place, date, amount, category, additional_info));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return storeExpenditure;
    }

    public void addExpenditure(Expenditure expenditure){
        ContentValues values = new ContentValues();
        values.put(COLUMN_PLACE,expenditure.getPlace());
        values.put(COLUMN_DATE,expenditure.getDate());
        values.put(COLUMN_AMOUNT,expenditure.getAmount());
        values.put(COLUMN_CATEGORIES,expenditure.getCategories());
        values.put(COLUMN_ADDITIONAL_INFO,expenditure.getAdditional_Info());
        SQLiteDatabase db = this.getWritableDatabase();
        db.insert(TABLE_EXPENDITURE,null,values);
    }

    public void updateExpenditure(Expenditure expenditure){
        ContentValues values = new ContentValues();
        values.put(COLUMN_PLACE,expenditure.getPlace());
        values.put(COLUMN_DATE,expenditure.getDate());
        values.put(COLUMN_AMOUNT,expenditure.getAmount());
        values.put(COLUMN_CATEGORIES,expenditure.getCategories());
        values.put(COLUMN_ADDITIONAL_INFO,expenditure.getAdditional_Info());
        SQLiteDatabase db = this.getWritableDatabase();
        db.update(TABLE_EXPENDITURE,values,COLUMN_ID + " =?",new String[]{String.valueOf(expenditure.getId())});

    }

    public Expenditure findExpenditure(String place){
        String query = "Select * FROM" + TABLE_EXPENDITURE + "WHERE"+ COLUMN_PLACE + " = " + "place";
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        Expenditure mExpenditure =null;
        Cursor cursor=sqLiteDatabase.rawQuery(query,null);
        if (cursor.moveToFirst()){
            int id = Integer.parseInt(cursor.getString(0));
            String expenditurePlace = cursor.getString(1);
            String date = cursor.getString(2);
            int amount = Integer.parseInt(cursor.getString(3));
            String category = cursor.getString(4);
            String additional_info = cursor.getString(5);
            mExpenditure = new Expenditure(id,expenditurePlace,date,amount,category,additional_info);

        }
        cursor.close();
        return mExpenditure;
    }

    public void deleteExpenditure(int id){
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        sqLiteDatabase.delete(TABLE_EXPENDITURE,COLUMN_ID + " = ?",new String[]{String.valueOf(id)});
    }

}

MainActivity.Java

public class MainActivity extends AppCompatActivity {

private static final String TAG = ExpenditureSummary.class.getSimpleName();
private SqliteDatabase mDatabase;


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

    FrameLayout frameLayout=(FrameLayout)findViewById(R.id.activity_expenditure_summary);
    RecyclerView expenditureView =(RecyclerView)findViewById(R.id.expenditure_list);
    LinearLayoutManager linearLayoutManager = new LinearLayoutManager(this);
    expenditureView.setLayoutManager(linearLayoutManager);

    expenditureView.setHasFixedSize(true);
    mDatabase = new SqliteDatabase(this);

    List<Expenditure> allExpenditure = mDatabase.listExpenditure();

    if (allExpenditure.size() > 0){
        expenditureView.setVisibility(View.VISIBLE);
        ExpenditureAdapter mAdapter = new ExpenditureAdapter(this,allExpenditure);
        expenditureView.setAdapter(mAdapter);
    } else {
        expenditureView.setVisibility(View.GONE);
        Toast.makeText(this, "There is no entries.Start adding now", Toast.LENGTH_SHORT).show();
    }

    FloatingActionButton fab =(FloatingActionButton)findViewById(R.id.fab);

    fab.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
        addTaskDialog();
        }
    });
}

private void addTaskDialog(){
    LayoutInflater inflater = LayoutInflater.from(this);
    View subview = inflater.inflate(R.layout.activity_edit_expenditure_details,null);

    final EditText placeField = (EditText)subview.findViewById(R.id.enter_place);

    //date picker
         final EditText dateField =(EditText)subview.findViewById(R.id.enter_date);
         final Calendar myCalendar = Calendar.getInstance();
         final DatePickerDialog.OnDateSetListener datePickerListener = new DatePickerDialog.OnDateSetListener() {
        @Override
        public void onDateSet(DatePicker view, int year, int monthOfYear, int dayOfMonth) {
            myCalendar.set(Calendar.YEAR,year);
            myCalendar.set(Calendar.MONTH,monthOfYear);
            myCalendar.set(Calendar.DAY_OF_MONTH,dayOfMonth);
            String myFormat ="dd/MM/yyyy";
            SimpleDateFormat simpleDateFormat =new SimpleDateFormat(myFormat, Locale.UK);
            dateField.setText(simpleDateFormat.format(myCalendar.getTime()));
        }
    };
        dateField.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
           new DatePickerDialog(MainActivity.this,datePickerListener,myCalendar.get(Calendar.YEAR),myCalendar.get(Calendar.MONTH),myCalendar.get(Calendar.DAY_OF_MONTH)).show();
        }
    });

    final EditText amountField =(EditText)subview.findViewById(R.id.enter_amount);

    //Spinner
    final Spinner spinnerCategories=(Spinner)subview.findViewById(R.id.enter_category);
    ArrayAdapter<CharSequence> adapter = ArrayAdapter.createFromResource(this,R.array.categories, android.R.layout.simple_spinner_item);
    adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
    spinnerCategories.setAdapter(adapter);

    final EditText additional_infoField = (EditText)subview.findViewById(R.id.enter_additional_info);

    AlertDialog.Builder builder = new AlertDialog.Builder(this);
    builder.setTitle("Add new expense");
    builder.setView(subview);
    builder.create();

    builder.setPositiveButton("ADD EXPENSE", new DialogInterface.OnClickListener() {
        @Override
        public void onClick(DialogInterface dialogInterface, int i) {
            final String place = placeField.getText().toString();
            final String date = dateField.getText().toString();
            final int amount = Integer.parseInt(amountField.getText().toString());
            final String category = spinnerCategories.getSelectedItem().toString();
            final String additional_info = additional_infoField.getText().toString();

            if (TextUtils.isEmpty(place)|| amount <= 0 || TextUtils.isEmpty(category)||TextUtils.isEmpty(date)){
                Toast.makeText(MainActivity.this, "Please input values", Toast.LENGTH_SHORT).show();
            }
            else {
                Expenditure newExpenditure = new Expenditure(place,date,amount,category,additional_info);
                mDatabase.addExpenditure(newExpenditure);
                finish();
                startActivity(getIntent());
            }
        }
    });
    builder.setNegativeButton("CANCEL",new DialogInterface.OnClickListener(){

        @Override
        public void onClick(DialogInterface dialogInterface, int i) {
            Toast.makeText(MainActivity.this, "Task Cancelled", Toast.LENGTH_SHORT).show();
        }
    });
    builder.show();
}

        @Override
        protected void onDestroy(){
        super.onDestroy();
        if (mDatabase != null){
        mDatabase.close();
    }
}

}

Error is as follows :

03-24 10:10:16.844 19709-19709/com.nebula.sebjohn.myexpenses E/AndroidRuntime: FATAL EXCEPTION: main Process: com.nebula.sebjohn.myexpenses, PID: 19709 java.lang.RuntimeException: Unable to start activity ComponentInfo{com.nebula.sebjohn.myexpenses/com.nebula.sebjohn.myexpenses.MainActivity}: android.database.sqlite.SQLiteException: near ",": syntax error (code 1): , while compiling: CREATE TABLE myexpenditures(id INTEGER PRIMARY,place TEXT,date TEXT,amount INTEGER,categories TEXT,additional_info TEXT) at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2439) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2499) at android.app.ActivityThread.access$900(ActivityThread.java:166) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1360) at android.os.Handler.dispatchMessage(Handler.java:102) at android.os.Looper.loop(Looper.java:148) at android.app.ActivityThread.main(ActivityThread.java:5468) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616) Caused by: android.database.sqlite.SQLiteException: near ",": syntax error (code 1): , while compiling: CREATE TABLE myexpenditures(id INTEGER PRIMARY,place TEXT,date TEXT,amount INTEGER,categories TEXT,additional_info TEXT) at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1674) at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1605) at Database.SqliteDatabase.onCreate(SqliteDatabase.java:39) at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251) at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187) at Database.SqliteDatabase.listExpenditure(SqliteDatabase.java:51) at com.nebula.sebjohn.myexpenses.MainActivity.onCreate(MainActivity.java:50) at android.app.Activity.performCreate(Activity.java:6556) at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1108) at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2392) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2499)  at android.app.ActivityThread.access$900(ActivityThread.java:166)  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1360)  at android.os.Handler.dispatchMessage(Handler.java:102)  at android.os.Looper.loop(Looper.java:148)  at android.app.ActivityThread.main(ActivityThread.java:5468)  at java.lang.reflect.Method.invoke(Native Method)  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616) 

I am new to Android app development.Please help me to solve this issue.I am trying to store the data within my phone itself using SQLiteDatabase.

Build.gradle(Module:app)

    android {
    compileSdkVersion 25
    buildToolsVersion '25.0.0'
    defaultConfig {
        applicationId "com.nebula.sebjohn.myexpenses"
        minSdkVersion 14
        targetSdkVersion 25
        versionCode 1
        versionName "1.0"
        testInstrumentationRunner "android.support.test.runner.AndroidJUnitRunner"
    }
    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.pro'
        }
    }
}

dependencies {
    compile fileTree(dir: 'libs', include: ['*.jar'])
    androidTestCompile('com.android.support.test.espresso:espresso-core:2.2.2', {
        exclude group: 'com.android.support', module: 'support-annotations'
    })
    testCompile 'junit:junit:4.12'
    compile 'com.android.support:appcompat-v7:25.0.1'
    compile 'com.intuit.sdp:sdp-android:1.0.3'
    compile 'com.android.support:design:25.0.1'
    compile 'com.android.support:cardview-v7:25.0.1'
    compile 'com.android.support:recyclerview-v7:25.0.1'
    compile 'com.android.support:support-v4:25.0.1'
    compile 'com.google.code.gson:gson:2.6.1'
    compile 'io.realm:android-adapters:1.4.0'
    compile 'com.android.support.constraint:constraint-layout:1.0.2'
    testCompile 'junit:junit:4.12'
}

Answer 1

There is syntax error in your case as well primary key one so i have fixed it so see difference

  String CREATE_EXPENDITURE_TABLE = "CREATE TABLE " + TABLE_EXPENDITURE + "(" + COLUMN_ID + " INTEGER PRIMARY KEY, " + COLUMN_PLACE + " TEXT, " +
        COLUMN_DATE + " TEXT, " + COLUMN_AMOUNT +
        " INTEGER, " + COLUMN_CATEGORIES + " TEXT, " + COLUMN_ADDITIONAL_INFO + " TEXT" + ")";

Answer 2

Use PRIMARY KEY not PRIMARY

       String CREATE_EXPENDITURE_TABLE = "CREATE TABLE " + TABLE_EXPENDITURE 
        + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
        + COLUMN_PLACE + " TEXT,"
        + COLUMN_DATE + " TEXT,"
        + COLUMN_AMOUNT + " INTEGER,"
        + COLUMN_CATEGORIES + " TEXT," 
        + COLUMN_ADDITIONAL_INFO + " TEXT"
        + ")" ;

Answer 3

When creating a primary key you need to say PRIMARY KEY, not just PRIMARY:

String CREATE_EXPENDITURE_TABLE = "CREATE TABLE " + TABLE_EXPENDITURE + "(" + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_PLACE + " TEXT," + COLUMN_DATE + " TEXT," + COLUMN_AMOUNT + " INTEGER," + COLUMN_CATEGORIES + " TEXT," + COLUMN_ADDITIONAL_INFO + " TEXT" + ")" ;

Answer 4

There is a problem with your SQL query, your query is missing apostrophes in the column names and table name. Your query is also missing a KEY param in the primary key declaration part. Please modify your query as follows:

CREATE TABLE `myexpenditures` (
`id`    INTEGER PRIMARY KEY AUTOINCREMENT,
`place` TEXT,
`date`  TEXT,
`amount`    INTEGER,
`categories`    TEXT,
`additional_info`   TEXT
); 

I hope this helps.