java Android sqlite 数据库错误 SQLiteException 没有这样的列

Android sqlite database error SQLiteException no such column

提问by user1576752

When opening my activity where my database is being created, it immediately crashes with the error SQLiteException: no such column. I'm stumped on what to do as SQLite is still very much a mystery to me.

打开正在创建我的数据库的活动时，它立即崩溃并显示错误 SQLiteException: no such column。我很困惑该怎么做，因为 SQLite 对我来说仍然是一个谜。

*New error, this time I can create the workout note, but once I click to edit it, I get another no such column exists, this time squatLabel. But it does exist....*

*新错误，这次我可以创建锻炼记录，但是一旦我点击编辑它，我得到另一个不存在这样的列，这次是squatLabel。但它确实存在....*

Edit: The only error I am getting at this point is the no such column exists: squatLabel

编辑：此时我遇到的唯一错误是不存在这样的列：squatLabel

import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.ContextMenu.ContextMenuInfo;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.AdapterView.AdapterContextMenuInfo;

public class WorkoutList extends ListActivity {
    private static final int ACTIVITY_CREATE=0;
    private static final int ACTIVITY_EDIT=1;

    private static final int INSERT_ID = Menu.FIRST;
    private static final int DELETE_ID = Menu.FIRST + 1;

    private StrongDbAdapter mDbHelper;


    /** Called when the 5x5 button is pressed in first activity */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.workout_list);
        mDbHelper = new StrongDbAdapter(this);
        mDbHelper.open();
        fillData();
        registerForContextMenu(getListView());
    }

    private void fillData() {
        Cursor NotesCursor = mDbHelper.fetchAllNotes();

        // Get all of the rows from the database and create the item list
        NotesCursor = mDbHelper.fetchAllNotes();
        startManagingCursor(NotesCursor);

        // Create an array to specify the fields we want to display in the list (only TITLE)
        String[] from = new String[]{StrongDbAdapter.KEY_TITLE,};

        // and an array of the fields we want to bind those fields to (in this case just text1)
        int[] to = new int[]{R.id.workout_row };

        // Now create a simple cursor adapter and set it to display
        SimpleCursorAdapter notes = 
            new SimpleCursorAdapter(this, R.layout.workout_row, NotesCursor, from, to);
        setListAdapter(notes);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        super.onCreateOptionsMenu(menu);
        menu.add(0, INSERT_ID, 0, R.string.menu_insert);
        return true;
    }

    @Override
    public boolean onMenuItemSelected(int featureId, MenuItem item) {
        switch(item.getItemId()) {
            case INSERT_ID:
                createNote();
                return true;
        }

        return super.onMenuItemSelected(featureId, item);
    }

    @Override
    public void onCreateContextMenu(ContextMenu menu, View v,
            ContextMenuInfo menuInfo) {
        super.onCreateContextMenu(menu, v, menuInfo);
        menu.add(0, DELETE_ID, 0, R.string.menu_delete);
    }

    @Override
    public boolean onContextItemSelected(MenuItem item) {
        switch(item.getItemId()) {
            case DELETE_ID:
                AdapterContextMenuInfo info = (AdapterContextMenuInfo) item.getMenuInfo();
                mDbHelper.deleteNote(info.id);
                fillData();
                return true;
        }
        return super.onContextItemSelected(item);
    }

    private void createNote() {
        Intent i = new Intent(this, WorkoutEdit.class);
        startActivityForResult(i, ACTIVITY_CREATE);
    }

    @Override
    protected void onListItemClick(ListView l, View v, int position, long id) {
        super.onListItemClick(l, v, position, id);

        Intent i = new Intent(this, WorkoutEdit.class);
        i.putExtra(StrongDbAdapter.KEY_ROWID, id);

        startActivityForResult(i, ACTIVITY_EDIT);
    }

    @Override
    protected void onActivityResult(int requestCode, int resultCode, Intent intent) {
        super.onActivityResult(requestCode, resultCode, intent);
        fillData();
    }
}

Here is some of my WorkoutEdit class where problems seem to be happening.

这是我的一些 WorkoutEdit 课程，其中似乎发生了问题。

import android.app.Activity;
import android.app.AlertDialog;
import android.content.Context;
import android.content.DialogInterface;
import android.database.Cursor;
import android.os.Bundle;
import android.text.InputType;
import android.view.View;
import android.view.WindowManager;
import android.widget.Button;
import android.widget.EditText;
import android.widget.RadioGroup;
import android.widget.RadioGroup.OnCheckedChangeListener;
import android.widget.TextView;
import android.widget.ViewFlipper;

public class WorkoutEdit extends Activity {

    public TextView mTitleText;
    //private EditText mBodyText;
    private Long mRowId;
    private StrongDbAdapter mDbHelper;
    private ViewFlipper viewFlipper;
    public TextView squats;
    public boolean workoutA;
    public String workoutState;
    //private double rowId = mRowId;
    public String squatLabel;
    public Button confirmButton;
    private Long prevId ;





    private void populateFields() {
        if (mRowId != null) {
            Cursor note = mDbHelper.fetchNote(mRowId);
            startManagingCursor(note);
            //mTitleText.setText(note.getString(
                    //note.getColumnIndexOrThrow(StrongDbAdapter.KEY_TITLE)));
            workoutState=(note.getString(
                    note.getColumnIndexOrThrow(StrongDbAdapter.KEY_TITLE)));
            squats.setText(note.getString(note.getColumnIndexOrThrow(StrongDbAdapter.SQUAT_LABEL)));
        }
    }



    @Override
    protected void onSaveInstanceState(Bundle outState) {
        super.onSaveInstanceState(outState);
        saveState();
        outState.putSerializable(StrongDbAdapter.KEY_ROWID, mRowId);
    }

    @Override
    protected void onPause() {
        super.onPause();
        saveState();
    }

    @Override
    protected void onResume() {
        super.onResume();
        populateFields();
    }



    //Saves all the data to the database
    private void saveState() {
        String title = workoutState;
        String squatLabel = squats.getText().toString();


        if (mRowId == null) {
            long id = mDbHelper.createNote(title, squatLabel);
            if (id > 0) {
                mRowId = id;
            }
        } else {
            mDbHelper.updateNote(mRowId, title);
        }
    }       

    }
    @Override
    protected void onDestroy() {
        super.onDestroy();
        if (mDbHelper != null) {
            mDbHelper.close();
        }

    }



}

And here is my DbAdapter

这是我的 DbAdapter

*Updated Create Statement*

*更新了创建语句*

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


public class StrongDbAdapter {

    public static final String KEY_TITLE = "title";
    public static final String KEY_BODY = "body";
    public static final String KEY_ROWID = "_id";


    public static final String WORKOUT_STATE = "workoutState";

    public static final String SQUAT_LABEL = "squatLabel";

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


    private static final String DATABASE_NAME = "data";
    private static final String DATABASE_TABLE = "notes";
    private static final int DATABASE_VERSION = 2;



    /**
     * Database creation sql statement
     */
    private static final String DATABASE_CREATE =
        "create table notes (_id integer primary key autoincrement , "
        + "title text," +
        " squatLabel text, workoutState text );";


    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) {

            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 notes");
            onCreate(db);
        }
    }

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

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public StrongDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

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



    public long createNote(String title, String squatLabel) {


        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        //initialValues.put(WORKOUT_STATE, workoutState);
        initialValues.put(SQUAT_LABEL, squatLabel);

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

    /**
     * Delete the note with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteNote(long rowId) {

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

    /**
     * Return a Cursor over the list of all notes in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllNotes() {

        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,
                WORKOUT_STATE}, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the note that matches the given rowId
     * 
     * @param rowId id of note to retrieve
     * @return Cursor positioned to matching note, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchNote(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                    KEY_TITLE, WORKOUT_STATE}, KEY_ROWID + "='" + rowId+"'", null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    /**
     * Update the note using the details provided. The note to be updated is
     * specified using the rowId, and it is altered to use the title and body
     * values passed in
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateNote(long rowId, String title) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        //args.put(WORKOUT_STATE, workoutState);

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

And finally my LogCat:

最后是我的 LogCat：

*With new create statement, this time no such column squatLabel.*

*用新的create语句，这次没有squatLabel这样的列。*

08-05 18:57:05.336: E/AndroidRuntime(1131): FATAL EXCEPTION: main
08-05 18:57:05.336: E/AndroidRuntime(1131): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.anapoleon.android.stronglifts/com.anapoleon.android.stronglifts.WorkoutEdit}: java.lang.IllegalArgumentException: column 'squatLabel' does not exist
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1955)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1980)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.app.ActivityThread.access0(ActivityThread.java:122)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1146)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.os.Handler.dispatchMessage(Handler.java:99)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.os.Looper.loop(Looper.java:137)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.app.ActivityThread.main(ActivityThread.java:4340)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at java.lang.reflect.Method.invokeNative(Native Method)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at java.lang.reflect.Method.invoke(Method.java:511)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at dalvik.system.NativeStart.main(Native Method)
08-05 18:57:05.336: E/AndroidRuntime(1131): Caused by: java.lang.IllegalArgumentException: column 'squatLabel' does not exist
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:301)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at com.anapoleon.android.stronglifts.WorkoutEdit.populateFields(WorkoutEdit.java:199)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at com.anapoleon.android.stronglifts.WorkoutEdit.onCreate(WorkoutEdit.java:82)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.app.Activity.performCreate(Activity.java:4465)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1049)
08-05 18:57:05.336: E/AndroidRuntime(1131):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1919)
08-05 18:57:05.336: E/AndroidRuntime(1131):     ... 11 more

Thanks in advance! :)

提前致谢！:)

采纳答案by Taras Feschuk

It seams that your updated Create DB statement is correct.

看来您更新后的 Create DB 语句是正确的。

As regards to the next error: it seams that You never close your database and cursor. I usually use the next template when work with DB:

关于下一个错误：看来您从未关闭数据库和游标。使用 DB 时，我通常使用下一个模板：

openReadableDatabase(); // or openWritableDatabase();

// do some work with DB

closeDatabase();

Aforementioned methods may look like:

上述方法可能如下所示：

private void closeDatabase() {
    if (_db != null && _db.isOpen()) {
        _db.close();
    }
}

private void openReadableDatabase() {
    _db = getReadableDatabase();
}

private void openWritableDatabase() {
    _db = getWritableDatabase();
}

The same You can apply to the cursor object:

同样的你可以应用到游标对象上：

Cursor cursor = _db.query(TABLE_NAME, FROM, null, null, null, null, null);

// do some work with cursor

cursor.close();

FROM is a array of column names. Notice: the comma after the last column name is not a typo.

FROM 是一个列名数组。注意：最后一列名称后面的逗号不是拼写错误。

private static final String[] FROM = { COLUMN1_NAME, COLUMN2_NAME, };

Updated:
You should add a SquatLabel column to the data retrieving parameter in the db.query() method (and the same for the fetchNote method):

更新：
您应该在 db.query() 方法中的数据检索参数中添加一个 SquatLabel 列（对于 fetchNote 方法也是如此）：

public Cursor fetchAllNotes() {
    return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,
        SQUAT_LABEL, WORKOUT_STATE}, null, null, null, null, null);
}

回答by Barak

As others have said, you don't have that column defined in your DB.

正如其他人所说，您的数据库中没有定义该列。

What they've neglected to tell you is that you must do one of two things afteryou add the column to your db schema in the app.

他们忽略了告诉您的是，在将列添加到应用程序中的数据库架构后，您必须执行以下两项操作之一。

1) Delete and re-install your app, so the new schema for the DB gets used when the DB is created

1）删除并重新安装您的应用程序，以便在创建数据库时使用数据库的新架构

or

或者

2) Increment the version of your database so that the onUpgrade method gets called and updates your DB to the new schema

2) 增加数据库的版本，以便调用 onUpgrade 方法并将数据库更新为新模式

回答by Rob Wagner

Look at your create statement:

看看你的 create 语句：

private static final String DATABASE_CREATE =
        "create table notes (_id integer primary key autoincrement , "
        + "title text," +
        " squatLabel text );";

You don't have workoutStateincluded. Simply add the column into the create statement.

你没有workoutState包含。只需将该列添加到 create 语句中即可。