1

I have an object that that is called to add a row to the database but for reasons I can't figure out, it is attempting to insert it not in the order I tell it to.

Here is the code for the method

public Coin createCoin(String create_year, String create_specialty, String create_mintage,  String create_mint, int create_have) {
    ContentValues values = new ContentValues();
    values.put(MySQLiteHelper.MINT, create_mint);
    values.put(MySQLiteHelper.YEAR, create_year);
    values.put(MySQLiteHelper.MINTAGE, create_mintage);
    values.put(MySQLiteHelper.HAVE, create_have);
    values.put(MySQLiteHelper.SPECIALTY, create_specialty);
    //(String table, String nullColumnHack, ContentValues values)
    long insertId = database.insert(MySQLiteHelper.TABLE_PENNY_FLYING_EAGLE, null,
            values);
    //(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
    Cursor cursor = database.query(MySQLiteHelper.TABLE_PENNY_FLYING_EAGLE,
            allColumns, MySQLiteHelper.YEAR, null,
            null, null, null);
    cursor.moveToFirst();
    Coin newCoin = cursorToCoin(cursor);
    cursor.close();
    return newCoin;
}
public ArrayList<Coin> getAllCoins() {
    ArrayList<Coin> coins = new ArrayList<Coin>();
    Cursor cursor = database.query(MySQLiteHelper.TABLE_PENNY_FLYING_EAGLE,
            allColumns, null, null, null, null, null);
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        Coin coin = cursorToCoin(cursor);
        coins.add(coin);
        cursor.moveToNext();
    }
    // make sure to close the cursor
    cursor.close();
    return coins;
}
private Coin cursorToCoin(Cursor cursor) {
    Coin coin = new Coin();
    coin.setYear(cursor.getString(0));
    coin.setSpecialty(cursor.getString(1));
    coin.setMintage(cursor.getString(2));
    return coin;
}

Now I would think it would attempt to insert the data as 

INSERT INTO penny_flying_eagle(mint,year,mintage,have,specialty) VALUES (?,?,?,?,?)

but instead it is giving me an error

66-1166/com.example.dommol.testlist E/SQLiteLog﹕ (1) table penny_flying_eagle has no column named mint
   02-03 21:31:42.097    1166-1166/com.example.dimmel.testlist E/SQLiteDatabase﹕ Error inserting mint=0 year=1858 mintage=24,600,000 specialty=Not NULL have=D
android.database.sqlite.SQLiteException: table penny_flying_eagle has no column named mint (code 1): , while compiling: INSERT INTO penny_flying_eagle(mint,year,mintage,specialty,have) VALUES (?,?,?,?,?)

My thought is it is telling me there is no column named mint because it is attempting to put mint in the place of year and not finding the column there. Why is this trying to insert the values in a different order than the one I specified?

Edit: code for create table

public class MySQLiteHelper extends SQLiteOpenHelper {

public static final String TABLE_PENNY_FLYING_EAGLE = "penny_flying_eagle";
public static final String YEAR = "year";
public static final String SPECIALTY = "specialty";
public static final String MINTAGE = "mintage";
public static final String HAVE = "have";
public static final String MINT = "mint";

private static final String DATABASE_NAME = "coins.db";
private static final int DATABASE_VERSION = 1;

// Database creation sql statement
private static final String DATABASE_CREATE = "create table "
        + TABLE_PENNY_FLYING_EAGLE + "("
        + MINT + " text, "
        + YEAR + " text, "
        + MINTAGE + " text not null, "
        + SPECIALTY + " text, "
        + HAVE + " integer, "
        + "primary key (" + YEAR + ", " + SPECIALTY + ", " + MINTAGE + ") on conflict ignore );";

public MySQLiteHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase database) {
    database.execSQL(DATABASE_CREATE);
Dommol
  • 191
  • 1
  • 17
  • maybe its sensitive case. i think to change your mint to MINT – Randyka Yudhistira Feb 04 '15 at 02:43
  • Can you show us your sql to create that table? – Lawrence Choy Feb 04 '15 at 02:44
  • there is the function that creates the table. It can't be a case issue, it pulls the name of the column from the same class that creates the table – Dommol Feb 04 '15 at 02:47
  • Have you actually checked that the column mint does actually exist in the database by pulling the db off the device and looking at it? Don't assume your tools are telling you incorrectly – jamesc Feb 04 '15 at 03:57
  • I don't know how the row wouldn't exist. The create statement uses the exact same name as the insert statement. – Dommol Feb 04 '15 at 23:12

1 Answers1

3

This issue will cause due to two things :

  1. If you renamed the column name. (that is mint column) because once the database is created it will not alter the table when changed the column name.

  2. If you added new column name for the already existing table.(that is mint column)

Solution:

  1. Change(Upgrade) the DATABASE_VERSION from 1 to 2 or so on.

  2. Delete the database from data/data/"your app name"/databases/"your database" and run the application so that the new database will be created.

Community
  • 1
  • 1
W I Z A R D
  • 1,224
  • 3
  • 17
  • 44
  • I'm running it on the emulator, shouldn't it recreate and repopulate the database every time I run it or restart the emulator? – Dommol Feb 04 '15 at 12:03
  • m not saying you to recreate db every time you run the project m just saying you, if you have altered the table then recreate the db or just change the database version that is 1 to 2 or anything u wish. – W I Z A R D Feb 04 '15 at 12:12
  • I think were having a misunderstanding, when I shut down the emulator doesn't that delete the db? It can't be that the issue is me attempting to add a new column to an existing db because there shouldn't be an existing db – Dommol Feb 04 '15 at 14:12
  • yes you are right so i told u to delete the db manually or change (upgrade) the database version. – W I Z A R D Feb 05 '15 at 04:02
  • 1
    Shutting down the emulator will not delete the db. You need to delete it yourself (either by uninstall the app or write something in your code). You should consider the emulator as a real device, which will retain everything you install in it. – goofyz Feb 05 '15 at 04:09
  • Thanks @goofyz, I was making faulty assumptions. After deleting the database the errors went away – Dommol Feb 05 '15 at 12:29