CODEWITHSUNDEEP
androiddatabasesqlite
Friso
Friso

Reputation: 2428

How to solve a failure to insert something into SQLite db due to non-existing table?

I'm trying to insert a note into my Android SQLite database, but I get the following error message:

02-04 01:28:19.775 9521-9521/? E/SQLiteDatabase﹕ Error inserting text=Note 0 android.database.sqlite.SQLiteException: no such table: notes (code 1): , while compiling: INSERT INTO notes(text) VALUES (?)

This is my database adapter: 

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;

/**
 * Created by Friso on 15/2/1.
 */
public class DBAdapter {
    private final static String DATABASE_NAME = "db_aio_organiser";
    private final static int DATABASE_VERSION = 1;

    private final static String TABLE_NOTES = "notes";
    private final static String KEY_NOTES_ID = "id";
    private final static String KEY_NOTES_TEXT = "text";

    private final static String CREATE_TABLE_NOTES = "create table " + TABLE_NOTES +
            " (" + KEY_NOTES_ID + "integer primary key autoincrement, "
            + KEY_NOTES_TEXT + "text not null);";

    private final Context context;

    DatabaseHelper helper;
    SQLiteDatabase db;

    public DBAdapter(Context context) {
        this.context = context;
        helper = new DatabaseHelper(context);
    }

    public DBAdapter open() throws SQLException{
        db = helper.getWritableDatabase();
        return this;
    }

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

    public long insertNote(String text) {
        ContentValues values = new ContentValues();
        values.put(KEY_NOTES_TEXT, text);
        return db.insert(TABLE_NOTES, null, values);
    }

    public boolean deleteNote(long id) {
        return db.delete(TABLE_NOTES, KEY_NOTES_ID + "=" + id, null) > 0;
    }

    public Cursor getAllNotes() {
        return db.query(TABLE_NOTES, new String[] {KEY_NOTES_ID, KEY_NOTES_TEXT}, null, null, null, null, null);
    }

    public Cursor getNote(long id) {
        Cursor mCursor = db.query(true, TABLE_NOTES, new String[] {KEY_NOTES_ID, KEY_NOTES_TEXT},
                KEY_NOTES_ID + "=" + id, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    public boolean updateNote(long id, String text) {
        ContentValues values = new ContentValues();
        values.put(KEY_NOTES_TEXT, text);
        return db.update(TABLE_NOTES, values, KEY_NOTES_ID + "=" + id, null) > 0;
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {
        private DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            try {
                db.execSQL(CREATE_TABLE_NOTES);
            } catch (SQLException e) {
                Log.e("DBAdapter", "failed to create table notes");
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        }
    }
}

What did I do wrong?

Upvotes: 0

Views: 1426

Answers (3)

laalto
laalto

Reputation: 152817

  1. You have syntax errors in your CREATE TABLE: missing whitespace between column names and types. Change to

    "create table " + TABLE_NOTES +
    " (" + KEY_NOTES_ID + " integer primary key autoincrement, "
    + KEY_NOTES_TEXT + " text not null);";

    Specifically, the autoincrement is misplaced, you can only use the keyword with integer primary key and not something like idinteger primary key. The text column is not syntactically incorrect but not what you want without the space.

  2. You're ignoring any errors in your CREATE TABLE by having try-catch in onCreate(). Since the method returns normally, the database is considered to successfully set up. Remove the try-catch.

  3. Uninstall your app so that onCreate() is run again.

Upvotes: 1

rogerwar
rogerwar

Reputation: 338

you have error in your sql statements try this

private final static String CREATE_TABLE_NOTES = "create table "+     TABLE_NOTES +
        " (" + KEY_NOTES_ID + " integer primary key autoincrement, "
        + KEY_NOTES_TEXT + " text not null)";

Upvotes: 1

cd141186
cd141186

Reputation: 9

Looks like there might be errors in your setup expression for the table. Try:

private final static String CREATE_TABLE_NOTES = "CREATE TABLE " + TABLE_NOTES + " (" + KEY_NOTES_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + KEY_NOTES_TEXT + "  TEXT NOT NULL);";

(I think the setup expression needs to be in capitals, also you have to be careful about where you put spaces, there were a couple missing in your code).

Really hope that works for you, SQLite can be a real pest when you're looking for one little error!

Upvotes: 1

Related Questions