Sqlite: how do I increment a row value by +1?

Question

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?

DBContract file:

...
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.

DBHelper file:

...
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 && !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<UserData> getDataFromDB(){
    List<UserData> modelList = new ArrayList<>();

    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 && !cursor.isClosed()){
              cursor.close();
              }
          }
    db.setTransactionSuccessful();
    db.endTransaction();
    return modelList;
}

What is the point of having a primary key value which is not autoincremented? — Tim Biegeleisen, May 13 '16 at 01:30
I am looking to have a column of values that increment by +1 with a default value of 0. I am new to SQLite so not sure what the best way to go about this. — AJW, May 13 '16 at 01:41
Have a look [here](http://stackoverflow.com/questions/6982173/sqlite-auto-increment-non-primary-key-field). — Tim Biegeleisen, May 13 '16 at 01:51
Thanks, I'll take a look...and I appreciate you coming back with additional advice. My apologies for my venting, I did not mean to direct that. — AJW, May 13 '16 at 01:54
I never thought you vented actually. Your design may have some flaws, but at least now you have two ways to auto increment a column. — Tim Biegeleisen, May 13 '16 at 01:58
Unfortunately app crashes on trying to create the table so something is not correct. String is: "private static final String SQL_CREATE_CARDNUM = "CREATE TABLE IF NOT EXISTS "+ DBContract.DBEntry.TABLE_NAME_TOTALCOUNT + "( "+ DBContract.DBEntry.COLUMN_NAME_CARDNUM + " INTEGER AUTOINCREMENT )";" — AJW, May 13 '16 at 02:00
I think SQLite doesn't let you use `AUTOINCREMENT` unless the field is _also_ the primary key. So the link I provided is an alternative. Really, I think your database design needs some improvement. — Tim Biegeleisen, May 13 '16 at 02:01

score 0 · Answer 1 · answered May 13 '16 at 00:39

0

You can do easily by adding " AUTOINCREMENT " option when creating table and it will automatically incremented

or by your way ,, but you are missing when dealing with Cursor try this way

Cursor c =db.rawQuery(select, null);
    int cardnum=0;
c.moveToFirst();
while( c != null )
{
 cardnum = c.getInt(1);
            cardnum++;
c.moveToNext();
}

answered May 13 '16 at 00:39

Abdul latif Wanas

11
4

Android Studio warning says "Condition "(c != null)" is always true. – AJW May 13 '16 at 00:46
it will be always true untill the last row have value of your table , then moveToNext() method will return null cursor – Abdul latif Wanas May 13 '16 at 00:59
App crashes. Using my way from above, error message says "CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0" for the line "cardnum = c.getInt(1);" – AJW May 13 '16 at 00:59

Sqlite: how do I increment a row value by +1?

1 Answers1