CODEWITHSUNDEEP
androidsqlitesql-update
Daisy
Daisy

Reputation: 121

Android: Having trouble updating a row in a database after determining what row I need to update

I have a table that contains a list of devices with catagories like type, manufacturer and model and a device ID. Another table called system uses the device id to point to the table with the devices. I have a edit system function that I am trying to get working where I can edit the device id field in a record on the system table to change which device in the devices table I am pointing too. I run the update command and it looks like it should work but the row does not get updated. Does anyone know what I am doing wrong?

Here is my update code routine.

SQLiteDatabase db = dbHelper.getWritableDatabase();
String UpdateDeviceName = txtEditDeviceName.getText().toString();
String UpdateDeviceIP = txtEditDeviceIP.getText().toString();
//get the new device id based off of the three spinner values
String dbQuery = "SELECT * FROM " + dbHelper.Devices_Table + ";";
Cursor c = db.rawQuery(dbQuery, null);
if (c.getCount() > 0)
{
    while (c.moveToNext())
    {
        int iColumnID = c.getColumnIndex(dbHelper.Attribute_Device_ID);
        int iColumnDeviceType = c.getColumnIndex(dbHelper.Attribute_Device_Type);
        int iColumnDeviceManufacturer = c.getColumnIndex(dbHelper.Attribute_Device_Manufacturer);
        int iColumnDeviceModel = c.getColumnIndex(dbHelper.Attribute_Device_Model);
        String CheckDeviceType = c.getString(iColumnDeviceType);
        if (CheckDeviceType.equals(DeviceType))
        {
            String CheckDeviceManufacturer = c.getString(iColumnDeviceManufacturer);
            if (CheckDeviceManufacturer.equals(DeviceManufacturer))
            {
                String CheckDeviceModel = c.getString(iColumnDeviceModel);
                if (CheckDeviceModel.equals(DeviceModel))
                {
                    DeviceID = c.getString(iColumnID);
                }
             }
         }
      }
}
db.close();
c.close();
db = dbHelper.getWritableDatabase();
//with the device ID update the system
dbQuery = "UPDATE " + dbHelper.System_Table + " SET " + dbHelper.Attribute_Device_ID + " = " + DeviceID + ", " +
    dbHelper.Attribute_Device_Name + " = '" + UpdateDeviceName + "', " +
    dbHelper.Attribute_Device_IP + " = '" +
    UpdateDeviceIP + "' WHERE " + dbHelper.Attribute_Device_ID + " = " + OrginalDeviceID + ";";
c = db.rawQuery(dbQuery, null);
Log.d("Database Dump", "Edit Device Query: " + dbQuery);
c.close();
db.close();

Upvotes: 2

Views: 884

Answers (3)

Daisy
Daisy

Reputation: 121

I found the answer. FIrst off I want to thank Henry and Wolfram Rittmeyer. You guys where very helpful. :)

For whatever reason the android SQLite does not like to use the update statement in raw sql query form. In researching what Wolfram Rittmeyer said about updateWithConflict I found the update method in the SQLiteOpenHelper object which my dbHelper extends. When I switched to that method I was able to update the database. My where conditions have been expanded on to include the device name as well in the event that the system has more then one device make and model of the same type and only one has to change.

For reference the working code to update the database is the following. I'm only posting the changed portion of the code. Everything in the if (c.getCount() > 0) block and above is unchanged.

c.close();
dbHelper.close();
db = dbHelper.getWritableDatabase();
//with the device ID update the system
ContentValues values = new ContentValues();
values.put(dbHelper.Attribute_Device_ID, DeviceID);
values.put(dbHelper.Attribute_Device_Name, UpdateDeviceName);
values.put(dbHelper.Attribute_Device_IP, UpdateDeviceIP);
String Where = dbHelper.Attribute_Device_ID + " = " + OrginalDeviceID +  " AND " + dbHelper.Attribute_Device_Name + " = '" + OrginalDeviceName + "'";
db.update(dbHelper.System_Table, values, Where, null);
c.close();
dbHelper.close();
finish(); //this exits the activity and goes back to the calling activity

Upvotes: 1

Wolfram Rittmeyer
Wolfram Rittmeyer

Reputation: 2402

First of all any rawQuery statement must not contain the trailing semicolon! See the documentation of SQLiteDatabase.

Also you always must close cursors before closing the database. And you have to close the db when you get a new one for each query. Otherwise the system at some point later on will throw exceptions. While those only will be logged and do not cause a Force Close, you should still care about proper resource management.

Finally you should use the updateWithOnConflict method instead with using CONFLICT_REPLACE as the conflictAlgorithm. This simply ignores any UNIQUE constraints and overwrites conflicting rows. So be careful. If you do not want to overwrite existing rows you have to make sure that a constarint violation isn't causing your problem.

Upvotes: 1

Henry
Henry

Reputation: 43738

You closed the DB after the search:

db.close();

leave out this line, since the DB helper code prefers to keep the DB open until it is no longer used (use dbHelper.close() to close the DB when you are done with it).

The same applies after the update.

Upvotes: 1

Related Questions