Error in sqllite programming in android

Question

I am designing an app for messaging.i am using a sqlite database to store users information but i am getting this error logcat.

05-19 22:24:00.211: E/SQLiteLog(8815): (1) no such table: pendingintents
05-19 22:24:00.251: E/SQLiteDatabase(8815): Error inserting message=aaaaaaaas receivername=Abc minutes=23 _id=-1092327224 seconds=28 month=4 year=2013 frequency=15 mins day=19 hour=22 numbertosend=(880) 037-6666
05-19 22:24:00.251: E/SQLiteDatabase(8815): android.database.sqlite.SQLiteException: no such table: pendingintents (code 1): , while compiling: INSERT INTO pendingintents(message,receivername,minutes,_id,seconds,month,year,frequency,day,hour,numbertosend) VALUES (?,?,?,?,?,?,?,?,?,?,?)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at com.xxxx.DatabaseCreator.InitializePendingIntents(DatabaseCreator.java:64)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at com.xxxx.Scheduler.addToDatabase(Scheduler.java:314)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at com.xxxx.Scheduler.access$20(Scheduler.java:311)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at com.xxxx.Scheduler$4.onClick(Scheduler.java:188)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.view.View.performClick(View.java:4204)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.view.View$PerformClick.run(View.java:17355)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.os.Handler.handleCallback(Handler.java:725)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.os.Handler.dispatchMessage(Handler.java:92)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.os.Looper.loop(Looper.java:137)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at android.app.ActivityThread.main(ActivityThread.java:5041)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at java.lang.reflect.Method.invokeNative(Native Method)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at java.lang.reflect.Method.invoke(Method.java:511)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
05-19 22:24:00.251: E/SQLiteDatabase(8815):     at dalvik.system.NativeStart.main(Native Method)
05-19 22:24:00.251: E/SQLiteLog(8815): (1) no such table: pendingintents

pendingintents is the name i am giving to my table.

here is my sqlite helper class

public class SQLLiteOpenHelper extends SQLiteOpenHelper {

 public static final String TABLE_PENDINGINTENT = "pendingintents";
  public static final String COLUMN_ID = "_id";
  public static final String COLUMN_HOUR = "hour";
  public static final String COLUMN_MINUTES = "minutes";
  public static final String COLUMN_SECONDS = "seconds";
  public static final String COLUMN_YEAR = "year";
  public static final String COLUMN_MONTH = "month";
  public static final String COLUMN_DAY = "day";
  public static final String COLUMN_FREQUENCY = "frequency";
  public static final String COLUMN_NUMBERTOSEND = "numbertosend";
  public static final String COLUMN_RECEIVERNAME = "receivername";
  public static final String COLUMN_MESSAGE = "message";
      private static final String DATABASE_NAME = "PendingIntentDatabase.db";
      private static final int DATABASE_VERSION = 1;


      private static final String DATABASE_CREATE = "CREATE TABLE " + TABLE_PENDINGINTENT + "(" + COLUMN_ID + " INTEGER PRIMARY KEY , " + COLUMN_HOUR +" INTEGER, " + COLUMN_MINUTES + " INTEGER, " + COLUMN_SECONDS + " INTEGER, " + COLUMN_YEAR + " INTEGER, " + COLUMN_MONTH + " INTEGER, " + COLUMN_DAY + " INTEGER, " + COLUMN_FREQUENCY + " String, " + COLUMN_NUMBERTOSEND + " String, " + COLUMN_RECEIVERNAME + " String, " + COLUMN_MESSAGE + " String " + ");";

public SQLLiteOpenHelper(Context context, String name,
        CursorFactory factory, int version) {
    super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    // TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase arg0) {
    // TODO Auto-generated method stub
    arg0.execSQL(DATABASE_CREATE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PENDINGINTENT);
    onCreate(db);
}

}

the database insertion is done here'

              public ValueInsertion InitializePendingIntents(int id, int hour, int mins, int secs, int year, int month, int day, String frequency, String number, String name, String message )
  {
      ContentValues val= new ContentValues();
      val.put(SQLLiteOpenHelper.COLUMN_ID, id);
      val.put(SQLLiteOpenHelper.COLUMN_HOUR, hour);
      val.put(SQLLiteOpenHelper.COLUMN_MINUTES, mins);
      val.put(SQLLiteOpenHelper.COLUMN_SECONDS, secs);
      val.put(SQLLiteOpenHelper.COLUMN_YEAR, year);
      val.put(SQLLiteOpenHelper.COLUMN_MONTH, month);
      val.put(SQLLiteOpenHelper.COLUMN_DAY, day);
      val.put(SQLLiteOpenHelper.COLUMN_FREQUENCY, frequency);
      val.put(SQLLiteOpenHelper.COLUMN_NUMBERTOSEND, number);
      val.put(SQLLiteOpenHelper.COLUMN_RECEIVERNAME, name);
      val.put(SQLLiteOpenHelper.COLUMN_MESSAGE, message);

      database.insert(SQLLiteOpenHelper.TABLE_PENDINGINTENT, null,val);

      Cursor cursor = database.query(SQLLiteOpenHelper.TABLE_PENDINGINTENT,
                allColumns, SQLLiteOpenHelper.COLUMN_ID + " = " + id, null,
                null, null, null);

      cursor.moveToFirst();
      ValueInsertion newPendingIntent = cursorForPI(cursor);
      cursor.close();
      return newPendingIntent;
  }    

Answer 1

You also need to ensure execution of the sql statement that creates your table:

DATABASE_CREATE = "CREATE TABLE " + TABLE_PENDINGINTENT + "(" + COLUMN_ID + " INTEGER PRIMARY KEY , " + COLUMN_HOUR +" INTEGER, " + COLUMN_MINUTES + " INTEGER, " + COLUMN_SECONDS + " INTEGER, " + COLUMN_YEAR + " INTEGER, " + COLUMN_MONTH + " INTEGER, " + COLUMN_DAY + " INTEGER, " + COLUMN_FREQUENCY + " String, " + COLUMN_NUMBERTOSEND + " String, " + COLUMN_RECEIVERNAME + " String, " + COLUMN_MESSAGE + " String " + ");";

It's very clear by the following statement:

05-19 22:24:00.211: E/SQLiteLog(8815): (1) no such table: pendingintents

that the table is not created - for whatever reason. To clarify, if upgrade is getting executed, the table is dropped - you have no recreate statement afterwards...

Point of order. You name the string that creates your table DATABASE_CREATE. It should be create_table_pendingintents, or something that is not as misleading as DATABASE_CREATE

EDIT

If you are experiencing an error during sql execution, try wrapping your code in an error trap - recommended anytime you perform db operations:

try{
    db.execSQL(DATABASE_CREATE_MY_TABLE);                                   
}catch(SQLiteException e){
   Log.v(CLASS,"Error: [{"+CLASS+"}{database}[onCreate] Exception: "+e.getMessage());
}

Answer 2

    private static final String DATABASE_CREATE = "CREATE TABLE " + TABLE_PENDINGINTENT + "(" + COLUMN_ID + " INTEGER PRIMARY KEY , " + COLUMN_HOUR +" INTEGER, " + COLUMN_MINUTES + " INTEGER, " + COLUMN_SECONDS + " INTEGER, " + COLUMN_YEAR + " INTEGER, " + COLUMN_MONTH + " INTEGER, " + COLUMN_DAY + " INTEGER, " + COLUMN_FREQUENCY + " String, " + COLUMN_NUMBERTOSEND + " String, " + COLUMN_RECEIVERNAME + " String, " + COLUMN_MESSAGE + " String " + ");";

    public SQLLiteOpenHelper(Context context, String name,CursorFactory factory, intversion) {
            super(context, DATABASE_NAME, factory, DATABASE_VERSION);
            // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase arg0) {
        // TODO Auto-generated method stub
        db.execSQL(DATABASE_CREATE );
   }