CODEWITHSUNDEEP
androidsqlwhere-clauseclause
Simon
Simon

Reputation: 53

android sql where clause with id

Problem solved, look at comment below

I'm trying to update a database, but the where clause doesn't work. I already looked at code samples, but they don't work for me.

This is how the table looks.

public static final String SQL_CREATE_ENTRIES =
         "CREATE TABLE " + MessageEntry.TABLE_NAME + " (" +
        MessageEntry.COLUMN_NAME_ENTRY_ID + " INT AUTO_INCREMENT PRIMARY KEY," +
         MessageEntry.COLUMN_NAME_ENTRY_USERNAME + 
         " VARCHAR(20)," +
         MessageEntry.COLUMN_NAME_ENTRY_MESSAGE+" VARCHAR(1000)," +
            MessageEntry.COLUMN_NAME_ENTRY_OUT + " SMALLINT NOT NULL DEFAULT  0,"+
         MessageEntry.COLUMN_NAME_ENTRY_STATUS + " VARCHAR(10),"+
            MessageEntry.COLUMN_NAME_ENTRY_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";

This is the method, that updates the table. There are a few update calls i tried, none of them works. Also tested with the hard value 1.

public void updateChatMessageStatus(long id,String status){
    MessageHelper helper = new MessageHelper(this.context);
    SQLiteDatabase db = helper.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(MessageEntry.COLUMN_NAME_ENTRY_STATUS, status);
    String[] where = {String.valueOf(id)};
    db.update(MessageEntry.TABLE_NAME, values, MessageEntry.COLUMN_NAME_ENTRY_ID+"=" +id, null);
    db.update(MessageEntry.TABLE_NAME, values, MessageEntry.COLUMN_NAME_ENTRY_ID+"=" +1, null);
    db.update(MessageEntry.TABLE_NAME, values, MessageEntry.COLUMN_NAME_ENTRY_ID+"=1", null);
    db.update(MessageEntry.TABLE_NAME, values, MessageEntry.COLUMN_NAME_ENTRY_ID+"=?", where);
    db.execSQL("UPDATE " + MessageEntry.TABLE_NAME+ " SET " +MessageEntry.COLUMN_NAME_ENTRY_STATUS+"='"+status
            +"' where "+ MessageEntry.COLUMN_NAME_ENTRY_ID +"="+1);
    db.execSQL("UPDATE " + MessageEntry.TABLE_NAME+ " SET " +MessageEntry.COLUMN_NAME_ENTRY_STATUS+"='"+status
            +"' where "+ MessageEntry.COLUMN_NAME_ENTRY_ID +"="+id);
}

I'm kinda stuck here, thanks for help.

Upvotes: 2

Views: 227

Answers (1)

Simon
Simon

Reputation: 53

The id column was created wrong. Before I used int and auto_increment to create the autogenerated id column. But in android the right way to create a autogenerated key column is to use autoincrement and integer.

corrected table

   "CREATE TABLE " + MessageEntry.TABLE_NAME + " (" +
        MessageEntry.COLUMN_NAME_ENTRY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +...

update call I use

values.put(MessageEntry.COLUMN_NAME_ENTRY_STATUS, status);
    String[] where = {String.valueOf(id)};
    db.update(MessageEntry.TABLE_NAME, values, MessageEntry.COLUMN_NAME_ENTRY_ID+"=?", where);

Upvotes: 1

Related Questions