Stack Exchange
android.database.sqlite.SQLiteException: near "a": syntax error (code 1): , while compiling: INSERT INTO characters(a,b) VALUES (?,?)
I have created an SQLite Database in which I am trying to store form data collected from one activity. I keep getting this error in the logcat :
04-10 00:49:00.341 8953-8953/com.miniproject32.writeup E/SQLiteLog: (1) near "Peeves": syntax error
04-10 00:49:00.351 8953-8953/com.miniproject32.writeup E/SQLiteDatabase: Error inserting Pet Peeves=Arshiya Eye Colour=Arshiya Profession=Arshiya Gender=null Purpose of the Story=Arshiya Dreams=Arshiya Age=22 Hair Colour=Arshiya Name=Arshiya 
android.database.sqlite.SQLiteException: near "Peeves": syntax error (code 1): , while compiling: INSERT INTO characters(Pet Peeves,Eye Colour,Profession,Gender,Purpose of the Story,Dreams,Age,Hair Colour,Name) VALUES (?,?,?,?,?,?,?,?,?)
 #################################################################
 Error Code : 1 (SQLITE_ERROR)
 Caused By : SQL(query) error or missing database.
    (near "Peeves": syntax error (code 1): , while compiling: INSERT INTO characters(Pet Peeves,Eye Colour,Profession,Gender,Purpose of the Story,Dreams,Age,Hair Colour,Name) VALUES (?,?,?,?,?,?,?,?,?))
 #################################################################
     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:996)
     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:561)
     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1624)
     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1496)
     at com.miniproject32.writeup.CharacterTable.createCharacter(CharacterTable.java:110)
     at com.miniproject32.writeup.S4CharacterForm.nextScreen(S4CharacterForm.java:82)
     at java.lang.reflect.Method.invoke(Native Method)
     at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:288)
     at android.view.View.performClick(View.java:5716)
     at android.widget.TextView.performClick(TextView.java:10926)
     at android.view.View$PerformClick.run(View.java:22596)
     at android.os.Handler.handleCallback(Handler.java:739)
     at android.os.Handler.dispatchMessage(Handler.java:95)
     at android.os.Looper.loop(Looper.java:148)
     at android.app.ActivityThread.main(ActivityThread.java:7325)
     at java.lang.reflect.Method.invoke(Native Method)
     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1230)
     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1120)

My database class is this :
public class CharacterTable {

public static final String KEY_ROWID = "_id";
public static final String COLUMN_NAME_NAME = "Name";
public static final String COLUMN_NAME_GENDER = "Gender";
public static final String COLUMN_NAME_AGE = "Age";
public static final String COLUMN_NAME_HAIRCOLOR = "Hair Colour";
public static final String COLUMN_NAME_EYECOLOR = "Eye Colour";
public static final String COLUMN_NAME_PROF = "Profession";
public static final String COLUMN_NAME_DREAMS = "Dreams";
public static final String COLUMN_NAME_PEEVES = "Pet Peeves";
public static final String COLUMN_NAME_PURPOSE = "Purpose of the Story";


public static final String TAG = "CharacterTable";
private DatabaseHelper mDbHelper;
private SQLiteDatabase mDb;

private static final String DATABASE_NAME = "writeup_database";
private static final String DATABASE_TABLE = "characters";
private static final int DATABASE_VERSION = 3;

/**
 * Database creation sql statement
 */
private static final String DATABASE_CREATE =
        "create table " + DATABASE_TABLE + " (" + KEY_ROWID + " integer primary key autoincrement, "
                + COLUMN_NAME_NAME +" text not null, "
                + COLUMN_NAME_GENDER +" text not null, "
                + COLUMN_NAME_AGE +" text not null, "
                + COLUMN_NAME_HAIRCOLOR +" text not null, "
                + COLUMN_NAME_EYECOLOR +" text not null, "
                + COLUMN_NAME_PROF +" text not null, "
                + COLUMN_NAME_DREAMS +" text not null, "
                + COLUMN_NAME_PEEVES +" text not null, "
                + COLUMN_NAME_PURPOSE + " text not null);";

private final Context mCtx;

private static class DatabaseHelper extends SQLiteOpenHelper {

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i(TAG, "Creating DataBase: " + DATABASE_CREATE);
        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);
    }
}

/**
 * Constructor - takes the context to allow the database to be
 * opened/created
 *
 * @param ctx the Context within which to work
 */
public CharacterTable(Context ctx) {
    this.mCtx = ctx;
}

public CharacterTable open() throws SQLException {
    Log.i(TAG, "Opening DataBase Connection....");
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();
    return this;
}

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

public long createCharacter(String name, String gender, String age, String hairColor, String eyeColor,
                            String prof, String dreams, String peeves, String purpose) throws NullPointerException {
    Log.i(TAG, "Inserting record...");
    ContentValues initialValues = new ContentValues();
    initialValues.put(COLUMN_NAME_NAME, name);
    initialValues.put(COLUMN_NAME_GENDER, gender);
    initialValues.put(COLUMN_NAME_AGE, age);
    initialValues.put(COLUMN_NAME_HAIRCOLOR, hairColor);
    initialValues.put(COLUMN_NAME_EYECOLOR, eyeColor);
    initialValues.put(COLUMN_NAME_PROF, prof);
    initialValues.put(COLUMN_NAME_DREAMS, dreams);
    initialValues.put(COLUMN_NAME_PEEVES, peeves);
    initialValues.put(COLUMN_NAME_PURPOSE, purpose);

    return mDb.insert(DATABASE_TABLE, null, initialValues);
}

public boolean deleteCharacter(long rowId) {

    return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}

public Cursor fetchAllCharacters() {

    return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, COLUMN_NAME_NAME,
            COLUMN_NAME_GENDER, COLUMN_NAME_AGE, COLUMN_NAME_HAIRCOLOR, COLUMN_NAME_EYECOLOR,
            COLUMN_NAME_PROF, COLUMN_NAME_DREAMS, COLUMN_NAME_PEEVES, COLUMN_NAME_PURPOSE}, null, null, null, null, null);
}

public Cursor fetchCharacter(long charId) throws SQLException {

    Cursor mCursor =

            mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, COLUMN_NAME_NAME,
                            COLUMN_NAME_GENDER, COLUMN_NAME_AGE, COLUMN_NAME_HAIRCOLOR, COLUMN_NAME_EYECOLOR,
                            COLUMN_NAME_PROF, COLUMN_NAME_DREAMS, COLUMN_NAME_PEEVES, COLUMN_NAME_PURPOSE},
                            KEY_ROWID + "=" + charId, null, null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}

public boolean updateCharacter(int charId, String name, String gender, String age, String hairColor, String eyeColor,
                               String prof, String dreams, String peeves, String purpose) {
    ContentValues args = new ContentValues();
    args.put(COLUMN_NAME_NAME, name);
    args.put(COLUMN_NAME_GENDER, gender);
    args.put(COLUMN_NAME_AGE, age);
    args.put(COLUMN_NAME_HAIRCOLOR, hairColor);
    args.put(COLUMN_NAME_EYECOLOR, eyeColor);
    args.put(COLUMN_NAME_PROF, prof);
    args.put(COLUMN_NAME_DREAMS, dreams);
    args.put(COLUMN_NAME_PEEVES, peeves);
    args.put(COLUMN_NAME_PURPOSE, purpose);

    return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + charId, null) > 0;
}
}

My activity is this :
public class S4CharacterForm extends AppCompatActivity {

TextView t1, t2, t3, t4, t5, t6, t7, t8, t9;
EditText e1, e3, e4, e5, e6, e7, e8, e9;
RadioGroup rg;
RadioButton r2;

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

    ImageView imageView = (ImageView) findViewById(R.id.img_s3);
    Glide.with(this).load(R.drawable.banner_s3).into(imageView);

    Typeface tf = Typeface.createFromAsset(getAssets(), "font.ttf");
    t1 = (TextView) findViewById(R.id.name);
    t1.setTypeface(tf);
    t2 = (TextView) findViewById(R.id.gender);
    t2.setTypeface(tf);
    t3 = (TextView) findViewById(R.id.age);
    t3.setTypeface(tf);
    t4 = (TextView) findViewById(R.id.hair_c);
    t4.setTypeface(tf);
    t5 = (TextView) findViewById(R.id.eye_c);
    t5.setTypeface(tf);
    t6 = (TextView) findViewById(R.id.prof);
    t6.setTypeface(tf);
    t7 = (TextView) findViewById(R.id.dreams);
    t7.setTypeface(tf);
    t8 = (TextView) findViewById(R.id.peeves);
    t8.setTypeface(tf);
    t9 = (TextView) findViewById(R.id.purpose);
    t9.setTypeface(tf);

    e1 = (EditText) findViewById(R.id.name_ip);
    e3 = (EditText) findViewById(R.id.age_ip);
    e4 = (EditText) findViewById(R.id.hair_c_ip);
    e5 = (EditText) findViewById(R.id.eye_c_ip);
    e6 = (EditText) findViewById(R.id.prof_ip);
    e7 = (EditText) findViewById(R.id.dreams_ip);
    e8 = (EditText) findViewById(R.id.peeves_ip);
    e9 = (EditText) findViewById(R.id.purpose_ip);

    rg = (RadioGroup) findViewById(R.id.gender_ip);
    int selected_ID = rg.getCheckedRadioButtonId();
    r2 = (RadioButton) findViewById(selected_ID);

}


public void nextScreen(View view) {
    String radioText = null;
    CharacterTable charTable = new CharacterTable(this);
    charTable.open();
    try {
        radioText = r2.getText().toString();
    } catch (Exception e) {
        e.printStackTrace();
        Log.i(TAG, "Radio Button Null Exception");
    }
    long rowID = charTable.createCharacter(e1.getText().toString(), radioText, e3.getText().toString(),
            e4.getText().toString(), e5.getText().toString(), e6.getText().toString(), e7.getText().toString(),
            e8.getText().toString(), e9.getText().toString());

    if (rowID != -1)
        Toast.makeText(S4CharacterForm.this, "Character Created", Toast.LENGTH_SHORT).show();
    else
        Toast.makeText(S4CharacterForm.this, "Something went wrong", Toast.LENGTH_SHORT).show();
    charTable.close();
    Intent intent = new Intent(S4CharacterForm.this, S5.class);
    startActivity(intent);
}
}

I've looked through a bunch of similar questions here but can't find a solution that works. Does anybody know what I should correct?
Edit
Changed the names of the columns like this:
public static final String KEY_ROWID = "_id";
public static final String COLUMN_NAME_NAME = "name";
public static final String COLUMN_NAME_GENDER = "gender";
public static final String COLUMN_NAME_AGE = "age";
public static final String COLUMN_NAME_HAIRCOLOR = "hair_colour";
public static final String COLUMN_NAME_EYECOLOR = "eye_colour";
public static final String COLUMN_NAME_PROF = "profession";
public static final String COLUMN_NAME_DREAMS = "dreams";
public static final String COLUMN_NAME_PEEVES = "pet_peeves";
public static final String COLUMN_NAME_PURPOSE = "purpose";

The logcat shows a new error:
04-12 01:08:25.644 32644-32644/com.miniproject32.writeup E/SQLiteLog: (1) table characters has no column named hair_colour
04-12 01:08:25.644 32644-32644/com.miniproject32.writeup E/SQLiteDatabase: Error inserting hair_colour=arshiya profession=arshiya eye_colour=arshiya age=22 dreams=arshiya name=arshiya  gender=null pet_peeves=arshiya purpose=arshiya
android.database.sqlite.SQLiteException: table characters has no column named hair_colour (code 1): , while compiling: INSERT INTO characters(hair_colour,profession,eye_colour,age,dreams,name,gender,pet_peeves,purpose) VALUES (?,?,?,?,?,?,?,?,?)

#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
(table characters has no column named hair_colour (code 1): , while compiling: INSERT INTO characters(hair_colour,profession,eye_colour,age,dreams,name,gender,pet_peeves,purpose) VALUES (?,?,?,?,?,?,?,?,?))

#################################################################


Adding a space before the table and database name worked for some reason

It could be related to the fact that you have spaces in your column names. Ensure your column names do not contain spaces, use underscore to separate the words.

Made the changes and double checked all the spaces. Now it says there are no such columns in the table
