android sqlite data not adding

Question

I wish to add some data to the database in android. Here is my code

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteException;
import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper {

  public static final String TABLE_ADD = "inventory";
  public static final String COLUMN_ID = "_id";
  public static final String COLUMN_QUANTITY = "quantity";
  public static final String COLUMN_PRICE_PER_UNIT="price_per_unit";
  public static final String COLUMN_TIMESTAMP="added_at";
  public static final String COLUMN_ADD_TYPE="add_type";
  
  private static final String DATABASE_NAME = "inventory.db";
  private static final int DATABASE_VERSION = 1;

  // Database creation sql statement
  private static final String DATABASE_CREATE = "create table "
      + TABLE_ADD + "(" + COLUMN_ID
      + " text primary key, " + COLUMN_QUANTITY
      + " integer not null, "+COLUMN_PRICE_PER_UNIT
      +" integer not null, "+COLUMN_TIMESTAMP+" text, "+
      COLUMN_ADD_TYPE+" text not null );";

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

  @Override
  public void onCreate(SQLiteDatabase database) {
    database.execSQL(db_create);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(MySQLiteHelper.class.getName(),
        "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_ADD);
    onCreate(db);
  }

}

UPDATE: Bellow is my log after editing my table name to inventory, and column timestamp to added_at as suggested in the answers:

09-09 12:37:58.289: E/SQLiteLog(1423): (1) no such table: inventory
09-09 12:37:58.489: D/dalvikvm(1423): GC_FOR_ALLOC freed 396K, 12% free 3541K/4012K, paused 100ms, total 108ms
09-09 12:37:58.519: E/SQLiteDatabase(1423): Error inserting timestamp=2014-09-09 12:37:57 add_type=new entry price_per_unit=90 quantity=9 _id=Product
09-09 12:37:58.519: E/SQLiteDatabase(1423): android.database.sqlite.SQLiteException: no such table: inventory (code 1): , while compiling: INSERT INTO inventory(timestamp,add_type,price_per_unit,quantity,_id) VALUES (?,?,?,?,?)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at com.example.invent2.DataSource.insertValues(DataSource.java:43)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at com.example.invent2.AddActivity$2.onClick(AddActivity.java:53)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.view.View.performClick(View.java:4424)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.view.View$PerformClick.run(View.java:18383)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.os.Handler.handleCallback(Handler.java:733)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.os.Handler.dispatchMessage(Handler.java:95)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.os.Looper.loop(Looper.java:137)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at android.app.ActivityThread.main(ActivityThread.java:4998)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at java.lang.reflect.Method.invokeNative(Native Method)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at java.lang.reflect.Method.invoke(Method.java:515)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:777)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:593)
09-09 12:37:58.519: E/SQLiteDatabase(1423):     at dalvik.system.NativeStart.main(Native Method)

It says no such table inventory.

You have no table called `inventory`: `public static final String TABLE_ADD = "add";` — Phantômaxx, Sep 09 '14 at 17:17
After fixing `onCreate()` e.g. uninstall your app so that the database gets recreated. http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run — laalto, Sep 10 '14 at 04:20

score 1 · Accepted Answer · answered Sep 09 '14 at 16:13

The problem is, that add is an SQLite Keyword. You'll have to escape it in your querys (the insert and the select):

INSERT INTO "add" (timestamp,add_type,price_per_unit,quantity,_id) VALUES (?,?,?,?,?)

Generally, it's a good idea to name tables and columns after their logical content, not their datatyp. I'd rename your timestamp column to something like added_at. The table-name should hold more information on exactly what is being added to it. For example AddArticle.

score 0 · Answer 2 · answered Sep 09 '14 at 16:13

Change the table name from ADD to something else. The SQL standard specifies a huge number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. ADD is one such keyword. Reference here.

android sqlite data not adding

2 Answers2