SQLite database not inserting data Android

Question

I'm trying to follow the instructions in this link: http://www.androiddesignpatterns.com/2012/05/correctly-managing-your-sqlite-database.html under approach #1 to access my database from different activities. However the data is not being inserted and I'm receiving a couple of errors.

the main class I'm using to insert the data:

import android.content.Intent;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class newCharacter extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_new_character);
        Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);

        Button submit_button = (Button) findViewById(R.id.char_submit);

        DatabaseHelper.getInstance(getApplicationContext());


        EditText name_text = (EditText) findViewById(R.id.nameText);
        EditText health_text = (EditText) findViewById(R.id.editText);
        EditText strength_text = (EditText) findViewById(R.id.editText2);
        EditText agility_text = (EditText) findViewById(R.id.editText3);
        EditText intelligence_text = (EditText) findViewById(R.id.editText4);
        EditText charisma_text = (EditText) findViewById(R.id.editText5);
        EditText endurance_text = (EditText) findViewById(R.id.editText6);
        EditText carry_weight_text = (EditText) findViewById(R.id.editText7);
        EditText zprep_text = (EditText) findViewById(R.id.editText8);
        EditText inventory_text = (EditText) findViewById(R.id.editText9);

        final String name = name_text.toString();
        final String health = health_text.toString();
        final String strength = strength_text.toString();
        final String agility = agility_text.toString();
        final String intelligence = intelligence_text.toString();
        final String charisma = charisma_text.toString();
        final String endurance = endurance_text.toString();
        final String carryweight = carry_weight_text.toString();
        final String zprep = zprep_text.toString();
        final String inventory = inventory_text.toString();

        submit_button.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {

               ERROR HERE boolean isInserted = DatabaseHelper.getInstance(getApplicationContext()).insertData(name, health, strength,
                        endurance, carryweight, agility, intelligence, charisma, zprep, inventory);

                if (isInserted == true)
                    Toast.makeText(newCharacter.this, "Data Inserted", Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(newCharacter.this, "Data NOT Inserted", Toast.LENGTH_LONG).show();



            }

        });
    }

}

My DatabaseHelper:

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by Matt on 11/23/2015.
 */
public class DatabaseHelper extends SQLiteOpenHelper {

    private static DatabaseHelper sInstance;

    public static final String DATABASE_NAME = "character_info.db";
    public static final String TABLE_NAME = "characters_table";
    public static final String COL_1 = "NAME";
    public static final String COL_3 = "HEALTH";
    public static final String COL_2 = "STRENGTH";
    public static final String COL_4 = "ENDURANCE";
    public static final String COL_5 = "CARRY_WEIGHT";
    public static final String COL_6 = "AGILITY";
    public static final String COL_7 = "INTELLIGENCE";
    public static final String COL_8 = "CHARISMA";
    public static final String COL_9 = "ZPREP";
    public static final String COL_10 = "INVENTORY";

    public static synchronized DatabaseHelper getInstance(Context context) {

        if (sInstance == null) {
            sInstance = new DatabaseHelper(context.getApplicationContext());
        }
        return sInstance;
    }

    private DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String SQL_String = "create table " + TABLE_NAME +" (NAME TEXT PRIMARY KEY, HEALTH TEXT, STRENGTH TEXT" +
                ", ENDURANCE TEXT, CARRY_WEIGHT TEXT, AGILITY TEXT, INTELLIGENCE TEXT, CHARISMA TEXT, ZPREP TEXT, INVENTORY TEXT)";

        db.execSQL(SQL_String);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
        onCreate(db);
    }

    public boolean insertData(String name, String health, String strength, String endurance, String carryweight,
                              String agility, String intelligence, String charisma, String zprep, String inventory) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_1, name);
        contentValues.put(COL_2, health);
        contentValues.put(COL_3, strength);
        contentValues.put(COL_4, endurance);
        contentValues.put(COL_5, carryweight);
        contentValues.put(COL_6, agility);
        contentValues.put(COL_7, intelligence);
        contentValues.put(COL_8, charisma);
        contentValues.put(COL_9, zprep);
        contentValues.put(COL_10, inventory);
        ERROR HERE: long result = db.insert(TABLE_NAME, null, contentValues);

        if (result == -1)
            return false;
        else
            return true;

    }
}

logcat:

12-01 01:05:45.049 274-274/proctor.csit.project E/Database: Error inserting INVENTORY=android.support.v7.widget.AppCompatEditText@45f78508 INTELLIGENCE=android.support.v7.widget.AppCompatEditText@45f9e0f0 CHARISMA=android.support.v7.widget.AppCompatEditText@45f7ab58 NAME=android.support.v7.widget.AppCompatEditText@45f663f0 ZPREP=android.support.v7.widget.AppCompatEditText@45f7ed60 HEALTH=android.support.v7.widget.AppCompatEditText@45f8dfd0 CARRY_WEIGHT=android.support.v7.widget.AppCompatEditText@45f634f0 ENDURANCE=android.support.v7.widget.AppCompatEditText@45f8ef60 STRENGTH=android.support.v7.widget.AppCompatEditText@45f7dda8 AGILITY=android.support.v7.widget.AppCompatEditText@45f9db10
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: android.database.sqlite.SQLiteException: table characters_table has no column named INVENTORY: , while compiling: INSERT INTO characters_table(INVENTORY, INTELLIGENCE, CHARISMA, NAME, ZPREP, HEALTH, CARRY_WEIGHT, ENDURANCE, STRENGTH, AGILITY) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:36)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1145)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1536)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1410)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at proctor.csit.project.DatabaseHelper.insertData(DatabaseHelper.java:70)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at proctor.csit.project.newCharacter$1.onClick(newCharacter.java:55)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.view.View.performClick(View.java:2408)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.view.View$PerformClick.run(View.java:8816)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.os.Handler.handleCallback(Handler.java:587)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.os.Handler.dispatchMessage(Handler.java:92)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.os.Looper.loop(Looper.java:123)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at android.app.ActivityThread.main(ActivityThread.java:4627)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at java.lang.reflect.Method.invokeNative(Native Method)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at java.lang.reflect.Method.invoke(Method.java:521)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database:     at dalvik.system.NativeStart.main(Native Method)

I have put notes next to the apparent errors in my java code. My first time using sqlite and I'm pretty lost on this, so any help is appreciated!

Goto settings then goto app, then goto `all` tab, then select your application after that click on clear data, then try running the app again it should work — Bhargav, Dec 01 '15 at 06:29
@M D mmm yes, I think, I had intended to add it originally, but forgot it, so I had to implement it a little while ago. — Matt, Dec 01 '15 at 06:29
then you should `uninstall` your app and again install a fresh .apk built. — M D, Dec 01 '15 at 06:30
@MD you dont have to reinstall the app, just clearing data for the app is enough — Bhargav, Dec 01 '15 at 06:37
@Bhargav ya but it's not working all time. I faced the same situation in past — M D, Dec 01 '15 at 06:39
@Matt you need to change the title of the question from not inserting data when using singleton to just not inserting data. This problem has nothing to do with using a singleton sqliteopenhelper class — Bhargav, Dec 01 '15 at 07:00

Karakuri · Accepted Answer · 2015-12-01T06:37:16.420

When you change your database schema, you need to increase the version number and implement an upgrade in onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion). In your case, it would probably look something like this:

private static final int VERSION = 2; // increased from 1

private DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, VERSION);
}

...

@Override
public void onUpdgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion) {
        case 1:
            db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN INVENTORY TEXT");
        // intentionally fall through to other cases
    }
}

This supports the case where a user downloads an update of your app and their database has a schema that is out-of-date.

If your app isn't released yet, you can get away with making the change to the schema without doing what I wrote above, and then simply clearing the app data (or uninstalling and reinstalling, which has the same effect). This will cause your database to be created anew and thus it will execute the CREATE sql again.

score 1 · Answer 2 · answered Dec 01 '15 at 06:29

1

if you add inventory column recently then Uninstall the app from phone or emulator you use and try new!

answered Dec 01 '15 at 06:29

Madhur

3,303
20
29

score 1 · Answer 3 · answered Dec 01 '15 at 06:34

Goto settings then goto app, then goto all tab, then select your application after that click on clear data, then try running the app again it should work.

This error occurred because the onCreate of your sqliteopenhelper class isn't called after you added the inventory table. Sqliteopenheler's onCreate is not called when there already exists a database with the name. The onUpgrade is also not called because you haven't incremented the version number in the sqliteopenhelper, therefore your table inventory is not created and hence you get the error.

So usually if your application is still under development, and you are the only developer working on it, then what you can do is just clear data for the application which will delete all data (including your database) and hence when you run the application again the onCreate will be called. But if your application is in production you need to increase the version number of your application and put the code for upgrade logic in onUpgrade

Sorry, but the settings menu under file right? I don't see an app or applications option — Matt, Dec 01 '15 at 06:45

SQLite database not inserting data Android

3 Answers3