AS SQLite "No such column" when column exists-CodePudding

I'm creating a quiz app, and so I created a question table in SQLite database. When I'm running the project it throws the following exception: E/SQLiteLog: (1) no such column: Question in "SELECT _id, Question, option1, option2, option3, option4, Answer number FROM Quiz_Questions"

And a fatal compiler exception: "java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.quiz/com.example.quiz.QuizActivity}: android.database.sqlite.SQLiteException: no such column: Question (code 1 SQLITE_ERROR): , while compiling: SELECT _id, Question, option1, option2, option3, option4, Answer number FROM Quiz_Questions"

Here is the code:

public class DB_Helper  extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "PRO_QUIZ";
public static final  int DATABASE_VERSION = 1;
private SQLiteDatabase db;


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


public void onCreate(SQLiteDatabase  db) {

    this.db = db;
    // Designing the sql table and naming the columns and what values do they hold
    final String CREATE_QUESTIONS_TABLE = "CREATE TABLE  "  
            QuestionTable.Table_Name   " ( "  
            QuestionTable._ID   " INTEGER PRIMARY KEY AUTOINCREMENT, " 
            QuestionTable.Column_Question   "TEXT, "  
            QuestionTable.Column_Option1   "TEXT, "  
            QuestionTable.Column_Option2   "TEXT, "  
            QuestionTable.Column_Option3   "TEXT, "  
            QuestionTable.Column_Option4   "TEXT, "  
            QuestionTable.Column_Correct_Ans   "INTEGER "  
            ")";
    db.execSQL(CREATE_QUESTIONS_TABLE);

And here is the Contract class for the case it's necessary:

public QuizContract(){}

 public static class QuestionTable implements BaseColumns{
    public static final String Table_Name = "Quiz_Questions";
    public static final String Column_Question = "Question";
    public static final String Column_Option1 = "option1";
    public static final String Column_Option2 = "option2";
    public static final String Column_Option3 = "option3";
    public static final String Column_Option4 = "option4";
    public static final String Column_Correct_Ans = "Answer number";

Query: public ArrayList getAllQuestions() {

    ArrayList<QuestionsDataBase> questionsList = new ArrayList<>();
    db = getReadableDatabase();
    String[] Projection ={
            QuestionTable._ID,
            QuestionTable.Column_Question,
            QuestionTable.Column_Option1,
            QuestionTable.Column_Option2,
            QuestionTable.Column_Option3,
            QuestionTable.Column_Option4,
            QuestionTable.Column_Correct_Ans
    };
    Cursor c = db.query(QuestionTable.Table_Name,Projection,null,null,null,null,null);
    if(c.moveToFirst()){
        do{
            QuestionsDataBase questions = new QuestionsDataBase();
            questions.setQuestion(c.getString(c.getColumnIndexOrThrow(QuestionTable.Column_Question)));
            questions.setQuestion(c.getString(c.getColumnIndexOrThrow(QuestionTable.Column_Option1)));
            questions.setQuestion(c.getString(c.getColumnIndexOrThrow(QuestionTable.Column_Option2)));
            questions.setQuestion(c.getString(c.getColumnIndexOrThrow(QuestionTable.Column_Option3)));
            questions.setQuestion(c.getString(c.getColumnIndexOrThrow(QuestionTable.Column_Option4)));
            questions.setQuestion(c.getString(c.getColumnIndexOrThrow(QuestionTable.Column_Correct_Ans)));
            questionsList.add(questions);

        }while(c.moveToNext());
    }
    c.close();
    return questionsList;
}

CodePudding user response：

You forgot to put a space between the column name and type. so "TEXT, " needs to be " TEXT, " and "INTEGER " needs to be " INTEGER "

CodePudding user response：

You are using an illegal column name: "Answer number". And I'd suggest to use all lower-case table names with an underscore, for the sake of simplicity - because else this might only drive you crazy, the more complex it may get (asking on SO whenever you cannot find a column isn't the solution).