Update row in SQlite database by row position in android

Question

I have database which contains "date" column and "item" column. I want that user could update specific row in the database. I trying to do it with update method in SQLiteDatabase class. My problem is that i dont know how to make update method find exactly the row i want. I saw some example that use it with parameters from one word. like this:

ourDatabase.update(tableName, cvUpdate, rowId + "=" + item , null);

My problem is that i want to update the row that have specific item and date. so the name of the item alone is not enough. I tried this code below but its didnt work, hope youll can help me.

public void updateEntry(String item, String date) throws SQLException{

String[] columns = new String[]{myItem, myDate};
Cursor c = ourDatabase.query(tableName, columns, null, null, null, null, null);

        long position;

        ContentValues cvUpdate = new ContentValues();
        cvUpdate.put(date, myDate);
        cvUpdate.put(item, myExercise);


        int itemAll = c.getColumnIndex(myItem);
        int dateAll = c.getColumnIndex(myDate);

        for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
            if (c.getString(itemAll).equals(myItem) && c.getString(dateAll).equals(myDate))
            {
                position = c.getPosition();
                break;
            }
        }

        ourDatabase.update(tableName, cvUpdate, rowId + "=" + position , null); 
    }

Can you not use SQL? UPDATE mytable SET date=A WHERE date=X and item=Y — jarmod, May 02 '13 at 16:08
See http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ — jarmod, May 03 '13 at 01:16

score 1 · Accepted Answer · answered May 02 '13 at 16:01

First, the columns String[] is supposed to contain column names, such as "_ID", or whatever are the column names you have used. Given that you compare the content of the column myItem with the object myItem, I assume there is a confusion somewhere here.

Secondly, rowId and position are different things in SQL, especially if you delete rows, as the row id usually is autoincrement, and especially since your query is not explicitely sorted. Replacing c.getPosition() by c.getLong(c.getColumnIndex(ID_COLUMN)) would make more sense.

Thirdly, sql is nice because you can query it. For example, rather than get all items and loop to find the matching date and item, you can :

String whereClause = ITEM_COLUMN + " = ? and " + DATE_COLUMN + " = ?";
String[] whereArgs = new String[] { item, date };
Cursor c = ourDatabase.query(tableName, columns, whereClause, whereArgs, null, null, null);

instead of your for loop.

Forthly, you can even make the query in the update :

String whereClause = ITEM_COLUMN + " = ? and " + DATE_COLUMN + " = ?";
String[] whereArgs = new String[] { item, date };
ourDatabase.update(tableName, cvUpdate, whereClause, whereArgs);

Extra tip: use full caps variable names for contants such as column names, it help with readability.

Great answer thank you! I think i understand you answer, i just dont realy know SQL commands. At this line of code: String whereClause = ITEM_COLUMN + " = ? and " + DATE_COLUMN + " = ?"; What should i put insted of the "?" a string? a long number? — tomer, May 02 '13 at 20:08
the ? is actually a ?. It is not intended for you to replace it. — njzk2, May 03 '13 at 07:37
This is what the `whereArgs` are for, sqlite does the replacement for you. It also does the escaping if required, and this format can be used by sqlite to make optimization based on the repetition of similar queries. — njzk2, May 03 '13 at 07:38

Update row in SQlite database by row position in android

1 Answers1