title: Sqlite: how do I increment a row value by +1?
body: <p>I have a TOTALCOUNT table with a PK _id column and an integer column "CARDNUM".  I want to set the default value of CARDNUM to zero.  And then increment CARDNUM by +1 each time a new row is inserted into the database.  So 5 row inserts would produce 1,2,3,4,5 for rowids 1 to 5.  When I push the data to the individual CardViews in my RecyclerView I am only getting the default value of zero.  The first five CardViews should be showing the CARDNUM value so 1,2,3,4,5.  What am I missing here?</p>
  
  <p>DBContract file:</p>
  
  <pre><code>...
  public static final String TABLE_NAME_TOTALCOUNT = "totalcount";
      public static final String COLUMN_NAME_COUNTID = "_id";
      public static final String COLUMN_NAME_CARDNUM = "cardnum";
      }
  }
  
      A second table, not shown is called USERINPUTS.
  </code></pre>
  
  <p>DBHelper file:</p>
  
  <pre><code>...
  public void onCreate(SQLiteDatabase db) {
      // Set up the Column headings for USERINPUTS and TOTALCOUNT Tables.
      db.execSQL(SQL_CREATE_ENTRIES);
      db.execSQL(SQL_CREATE_CARDNUM);
  
  private static final String SQL_CREATE_CARDNUM =
      "CREATE TABLE IF NOT EXISTS "+ DBContract.DBEntry.TABLE_NAME_TOTALCOUNT +
              "( "+ DBContract.DBEntry.COLUMN_NAME_COUNTID +
              " INTEGER PRIMARY KEY , "+
              DBContract.DBEntry.COLUMN_NAME_CARDNUM +
              " INTEGER);";
  
  public void insertIntoTableTOTALCOUNT() {
  
      SQLiteDatabase db = this.getWritableDatabase();
  
      db.beginTransaction();
  
      String select = "Select * from "+ DBContract.DBEntry.TABLE_NAME_TOTALCOUNT;
  
      Cursor c =db.rawQuery(select, null);
      int cardnum=0;
  
      try {
          if (c.moveToFirst()) {
              cardnum = c.getInt(1);
              cardnum++;
          }
      } finally {
          if(c !=null &amp;&amp; !c.isClosed()){
              c.close();
          }
      }
  
      ContentValues cv = new ContentValues();
      cv.put(DBContract.DBEntry.COLUMN_NAME_CARDNUM,cardnum);
      db.insert(DBContract.DBEntry.TABLE_NAME_TOTALCOUNT,null,cv);
  
      db.setTransactionSuccessful();
      db.endTransaction();
      // Close the database.
      if(db.isOpen())
          db.close();
  }
  
  ...
  // Two Tables push the data to a RecyclerView list.
  public List&lt;UserData&gt; getDataFromDB(){
      List&lt;UserData&gt; modelList = new ArrayList&lt;&gt;();
  
      String query = "SELECT *, (SELECT " + DBContract.DBEntry.COLUMN_NAME_CARDNUM +
              " from " + DBContract.DBEntry.TABLE_NAME_TOTALCOUNT +") from "
              + DBContract.DBEntry.TABLE_NAME_USERINPUTS +";";
  
      SQLiteDatabase db = this.getReadableDatabase();
  
      db.beginTransaction();
  
      Cursor cursor = db.rawQuery(query, null);
  
      Log.d("TAG", DatabaseUtils.dumpCursorToString(cursor));
  
          try {
              if (cursor.moveToFirst()) {
                  do {
                      UserData userData = new UserData();
                      userData.setTodo(cursor.getString(1));
                      userData.setNote1(cursor.getString(2));
                      userData.setNote2(cursor.getString(3));
                      userData.setDuedate(cursor.getString(4));
                      userData.setDuetime(cursor.getString(5));
                      userData.setTimestamp(cursor.getLong(6));
                      userData.setCardnum(cursor.getInt(7));
  
                      modelList.add(0, userData);
                      } while (cursor.moveToNext());
              }
          } finally {
                if(cursor !=null &amp;&amp; !cursor.isClosed()){
                cursor.close();
                }
            }
      db.setTransactionSuccessful();
      db.endTransaction();
      return modelList;
  }    
  </code></pre>