java - SQlite 列不存在

Question

已解决，Ahmad 帖子中的解决方案，更改错误语法并增加数据库版本/或重新安装

我看到了很多有关该问题的主题，但我无法修复我的代码。有两个类:Main 和 DatabaseHandler。创建数据库表的目的(姓名、办公室、照片、 promise 、声明、 Activity )但是，最后一列“Activity ”无法识别并引发错误:“SQLite 返回:错误代码 =1，消息 =表候选者没有名为 Activity 的列”

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

您的 SQL 语法错误。将 CREATE_CANDIDATES_TABLE 更改为:

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" + ")";