Updating a single column is creating sqlite syntax error

Question

I'm not sure what I'm doing wrong, but I'm trying to update a single integer value in a column of a table to 1 from 0. When creating the database, I set all values of the column to zero using:

for (int i = 0; i < setups.length; i++) {
        ContentValues values = new ContentValues();
        values.put(JokeDbContract.TblJoke.COLUMN_NAME_SETUP, setups[i]);
        values.put(JokeDbContract.TblJoke.COLUMN_NAME_PUNCHLINE, punchlines[i]);
        values.put(JokeDbContract.TblJoke.COLUMN_NAME_USED, 0);

        db.insert(JokeDbContract.TblJoke.TABLE_NAME, null, values);
    }

Then, in the actual activity, I'm doing:

private void findNewJoke() {
    JokeDb jokeDb = JokeDb.getInstance(this);
    SQLiteDatabase theDb = jokeDb.getDB();
    String selection = JokeDbContract.TblJoke.COLUMN_NAME_USED + "=" + 0;

    // Query database for a joke that has not been used, update the fields
    // theJoke and thePunchline appropriately
    String[] columns = {JokeDbContract.TblJoke._ID,
                        JokeDbContract.TblJoke.COLUMN_NAME_PUNCHLINE,
                        JokeDbContract.TblJoke.COLUMN_NAME_SETUP,
                        JokeDbContract.TblJoke.COLUMN_NAME_USED};

    Cursor c = theDb.query(JokeDbContract.TblJoke.TABLE_NAME, columns, selection,
                        null, null, null, null);

    if (c.moveToFirst() == false) {
        Toast.makeText(this, R.string.error_retrieving_joke, Toast.LENGTH_LONG).show();
        Log.e(getString(R.string.app_name),"No jokes retreived from DB in JokeActivity.findNewJoke()!");
    }
    else {
        ContentValues values = new ContentValues();
        theSetup = c.getString(c.getColumnIndexOrThrow(JokeDbContract.TblJoke.COLUMN_NAME_SETUP));
        thePunchline = c.getString(c.getColumnIndexOrThrow(JokeDbContract.TblJoke.COLUMN_NAME_PUNCHLINE));

        String updateSelection = JokeDbContract.TblJoke.COLUMN_NAME_SETUP + "=" + theSetup;

        values.put(JokeDbContract.TblJoke.COLUMN_NAME_USED, 1);

        theDb.update(JokeDbContract.TblJoke.TABLE_NAME, values, updateSelection, null);
    }
}

I'm getting an error on the update:

java.lang.RuntimeException: .... while compiling: UPDATE jokes SET used=? 
WHERE setup=Why do programmers always mix up Halloween and Christmas?

It seems as though I'm not getting an actual value set for the used column. What the program ultimately does is cycle through jokes where used=0, then sets used to 1 when it has been viewed. So the query only pulls those jokes that aren't used yet. I have a feeling I'm missing something simple, one can hope.

Knossos · Accepted Answer · 2013-04-09 06:18:18Z

4

I think you are having problems with quotation marks.

Example:

String updateSelection = JokeDbContract.TblJoke.COLUMN_NAME_SETUP + "=\"" + theSetup + "\"";

However, the recommended way to do this, would be:

theDb.update(JokeDbContract.TblJoke.TABLE_NAME, values, JokeDbContract.TblJoke.COLUMN_NAME_SETUP + " = ?", new String[] { theSetup });

It is better to use field = ?, because this helps sqlite cache queries (I believe).

answered Apr 9, 2013 at 6:18

Knossos

16.1k11 gold badges59 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

The Rationalist Over a year ago

That was it. Here I was thinking I had set my values up incorrectly b/c of the syntax error it was giving with the SET clause. Thanks for your help.

Collectives™ on Stack Overflow

Updating a single column is creating sqlite syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related