Android Update record if data exist

Question

I have this code below which consist on my Android app DatabaseHelper Class, Currently i have hardcoded a row of data inside my database which is the Barcode(263924) and Quantity(1).

But the problem is i'm currently trying to make it so that when the user adds the same barcode number i hardcoded into my database the quantity will +1 and not create a new row.

Sorry im currently new to android any help would be greatly appreciated thanks!

MainActivity Codes

@Override
public void handleResult(Result result) {
    final String barcodes = result.getText();
    final String quantitys = "1";
    Log.d("QRCodeScanner", result.getText());
    Log.d("QRCodeScanner", result.getBarcodeFormat().toString());

    AlertDialog.Builder builder = new AlertDialog.Builder(this);
    builder.setTitle("Scan Result");
    builder.setPositiveButton("OK", new DialogInterface.OnClickListener() {
        @Override
        public void onClick(DialogInterface dialog, int which) {
            scannerView.resumeCameraPreview(MainActivity.this);
        }
    });
    builder.setMessage(result.getText());
    AlertDialog alert1 = builder.create();
    alert1.show();
    AddData(barcodes,quantitys);

}

public void AddData(String barcodes,String quantitys){
    boolean insertData = myDB.addData(barcodes,quantitys);

    if(insertData==true){
        Toast.makeText(MainActivity.this,"Successfully Entered Data!",Toast.LENGTH_LONG).show();
    }else{
        Toast.makeText(MainActivity.this,"Something went wrong :(.",Toast.LENGTH_LONG).show();
    }
}

Database Code

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "scan.db";
    public static final String TABLE_NAME = "scan_data";
    public static final String COL1 = "ID";
    public static final String COL2 = "BARCODE";
    public static final String COL3 = "QUANTITY";



    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                " BARCODE TEXT, QUANTITY TEXT)";
        db.execSQL(createTable);

        ContentValues contentValues=new ContentValues();
        contentValues.put(DatabaseHelper.COL2,263924);
        contentValues.put(DatabaseHelper.COL3,1);

        db.insert(DatabaseHelper.TABLE_NAME,null,contentValues);


    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public boolean addData(String code, String quant) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL2, code);
        contentValues.put(COL3, quant);

        long result = db.insert(TABLE_NAME, null, contentValues);
        //if date as inserted incorrectly it will return -1
        if (result == -1) {
            return false;
        } else {
            return true;
        }
    }

    public Cursor getListContents() {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor data = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        return data;
    }
}

Possible duplicate of [Android SQLite Insert or Update](https://stackoverflow.com/questions/13311727/android-sqlite-insert-or-update) — MWB, Nov 03 '18 at 16:24

Gratien Asimbahwe · Answer 1 · 2018-11-05T05:18:54.393

For that, I could proceed as follows:

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "scan.db";
    public static final String TABLE_NAME = "scan_data";
    public static final String COL1 = "ID";
    public static final String COL2 = "BARCODE";
    public static final String COL3 = "QUANTITY";



    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                " BARCODE TEXT, QUANTITY TEXT)";
        db.execSQL(createTable);

        ContentValues contentValues=new ContentValues();
        contentValues.put(DatabaseHelper.COL2,263924);
        contentValues.put(DatabaseHelper.COL3,1);

        db.insert(DatabaseHelper.TABLE_NAME,null,contentValues);


    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public boolean addData(String code, String quant) {
        SQLiteDatabase db = this.getWritableDatabase();
        return isValueExists(code,db,quant);

    }

    public Cursor getListContents() {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor data = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        return data;
    }
    public Boolean isValueExists(String barcode,SQLiteDatabase db, String quant){
        Cursor cursor= db.query(TABLE_NAME,new String[]      {"ID"},"ID=?",new String[]{barcode},null,null,null,"1");
        if(cursor.getCount()>0){
             //if it exists then update
               try{
                    ContentValues cv = new ContentValues();
                    cv.put(COL1,cursor.getString(0));
                    cv.put(COL2,cursor.getString(1));
                  cv.put(COL3,String.valueOf(Integer.valueOf(cursor.getString(2)+1)));
                  sqLiteDataBase.update(TABLE_NAME,cv,"BARCODE=?",new String[]{barcode});
                     cursor.close();

            }catch(Exception e){}

        return true;
                }else{
                    cursor.close();
                    ContentValues contentValues = new ContentValues();
        contentValues.put(COL2, code);
        contentValues.put(COL3, quant);

        long result = db.insert(TABLE_NAME, null, contentValues);

        db.close();
        //if date as inserted incorrectly it will return -1

        if (result == -1) {
            return false;
        } else {
            return true;
        }}
            }

}

copy the code and insert it in the database helper class. then before calling `addData`, test if the value exists by passing the barcode into parameter if it returns false then continue instructions inside `addData`. do you want me to edit the answer and insert the line? — Gratien Asimbahwe, Nov 04 '18 at 15:05
Yes if you dont mind can you edit the awnser and insert the lines? I have updated my question. — Best Jeanist, Nov 05 '18 at 04:38
Is it showing any error? If yes post in comment. Any message? isn't it even inserting? I'd to test your project in local if possible — Gratien Asimbahwe, Nov 05 '18 at 07:04

score 0 · Answer 2 · answered Nov 03 '18 at 16:27

CREATE TABLE table_name(
    id NOT NULL INTEGER PRIMARY KEY,
    ...
);

A primary key is a column or group of columns used to identify the uniqueness of rows in a table. Each table has one and only one primary key.

Source: http://www.sqlitetutorial.net/sqlite-primary-key/

Android Update record if data exist

2 Answers2