database.sqlite.SQLiteException

Question

I am building a workout logging app as a project. I am trying to insert the workout name into the database. Before prompting the user to then add in there exercise. Currently it it displyed the "Something went wrong error" and then app crashes.

Below is the error in the Logcat

android.database.sqlite.SQLiteException: near "Name": syntax error (code 1): , while compiling: INSERT INTO workout_table(Workout Name) VALUES (?)
 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.<init>(SQLiteProgram.java:58)
                                                                               at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
                                                                               at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
                                                                               at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
                                                                               at com.example.cormac.gymbud.DatabaseHelper.addWorkout(DatabaseHelper.java:112)
                                                                               at com.example.cormac.gymbud.AddWorkout.AddData(AddWorkout.java:65)
                                                                               at com.example.cormac.gymbud.AddWorkout.access$100(AddWorkout.java:15)
                                                                               at com.example.cormac.gymbud.AddWorkout$1.onClick(AddWorkout.java:49)
                                                                               at android.view.View.performClick(View.java:5205)
                                                                               at android.view.View$PerformClick.run(View.java:21164)
                                                                               at android.os.Handler.handleCallback(Handler.java:739)
                                                                               at android.os.Handler.dispatchMessage(Handler.java:95)

Below is my DatabaseHelper.class

public class DatabaseHelper extends SQLiteOpenHelper {
    private static String TAG = "DatabaseHelper";

    //workout table
    public static final String TABLE_WORKOUT = "workout_table";
    public static final String COL1 = "ID";
    public static final String COL2 = "Workout Name";    
    //exercise table
    public static final String TABLE_EXERCISE ="exercise_table";
    public static final String COL3 = "ID";
    public static final String COL4 = ("Exercise Name");
    public static final String COL5 = ("Set");
    public static final String COL6 = ("Weight");
    public static final String COL7 = ("Reps");

    private static final String DATABASE_NAME = "workouts.db";
    private static final int DATABASE_VERSION = 1;

    private static final String createWorkoutTable = "CREATE TABLE " + TABLE_WORKOUT + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COL2 +" TEXT)";

    private static final String createExerciseTable = "CREATE TABLE " + TABLE_EXERCISE + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COL4 +" TEXT," + COL5 +" INTEGER,"+ COL6 + " INTEGER," + COL7 + "INTEGER)";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        //db.execSQL("Create table user(email text primary key, password text)");    
                db.execSQL(createWorkoutTable);    
                db.execSQL(createExerciseTable);    
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        //db.execSQL("drop table if exists user");
        //onCreate(db);

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

      //inserting in database
       // public boolean insert(String email, String password){
         //   SQLiteDatabase db = this.getWritableDatabase();
       // ContentValues contentValues = new ContentValues();
        //contentValues.put("email",email);
        //contentValues.put("password",password);
        //long ins = db.insert("user", null,contentValues);
        //if (ins==1) return false;
        //else return true;
    //}

    //checking if the email already exists
    //public Boolean checkEmail(String email){
      //  SQLiteDatabase db = this.getReadableDatabase();
       // Cursor cursor = db.rawQuery("Select * from user where email=?",new String[]{email});
        //if (cursor.getCount()>0) return false;
        //else return true;
    //}

    //checking the email and password
    //public Boolean emailpassword(String email, String password){
      //  SQLiteDatabase db = this.getReadableDatabase();
       // Cursor cursor = db.rawQuery("select * from user where email=? and password=?",new String[]{email,password});
       // if (cursor.getCount()>0) return true;
        //else return false;
    //}

    public boolean addWorkout(String workout){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL2,workout);

        Log.d(TAG, "addData: Adding " + workout + " to" + TABLE_WORKOUT);    
        long result = db.insert(TABLE_WORKOUT, null, contentValues);    
        if (result == -1){    
            return false;
        }else{
            return true;
        }    
    }

    public boolean addExercise(String item){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(item, COL4);
        contentValues.put(item, COL5);
        contentValues.put(item, COL6);
        contentValues.put(item, COL7);

        Log.d(TAG, "addData: Adding " + item + " to" + TABLE_EXERCISE);    
        long result = db.insert(TABLE_EXERCISE, null, contentValues);    
        if (result == -1){
            return false;
        }else{
            return true;
        }
    }

    //return data from the database 
    public Cursor getDataWorkout(){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT * FROM " + TABLE_WORKOUT;
        Cursor data = db.rawQuery(query, null);
        return data;
    }

    public Cursor getDataExercise(){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT * FROM " + TABLE_EXERCISE;
        Cursor data = db.rawQuery(query, null);
        return data; 
    }

    public Cursor getItemIDWorkout(String name){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT " + COL1 + "FROM" + TABLE_WORKOUT +
                " WHERE " + COL2 + " = " + name + " ";
        Cursor data = db.rawQuery(query, null);
        return data;
    }

    public Cursor getItemIDExercise(String name){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT " + COL3 + "FROM" + TABLE_EXERCISE +
                " WHERE " + COL4 + " = " + name + " ";
        Cursor data = db.rawQuery(query, null);
        return data;
    }

    public Cursor getExerciseSet(Integer set){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("SELECT " + COL3 + "FROM" + TABLE_EXERCISE +
                " WHERE " + COL5 + " = " + set + " ");
        Cursor data = db.rawQuery(String.valueOf(query), null);
        return data; 
    }

    public Cursor getExerciseWeight(Integer weight){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("SELECT " + COL3 + "FROM" + TABLE_EXERCISE +
                " WHERE " + COL6 + " = " + weight + " ");
        Cursor data = db.rawQuery(String.valueOf(query), null);
        return data;    
    }

    public Cursor getExerciseRep(Integer rep){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("SELECT " + COL3 + "FROM" + TABLE_EXERCISE +
                " WHERE " + COL7 + " = " + rep + " ");
        Cursor data = db.rawQuery(String.valueOf(query), null);
        return data;    
    }

    public void updateWorkoutName(String newName, int id, String oldName){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "UPDATE " + TABLE_WORKOUT + " SET" + COL2 +
                " = " + newName + "WHERE" + COL1 + " = " + id + " " +
                " AND " + COL2 + " = " + oldName + " ";
        Log.d(TAG, "updateName: query " + query);
        Log.d(TAG, "updateName: Setting name to " + newName);
        db.execSQL(query);    
    }

    public void updateExerciseName(String newName, int id, String oldName){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "UPDATE" + TABLE_EXERCISE + "SET" + COL4 +
                " = " + newName + "WHERE" + COL3 + " = " + id + " " +
                " AND " + COL4 + " = " + oldName + " ";
        Log.d(TAG, "updateName: query " + query);
        Log.d(TAG, "updateName: Setting name to" + newName);
        db.execSQL(query);
     }

    public void updateExerciseSet(int newSet, int id, String oldSet){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("UPDATE" + TABLE_EXERCISE + "SET" + COL5 +
                " = " + newSet + "WHERE" + COL3 + " = " + id + " " +
                " AND " + COL5 + " = " + oldSet + " ");
        Log.d(TAG, "updateSet: query " + query);
        Log.d(TAG, "updateSet: Setting name to" + newSet);
        db.execSQL(String.valueOf(query));
    }

    public void updateExerciseWeight(int newWeight, int id, String oldWeight){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("UPDATE" + TABLE_EXERCISE + "SET" + COL6 +
                " = " + newWeight + "WHERE" + COL3 + " = " + id + " " +
                " AND " + COL6 + " = " + oldWeight + " ");
        Log.d(TAG, "updateWeight: query " + query);
        Log.d(TAG, "updateWeight: Setting name to" + newWeight);
        db.execSQL(String.valueOf(query));
    }

    public void updateExerciseRep(int newRep, int id, String oldRep){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("UPDATE" + TABLE_EXERCISE + "SET" + COL7 +
                " = " + newRep + "WHERE" + COL3 + " = " + id + " " +
                " AND " + COL7 + " = " + oldRep + " ");
        Log.d(TAG, "updateRep: query " + query);
        Log.d(TAG, "updateRep: Setting name to" + newRep);
        db.execSQL(String.valueOf(query));
    }

    public void deleteWorkoutName(int id, String name){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_WORKOUT + " WHERE "
                + COL1 + " = '" + id + "'" +
                " AND " + COL2 + " = '" + name + "'";
        Log.d(TAG, "deleteName: query: " + query);
        Log.d(TAG, "deleteName: Deleting " + name + " from database.");
        db.execSQL(query);
    }

    public void deleteExerciseName(int id, String name){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_EXERCISE + " WHERE "
                + COL3 + " = '" + id + "'" +
                " AND " + COL4 + " = '" + name + "'";
        Log.d(TAG, "deleteName: query: " + query);
        Log.d(TAG, "deleteName: Deleting " + name + " from database.");
        db.execSQL(query);    
    }

    public void deleteExerciseSet(int id, int set){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_EXERCISE + " WHERE "
                + COL3 + " = '" + id + "'" +
                " AND " + COL5 + " = '" + set + "'";
        Log.d(TAG, "deleteSet: query: " + query);
        Log.d(TAG, "deleteSet: Deleting " + set + " from database.");
        db.execSQL(query);    
    }

    public void deleteExerciseWeight(int id, int Weight){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_EXERCISE + " WHERE "
                + COL3 + " = '" + id + "'" +
                " AND " + COL6 + " = '" + Weight + "'";
        Log.d(TAG, "deleteWeight: query: " + query);
        Log.d(TAG, "deleteWeight: Deleting " + Weight + " from database.");
        db.execSQL(query);    
    }

    public void deleteExerciseRep(int id, int rep){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_EXERCISE + " WHERE "
                + COL3 + " = '" + id + "'" +
                " AND " + COL7 + " = '" + rep + "'";
        Log.d(TAG, "deleteRep: query: " + query);
        Log.d(TAG, "deleteRep: Deleting " + rep + " from database.");
        db.execSQL(query);    
    }    
}

Here is the AddWorout Activty

public class AddWorkout extends AppCompatActivity implements View.OnClickListener {

    public static final String TAG = "AddWorkout";
    private EditText edtWorkout;
    private Button btnAddW;
    DatabaseHelper mDatabseHelper;
    private WorkoutDAO mWorkoutDOA;

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

        initViews();
        edtWorkout = (EditText) findViewById(R.id.edtWorkout);
        btnAddW = (Button) findViewById(R.id.btnAddW);
        mDatabseHelper = new DatabaseHelper(this);

        btnAddW.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                String workout = edtWorkout.getText().toString();
                if (edtWorkout.length() != 0) {
                    AddData(workout);
                    edtWorkout.setText("");
                    Intent intent = new Intent(AddWorkout.this, AddExercise.class);
                    startActivity(intent);
                } else {
                    toastMessage("You must put something in the text field!");
                }
                ;
            }
        });
    }

    private void AddData(String workout) {
        boolean insertData = mDatabseHelper.addWorkout(workout);

        if (insertData){
            toastMessage("Data Successfully Inserted");
        } else {
            toastMessage("Something went wrong");
        }
    }

    private void toastMessage(String s) {
        Toast.makeText(this,s,Toast.LENGTH_SHORT).show();
    }

    private void initViews(){

        this.edtWorkout = (EditText) findViewById(R.id.edtWorkout);
        this.btnAddW = (Button) findViewById(R.id.btnAddW);
        this.btnAddW.setOnClickListener(this);
    }

    @Override
    public void onClick(View view) {

        switch (view.getId()) {
            case R.id.btnAddW:
                Editable WorkoutName = edtWorkout.getText();

                if (!TextUtils.isEmpty(WorkoutName) ) {
                    // add the exercise to database
                    Workout createdWorkout = mWorkoutDOA.createWorkout(
                            WorkoutName.toString());

                    Log.d(TAG, "added workout : "+ createdWorkout.getName());
                    Intent intent = new Intent();
                    intent.putExtra(ListWorkoutActivity.EXTRA_ADDED_WORKOUT, (Parcelable) createdWorkout);
                    setResult(RESULT_OK, intent);
                    finish();
                }
                else {
                    Toast.makeText(this, R.string.empty_fields_message, Toast.LENGTH_LONG).show();
                }
                break;

            default:
                break;
        }
    }
    @Override
    protected void onDestroy() {
        super.onDestroy();
        mWorkoutDOA.close();
    }
}

Answer 1

Your issue is that you've defined COL2 with public static final String COL2 = "Workout Name";

The space between Workout and Name will cause issues such as this but sometimes not cause issue such as when creating the table.

• i.e the column will be Workout and the column type will be name TEXT which will have a type affinity of TEXT as it contains TEXT. more about this at - Datatypes In SQLite Version 3

You can either change the column name to not have a space e.g.

public static final String COL2 = "Workout_Name"; //<<<< recommended way

or you could enclose it e.g. using ONE of the following:-

public static final String COL2 = "[Workout Name]"; 
public static final String COL2 = "`Workout Name`";
public static final String COL2 = "'Workout Name'";
public static final String COL2 = "\"Workout Name\"";

These fixes would require that the table is dropped and recreated as the current column name would be Workout

If you really wanted, you may get away with using public static final String COL2 = "Workout"; as that is what the column name will currently be. This wouldn't, at this stage, require dropping and recreating the table.

NOTE

You will likely have similar problems with COL4 being Excerise Name.