SQlite column does not exist

Question

Solved, solution in Ahmad post, change wrong syntax and increase database version/or reinstall

I saw many topics about that problem but i cannot fix my code. There are two class: Main and DatabaseHandler. Purpose to create dataase table (name, office, photograph, promisses, statment, active) However, last colume "active" is not recognized and error is thrown: "SQLite returned: error code =1, msg =table candidates has not column named active"

public class DatabaseHandler extends SQLiteOpenHelper {
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "ElectionCandidates";

    // Candidates table name
    private static final String TABLE_CANDIDATES = "candidates";

    // Candidates Table Columns names
    private static final String KEY_NAME = "name";
    private static final String KEY_OFFICE = "office";
    private static final String KEY_PHOTO = "photograph";
    private static final String KEY_PROMISSES = "promisses";
    private static final String KEY_STATMENT = "statment";
    private static final String KEY_ACTIVE = "active";

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

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_CANDIDATES_TABLE = "CREATE TABLE " + TABLE_CANDIDATES
                + "(" + KEY_NAME + " TEXT," + KEY_OFFICE + " TEXT," + KEY_PHOTO
                + " TEXT" + KEY_PROMISSES + " TEXT" + KEY_STATMENT + " TEXT"
                + KEY_ACTIVE + " TEXT" + ")";
        db.execSQL(CREATE_CANDIDATES_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CANDIDATES);

        // Create tables again
        onCreate(db);
    }

    void addCandidate(Candidate candidate) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, candidate.get_name()); // Candidate Name
        values.put(KEY_OFFICE, candidate.get_office()); // Candidate Phone
        values.put(KEY_PHOTO, candidate.get_photograph());
        values.put(KEY_PROMISSES, candidate.get_promisses());
        values.put(KEY_STATMENT, candidate.get_statment());
        values.put(KEY_ACTIVE, 1);

        // Inserting Row
        db.insert(TABLE_CANDIDATES, null, values);
        db.close(); // Closing database connection
    }

    Candidate getCandidate(String name) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_CANDIDATES,
                new String[] { KEY_NAME, KEY_OFFICE, KEY_PHOTO, KEY_PROMISSES,
                        KEY_STATMENT, KEY_ACTIVE }, KEY_NAME + "=?",
                new String[] { String.valueOf(name) }, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        Candidate candidate = new Candidate(cursor.getString(0),
                cursor.getString(1), cursor.getString(2), cursor.getString(3),
                cursor.getString(4), Integer.parseInt(cursor.getString(5)));
        // return candidate
        return candidate;
    }

    public List<Candidate> getAllCandidates() {
        List<Candidate> candidateList = new ArrayList<Candidate>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_CANDIDATES;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Candidate candidate = new Candidate();
                candidate.set_name(cursor.getString(0));
                candidate.set_office(cursor.getString(1));
                candidate.set_photograph(cursor.getString(2));
                candidate.set_promisses(cursor.getString(3));
                candidate.set_statment(cursor.getString(4));
                candidate.set_active(Integer.parseInt(cursor.getString(5)));

                // Adding candidate to list
                candidateList.add(candidate);
            } while (cursor.moveToNext());
        }

        // return candidate list
        return candidateList;
    }

    public int updateCandidate(Candidate candidate) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, candidate.get_name()); // Candidate Name
        values.put(KEY_OFFICE, candidate.get_office()); // Candidate Phone
        values.put(KEY_PHOTO, candidate.get_photograph());
        values.put(KEY_PROMISSES, candidate.get_promisses());
        values.put(KEY_STATMENT, candidate.get_statment());
        values.put(KEY_ACTIVE, candidate.get_active());

        // updating row
        return db.update(TABLE_CANDIDATES, values, KEY_NAME + " = ?",
                new String[] { String.valueOf(candidate.get_name()) });
    }

    // Deleting single candidate
    public void deleteCandidate(Candidate candidate) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_CANDIDATES, KEY_NAME + " = ?",
                new String[] { String.valueOf(candidate.get_name()) });
        db.close();
    }

    // Getting candidates Count
    public int getCandidatesCount() {
        String countQuery = "SELECT  * FROM " + TABLE_CANDIDATES;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }
}


package com.s1042512.electionvoter;

import java.util.List;

import com.s1042512.electionvoter.Candidate;
import com.s1042512.electionvoter.DatabaseHandler;
import com.s1042512.electionvoter.R;

import android.os.Bundle;
import android.app.Activity;
import android.util.Log;

public class MainActivity extends Activity {
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        DatabaseHandler db = new DatabaseHandler(this);

        /**
         * CRUD Operations
         * */
        // Inserting Candidates
        Log.d("Insert: ", "Inserting ..");
        db.addCandidate(new Candidate("Scott Web", "Vice President", "Uglyface", "Promice To suck", "I am noob",0));
        db.addCandidate(new Candidate("Nyash Rush", "President", "Kavaiiii", "Promice To kill", "I am pro",1));

        // Reading all candidates
        Log.d("Reading: ", "Reading all candidates..");
        List<Candidate> candidates = db.getAllCandidates();       

        for (Candidate cn : candidates) {
            String log = "Name: "+cn.get_name()+" ,Office: " + cn.get_office() + " , Photo: " + cn.get_photograph()
                    + " , Promisses: " + cn.get_promisses()+ " , Statment: " + cn.get_statment();
                // Writing Candidates to log
        Log.d("Name: ", log);

        }
    }
}

Answer 1

I think your are missing the primary Key identification:

Try the following code:

db.execSQL("CREATE TABLE " + TABLE_CANDIDATES + " (id INTEGER PRIMARY KEY, name TEXT, office TEXT, photograph TEXT, promisses TEXT, statment TEXT, active TEXT)");

if it works with you then customize it to your requirements.

Note: also you defined active as text and you entered its value as integer you either define it in onCreat method for the database as INTEGER or you insert it as text.

Answer 2

Your SQL syntax is wrong. Change CREATE_CANDIDATES_TABLE to this:

String CREATE_CANDIDATES_TABLE = "CREATE TABLE " + TABLE_CANDIDATES
                + "(" + KEY_NAME + " TEXT, " + KEY_OFFICE + " TEXT, " + KEY_PHOTO
                + " TEXT, " + KEY_PROMISSES + " TEXT, " + KEY_STATMENT + " TEXT, "
                + KEY_ACTIVE + " TEXT" + ")";