Update SQL Database with Contentvalues and the Update-Method

update sql database with ContentValues and the update-method

You're using the update function wrong. It should be like this:

String where = "id=?";
String[] whereArgs = new String[] {String.valueOf(id)};

db.update(DATABASE_TABLE, dataToInsert, where, whereArgs);

The Strings in the whereArgs array gets substituted in for each '?' in the where variable.

ie. if you had where = "name=? AND type=? then the first '?' would get replaced by whereArgs[0] and the second by whereArgs[1].

Updating SQLite row using update method

A WHERE clause is the filter for your query.
Normal WHERE clauses look something like this

UPDATE table_name
SET column_name = value
WHERE column_name1 = value1 AND/OR column_name2 = value2 AND/OR...

The db.update() does the first step for you when you pass it a table name. The String whereClause is the column_name1= part and value1 would be put in the String[] whereArgs spot.
The String[] whereArgs can be passed a null option as long as your String whereClause has the full option of column_name1 = value1 clause.

The whereClause acts as a filter to limit which rows are changed by the UPDATE statement.

If you wanted to set one row to 'x' you would do it like this:

String whereClause = "note = \"the\"";
db.update("noteTable", "x", whereClause, null);

This would update all rows where the 'note' column contains the value "the".

How to update and increment a database?

I guess COL_NUM is the name of the column, right?

So this line is wrong:

cv.put("COL_NUM", COL_NUM + 1);

because you think that you add a number to the value of the column where in reality you concatenate 1 to the name of the column.

What you must do is increment the current value by 1 like this:

db.execSQL(
    "update " + TaskContract.TaskEntry.TABLE + 
    " set " + COL_TaskContract.TaskEntry.COL_NUM + " = " + COL_TaskContract.TaskEntry.COL_NUM + " + 1" + 
    " where " + TaskContract.TaskEntry.COL_TASK_TITLE + " = ?", new Object[] {task});

If there is a case that COL_NUM is null then change the statement to this:

db.execSQL(
    "update " + TaskContract.TaskEntry.TABLE + 
    " set " + COL_TaskContract.TaskEntry.COL_NUM + " = coalesce(" + COL_TaskContract.TaskEntry.COL_NUM + ", 0) + 1" + 
    " where " + TaskContract.TaskEntry.COL_TASK_TITLE + " = ?", new Object[] {task});

So change your method to this:

public void incrment(View view) {
    View parent = (View) view.getParent();
    TextView taskTextView = (TextView) parent.findViewById(R.id.task_title);
    String task = taskTextView.getText().toString();
    SQLiteDatabase db = mHelper.getWritableDatabase();
    db.execSQL(
        "update " + TaskContract.TaskEntry.TABLE +
        " set " + COL_TaskContract.TaskEntry.COL_NUM + " = coalesce(" + COL_TaskContract.TaskEntry.COL_NUM + ", 0) + 1" +
        " where " + TaskContract.TaskEntry.COL_TASK_TITLE + " = ?", new Object[] {task}
    );
    db.close();
    updateUI();
}

Of course you could use the update() method like you do, but first you should find the existing value in COL_NUM, increment it, put it in cv and then execute update().

How can use sqlite update via ContentValues when want to update depend on current value and how can retrieve the Result?

Android's update() function can set only fixed values.

To execute your SQL statement, you have to use execSQL():

db.execSQL("Update my_table set _score = _score + 5 where _id = 1");

In SQLite, the UPDATE statement does not return any data (except the count of affected rows). To get the old or new data, you have to execute a SELECT separately.

How to update data in a SQLite database with an AND condition?

Your can write in two ways :

long id = db.update(tableName, contentValues, "hhid=" + " = ? AND " + "respid=" + " = ?",new String[]{hhid_id, resp_id});

OR

db.update(SSFormat_One, contentValues, "hhid=" + hhid_id + " and respid=" + resp_id , null);

Android SQLite database update method

Edit:

It will check is there a row with date = '15/03/2013 if there is no row,
then no rows will effect, and the update method will return 0

---

If I understand your question,
Where claus is optional, it may be null, if you don't provide where clause it will update all the rows in a table.

It is just a sql thing, like if you don't supply where condition it will update all rows.
documentation clearly says that,

Parameters

whereClause the optional WHERE clause to apply when updating. Passing null will update all rows.

SqlliteData Update

SQLiteDatabase update using replace function

The convenience methods do have limitations in regards to enclosing values.

You could instead utilise the execSQL method (see code re rawQuery) for example :-

public long alterRows(String from, String to, int limit) {
    long rv = 0;
    String[] args = new String[]{from,to,from,String.valueOf(limit)};
    String sql = "UPDATE " + TB_TBL +
            " SET " + COL_TBL_TEL + " = replace(" + COL_TBL_TEL + ",?,?) " + //<<<<IGNORE EXPRESSION EXPECTED
            " WHERE rowid IN (" +
            "SELECT rowid FROM " + TB_TBL +
            " WHERE " + COL_TBL_TEL + "=? " +
            "ORDER BY " + COL_TBL_TEL +
            " LIMIT ?" +
            ");" ;
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL(sql,args);
    //mDB.rawQuery(sqlxxx,args); //<<<< only does 1 update not 2
    //Ascertain the number of updates
    Cursor csr = db.rawQuery("SELECT changes()",null);
    if (csr.moveToFirst()) {
        rv = csr.getLong(0);
    }
    csr.close();
    Log.d("Updates","Number of updates = " + String.valueOf(rv)); //<<<< LOG number of updates
    return rv;
}

Which could be called like :-

    SO50378333DBHelper mDBHlpr = new SO50378333DBHelper(this);
    mDBHlpr.alterRows("0501","0502",2); 

• Where SO50378333DBHelper is the DatabaseHelper (named as it is for my convenience) where the code above exists.

Example used for testing

The following is the complete code used to test this.

First the DatabaseHelper SO50378333DBHelper.java

public class SO50378333DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "so50378333";
    public static final int DBVERSION = 1;
    public static final String TB_TBL = "tbl";
    public static final String COL_TBL_TEL = "tel";

    SQLiteDatabase mDB;

    public SO50378333DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crt_tbl = "CREATE TABLE IF NOT EXISTS " + TB_TBL +
                "(" +
                COL_TBL_TEL + " TEXT" +
                ")";
        db.execSQL(crt_tbl);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void loadTBL() {
        ContentValues cv = new ContentValues();
        mDB.beginTransaction();
        mDB.delete(TB_TBL,null,null);
        for (int i = 0; i < 1000; i++) {
            cv.clear();
            if ( i % 4 == 0) {
                cv.put(COL_TBL_TEL,"0501");
            } else {
                cv.put(COL_TBL_TEL,"0504");
            }
            mDB.insert(TB_TBL,null,cv);
        }
        String sql = "SELECT sqlite_version() AS v";
        Cursor csr = mDB.rawQuery(sql,null);
        if (csr.moveToFirst()) {
            Log.d("SQLite VERSION","Version is " + csr.getString(0));
        }
        mDB.setTransactionSuccessful();
        mDB.endTransaction();

    }

    public long alterRows(String from, String to, int limit) {
        long rv = 0;
        String[] args = new String[]{from,to,from,String.valueOf(limit)};
        String sql = "UPDATE " + TB_TBL +
                " SET " + COL_TBL_TEL + " = replace(" + COL_TBL_TEL + ",?,?) " + //<<<<IGNORE EXPRESSION EXPECTED
                " WHERE rowid IN (" +
                "SELECT rowid FROM " + TB_TBL +
                " WHERE " + COL_TBL_TEL + "=? " +
                "ORDER BY " + COL_TBL_TEL +
                " LIMIT ?" +
                ");" ;
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL(sql,args);
        //mDB.rawQuery(sqlxxx,args); //<<<< only does 1 update not 2
        //Ascertain the number of updates
        Cursor csr = db.rawQuery("SELECT changes()",null);
        if (csr.moveToFirst()) {
            rv = csr.getLong(0);
        }
        csr.close();
        Log.d("Updates","Number of updates = " + String.valueOf(rv)); //<<<< LOG number of updates
        return rv;
    }
}

• See comments //<<<<
• rawQuery doesn't actually update any rows, even though the changes() function indicates that it has updated 1 instead of 2. This is likely because it is reporting on the last INSERT due to th rawQuery not performing any updates.
• rawQuery doesn't update as it's not until the result (the Cursor) is accessed (e.g. a moveToFirst()) (which it isn't) that it will do it's work.

Second the invoking code within an activity (MainActivity.java)

    SO50378333DBHelper mDBHlpr = new SO50378333DBHelper(this);
    mDBHlpr.loadTBL();
    mDBHlpr.alterRows("0501","0502",2);

• Note the call to loadTBL loads data into the table (deleting all rows before doing so). 1000 rows are inserted, every first row of 4 will be "0501" (250) all other rows (750) will be "0504"

Results in the Log from the above :-

05-16 23:19:31.554 2622-2622/? D/SQLite VERSION: Version is 3.7.11
05-16 23:19:31.558 2622-2622/? D/Updates: Number of updates = 2

SQLite database columns don't get updated with Update method

Your updateProject method is roughly saying update values (as per the Contentvalues) where the project id is whatever is returned from the project's getID method.

However when you create the project to be passed to the method you don't provide the id (which should be as per the row added in the database) so it will be some arbitrary value perhaps null (can't say as the Project class isn't included in your code.)

So that's the cause.

There could be a number of fixes. One would be to get the id (very much the preferable/standard method as identifying a row is why you use an id column).

You can retrieve this when adding the project to the database by instead of returning false if the SQLiteDatabase method returns -1 by instead returning the value (the id of the inserted row). You can then check if that is -1 or less than 1 (id will be 1 or greater), which will indicate that the row was not added. If the value is 1 or greater, then the row has been inserted.

You could then pass the id via an IntentExtra to the MainActivity where you would extract and set the projects id to the value. Note you should ideally use long for the id as it can be up to a 64 bit signed Integer (Normally it is 1 then 2 then 3 etc.......).

Another way would be to identify the project from the database according to other know stored values that can uniquely identify the project and then alter the WHERE clause of the updateProject method (the 3rd and 4th parameters).

Another way would be to extract the id using the identifying information (so a permutation of the above)

Suggested fix

• Note this is in-principle code, it has not been tested so may contain some errors:-
• Comments have been include to identify the changes. they will typically be //<<<< followed some indication of that has been done.

1) Amend the addProject method to return the id else -1 :-

@Override
public long addProject(Project project) { //<<<< CHANGED
    SQLiteDatabase db = getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("name", project.getName());
    contentValues.put("company_name", project.getCompany_name());
    contentValues.put("address", project.getAddress());
    contentValues.put("length1",project.getLength1());
    contentValues.put("length2",project.getLength2());
    contentValues.put("length3",project.getLength3());
    contentValues.put("length4",project.getLength4());
    contentValues.put("length5",project.getLength5());
    contentValues.put("diameter1",project.getDiameter1());
    contentValues.put("diameter2",project.getDiameter2());
    contentValues.put("diameter3",project.getDiameter3());
    contentValues.put("diameter4",project.getDiameter4());
    contentValues.put("diameter5",project.getDiameter5());
    long result = db.insert("tbl_project_info", null, contentValues);
    db.close();
    return result; //<<<< CHANGED
}

2) Pass the id from the NewProjectActivity for the MainActivity :-

    saveInfoBTN.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            long projectid = -1; //<<<< LINE ADDED
            if (projectNameET.length() > 0) {
                if (companyNameET.length() > 0) {
                    if (addressET.length() > 0) {
                        Project project = new Project();
                        project.setName(projectNameET.getText().toString());
                        project.setCompany_name(companyNameET.getText().toString());
                        project.setAddress(addressET.getText().toString());
                        projectid = projectDAO.addProject(project); //<<<< LINE ADDED
                        if (projectid > 0){ //<<<< LINE CHANGED
                            Toast.makeText(NewProjectActivity.this, "Success", Toast.LENGTH_SHORT).show();
                        }else {
                            Toast.makeText(NewProjectActivity.this, "Failed", Toast.LENGTH_SHORT).show();
                        }
                    }
                }
            }

            Intent intent = new Intent(NewProjectActivity.this,MainActivity.class);
            intent.putExtra("IE_PROJECTID",projectid); //<<<< ADDED
            startActivity(intent);
            }
        });
    }

3) Retrieve the id in the MainActivity :-

private long mProjectID;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    projectDAO = DBInjector.provideProjectDao(this);
    //<<<< ADDED folowing lines to get the id from the intent >>>>
    mProjectID = getIntent().getLongExtra(
                "IE_PROJECTID",
                0 //<<<< NOTE default could be -1 (or any negative) as long as it's less than 1
        );
    .........
                    project.setID(mProjectID); //<<<< ADDED might need setter in project
                    projectDAO.updateProject(project);

• use of "IE_PROJECTID" could be replaced with constant, there are some stock ones that can be utilised like Intent.EXTRA_????????? (where ????????? represents many values)

Update method not saving changes Sqlite Database (Android Studio)

I was able to correct the error by rewriting my update method:

void updateData(String row_id, String produto, String valor, String quantidade, String tipo){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(COLUMN_PRODUTO, produto);
        cv.put(COLUMN_VALOR, valor);
        cv.put(COLUMN_QTD, quantidade);
        cv.put(COLUMN_TIPO, tipo);

        long result = db.update(TABLE_PRODUTO, cv, "codigo=?", new String[]{row_id});
        if(result == -1){
            Toast.makeText(context, R.string.strFailed, Toast.LENGTH_SHORT).show();
        } else{
            Toast.makeText(context, R.string.strSucess, Toast.LENGTH_SHORT).show();
        }

        db.close();
    }

Thanks for all the help

---

Related Topics