SQLite database not inserting data Android [duplicate]

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
– Bhargav, Commented Dec 1, 2015 at 6: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
– Matt, Commented Dec 1, 2015 at 6:29
then you should uninstall your app and again install a fresh .apk built. — M D
– M D, Commented Dec 1, 2015 at 6:30
@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
– Bhargav, Commented Dec 1, 2015 at 7:00

Karakuri · Accepted Answer · 2015-12-01 06:37:16Z

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.

Madhur · Accepted Answer · 2015-12-01 06:29:50Z

1

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

answered Dec 1, 2015 at 6:29

Madhur

3,37322 silver badges29 bronze badges

Comments

Bhargav · Accepted Answer · 2015-12-01 06:34:43Z

1

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

answered Dec 1, 2015 at 6:34

Bhargav

8,3076 gold badges43 silver badges63 bronze badges

1 Comment

Matt Over a year ago

Sorry, but the settings menu under file right? I don't see an app or applications option

Collectives™ on Stack Overflow

SQLite database not inserting data Android [duplicate]

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related