insert initial data to sql lite android

Question

I'm developing an expense tracker where I want to populate the DB with a few records when the application first start. I tried to call the method in my splash activity and add the data.! But when I clear my application data and start the application for the first time an error occurs saying that there is no such column.

The log cat error is as shown below.

06-30 18:09:58.834: E/SQLiteLog(18380): (1) no such column: exp_type
06-30 18:10:00.826: D/AndroidRuntime(18380): Shutting down VM
06-30 18:10:00.826: W/dalvikvm(18380): threadid=1: thread exiting with uncaught exception (group=0x416b8318)
06-30 18:10:00.856: E/AndroidRuntime(18380): FATAL EXCEPTION: main
06-30 18:10:00.856: E/AndroidRuntime(18380): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.sliit.expenseanalyzer/com.sliit.expenseanalyzer.SplashActivity}: android.database.sqlite.SQLiteException: no such column: exp_type (code 1): , while compiling: SELECT * FROM expense_type Order BY exp_type DESC
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2063)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2088)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.app.ActivityThread.access$600(ActivityThread.java:134)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1199)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.os.Handler.dispatchMessage(Handler.java:99)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.os.Looper.loop(Looper.java:137)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.app.ActivityThread.main(ActivityThread.java:4744)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at java.lang.reflect.Method.invokeNative(Native Method)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at java.lang.reflect.Method.invoke(Method.java:511)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at dalvik.system.NativeStart.main(Native Method)
06-30 18:10:00.856: E/AndroidRuntime(18380): Caused by: android.database.sqlite.SQLiteException: no such column: exp_type (code 1): , while compiling: SELECT * FROM expense_type Order BY exp_type DESC
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at com.sliit.expenseanalyzer.util.DatabaseHandler.getAllExpenseTypes(DatabaseHandler.java:218)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at com.sliit.expenseanalyzer.SplashActivity.onCreate(SplashActivity.java:29)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.app.Activity.performCreate(Activity.java:5008)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
06-30 18:10:00.856: E/AndroidRuntime(18380):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2027)
06-30 18:10:00.856: E/AndroidRuntime(18380):    ... 11 more

How can I populate the data when the application starts for the first time! It's only few records so I'm not worried about performance.

It would be a great help!

Thank you.

DATABASE CODE

public void onCreate(SQLiteDatabase db) {

        // Create user table
        String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "(" + KEY_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_NAME + " TEXT,"
                + KEY_EMAIL + " TEXT," + KEY_PW + " TEXT," + KEY_SESSION
                + " INTEGER" + ")";

        String CREATE_EXPENSE_TYPE_TABLE = "CREATE TABLE " + TABLE_EXPENSE_TYPE
                + " (" + KEY_EXPENSE_ID + " INTEGER AUTOINCREMENT" + KEY_EXPENSE_TYPE + " TEXT PRIMARY KEY" + ")";

        db.execSQL(CREATE_USER_TABLE);
        db.execSQL(CREATE_EXPENSE_TYPE_TABLE);
    }

Method I use to add records

public Long addExpenseType(String expense) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_EXPENSE_TYPE, expense);

        Long row = db.insert(TABLE_EXPENSE_TYPE, null, values);
        db.close();

        return row;
    }

String Values:

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "expenseManager";

    // Database Tables
    private static final String TABLE_USER = "user";
    private static final String TABLE_EXPENSE_TYPE = "expense_type";

    // User table columns
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "full_name";
    private static final String KEY_PW = "password";
    private static final String KEY_EMAIL = "email";
    private static final String KEY_SESSION = "session";

    // Expense type table columns
    private static final String KEY_EXPENSE_ID = "id";
    private static final String KEY_EXPENSE_TYPE = "exp_type";

check your table creation code. seems you are trying to access wrong column — stinepike, Jun 30 '13 at 12:44
While you are using constants, it does not tell us the real string values therein. You must supply that code. — Woot4Moo, Jun 30 '13 at 12:46
Missed a `,` after `AUTOINCREMENT` at ` " (" + KEY_EXPENSE_ID + " INTEGER AUTOINCREMENT" + KEY_EXPENSE_TYPE + " ` — S.D., Jun 30 '13 at 12:54

score 3 · Accepted Answer · answered Jun 30 '13 at 12:59

Make a habit of formatting your code well. The last column definition does not have a comma :

    String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER 
            + "(" 
            + KEY_ID      + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
            + KEY_NAME    + " TEXT, "
            + KEY_EMAIL   + " TEXT, " 
            + KEY_PW      + " TEXT, "
            + KEY_SESSION + " INTEGER "
            + ")";

    String CREATE_EXPENSE_TYPE_TABLE = "CREATE TABLE " + TABLE_EXPENSE_TYPE
            + "(" 
            + KEY_EXPENSE_ID   + " INTEGER AUTOINCREMENT, "
            + KEY_EXPENSE_TYPE + " TEXT PRIMARY KEY "
            + ")";

it's the comma that was missing! – Imesh Chandrasiri Jun 30 '13 at 13:11 — Imesh Chandrasiri, Jun 30 '13 at 13:11

Woot4Moo · Answer 2 · 2013-06-30T12:49:53.410

2

android.database.sqlite.SQLiteException: no such column: exp_type (code 1): , while compiling: SELECT * FROM expense_type Order BY exp_type DESC

This is your error:

String CREATE_EXPENSE_TYPE_TABLE = "CREATE TABLE " + TABLE_EXPENSE_TYPE
                + " (" + KEY_EXPENSE_ID + " INTEGER AUTOINCREMENT" +  
   KEY_EXPENSE_TYPE + " TEXT PRIMARY KEY" + ")

You need a space after INTEGER AUTOINCREMENT

KEY_EXPENSE_ID + " INTEGER AUTOINCREMENT" +  
       KEY_EXPENSE_TYPE

evaluates to:

"id integer autoincrementexp_type"

edited Jun 30 '13 at 12:49

answered Jun 30 '13 at 12:44

Woot4Moo

23,987
16
94
151

it should have since I create the table in the database handler class! – Imesh Chandrasiri Jun 30 '13 at 12:45
@DimalChandrasiri I don't see any code in your create statement that reference the value exp_type. All you have shown is references to constant strings, not the values of those strings. – Woot4Moo Jun 30 '13 at 12:47
I've added the strings! sorry for not adding them on the first place! – Imesh Chandrasiri Jun 30 '13 at 12:48

score 2 · Answer 3 · answered Jun 30 '13 at 12:52

try to add a flag in your user preferences/shared preferences to check the apps first run and then upon return you can decide if you want to create your db or read it !
have a look here http://www.vogella.com/articles/AndroidSQLite/article.html
EDIT
or just check if dbexists instead of user pref flag

Ayush · Answer 4 · 2013-06-30T12:58:58.067

private final String SAMPLE_DB_NAME = "TheftData";
    private final String SAMPLE_TABLE_NAME = "UserDetails";
    String CreateTable = "CREATE TABLE IF NOT EXISTS " +
            SAMPLE_TABLE_NAME +
            " (Username VARCHAR, Password VARCHAR," +                   
            " );";

Then in onCreate

protected void onCreate(Bundle savedInstanceState) {
try {
        sampleDB =  this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);

        sampleDB.execSQL(CreateTable);
        sampleDB.close();
        }

    catch(Exception e)
    {Toast.makeText(getApplicationContext(), " Error: "+e.getMessage(), Toast.LENGTH_LONG).show();
    sampleDB.close();
    }

and to add data

 public void insertData(String username,String password)
    {

        sampleDB =  this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);

        sampleDB.execSQL(CreateTable);
        sampleDB.execSQL("INSERT INTO " +
                SAMPLE_TABLE_NAME +
                " Values ('"+username+"','"+ password+"');");
        sampleDB.close();

    }

insert initial data to sql lite android

4 Answers4

Linked