Dfyjkt

I have an SQL database with multiple columns which I use with a simpleCursorAdapter to show it inside a listview. I would like to sort my database on the Integer Column COL3. I am aware of how this should be done, however, my attempts did not sort the data. I am not fully sure if I've placed it incorrectly but I would greatly appreciate the help.

DatabaseHelper.java

 @Override
 public void onCreate(SQLiteDatabase db) 
 String createTable = "CREATE TABLE " + TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
 COL2 +" TEXT, " + COL3 + " INTEGER, '" + COL4 + "' TEXT, '" + COL5 + "'INTEGER, '" + COL6 + "'INTEGER, '" + COL7 + "'INTEGER, '" + COL8 + "'INTEGER)";
 db.execSQL(createTable);
 

 @Override
 public void onUpgrade(SQLiteDatabase db, int i, int i1) 
 db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
 onCreate(db);
 

 public boolean addData(String item, String desc, int pri, int strat, int person, int urgen) 
 SQLiteDatabase db = this.getWritableDatabase();
 ContentValues contentValues = new ContentValues();
 contentValues.put(COL2, item);
 contentValues.put(COL4, desc);
 contentValues.put(COL3, pri);
 contentValues.put(COL6, strat);
 contentValues.put(COL7, person);
 contentValues.put(COL8, urgen);

 String query = "SELECT * FROM " + TABLE_NAME + " ORDER BY " + COL3 + " DESC";
 db.rawQuery(query, null);

 Log.d(TAG, "addData: Adding " + item + " to " + TABLE_NAME);
 long result = db.insert(TABLE_NAME, null, contentValues);

 //if data as inserted incorrectly it will return -1
 if (result == -1) 
 return false;
 else 
 return true;
 
 
public Cursor getData()
 SQLiteDatabase db = this.getWritableDatabase();
 String query = "SELECT * FROM " + TABLE_NAME;
 Cursor data = db.rawQuery(query, null);
 return data;
 
 public Cursor getItemID(String name)
 SQLiteDatabase db = this.getWritableDatabase();
 String query = "SELECT " + COL1 + " FROM " + TABLE_NAME +
 " WHERE " + COL2 + " = '" + name + "'";
 Cursor data = db.rawQuery(query, null);
 return data;
 
 public String databaseToStringArray() 
 String fromColumns = new StringCOL2, COL4;
 SQLiteDatabase db = getWritableDatabase();
 Cursor cursor = db.rawQuery(" SELECT * FROM " + TABLE_NAME + " WHERE 1 ", null);

 if (cursor != null && cursor.getCount()>0) 
 Log.d("Event", "Records do exist");
 
 else 
 Log.d("Event", "Records do not exist");
 


 cursor.moveToFirst();
 while (!cursor.isAfterLast()) 
 cursor.moveToNext();
 
 db.close();
 return fromColumns;
 
}

If you would like me to elaborate or provide further information I would be happy to. Thanks in advance

Your code :-

 String query = "SELECT * FROM " + TABLE_NAME + " ORDER BY " + COL3 + " DESC";
 db.rawQuery(query, null);

Will extract the data into a Cursor sorted as you have specified. However, the Cursor is not used. This will have no affect on the Table itself where the data is stored where available space exists.

What you need to do include the sort (ORDER BY COL3) in the query that returns the Cursor (the getData method appears to this method) that is used as the source of the ListView.

If the above assumption is correct then the getData method could be :-

public Cursor getData()
 SQLiteDatabase db = this.getWritableDatabase();
 String query = "SELECT * FROM " + TABLE_NAME + " ORDER BY " + COL3 + " DESC";
 return db.rawQuery(query, null);

• Note the simpler direct return of Cursor

The above lines serve no useful purpose inside the addData method and should be removed. As such the addData method could be :-

public boolean addData(String item, String desc, int pri, int strat, int person, int urgen) 
 SQLiteDatabase db = this.getWritableDatabase();
 ContentValues contentValues = new ContentValues();
 contentValues.put(COL2, item);
 contentValues.put(COL4, desc);
 contentValues.put(COL3, pri);
 contentValues.put(COL6, strat);
 contentValues.put(COL7, person);
 contentValues.put(COL8, urgen);

 Log.d(TAG, "addData: Adding " + item + " to " + TABLE_NAME);
 return (db.insert(TABLE_NAME, null, contentValues) > 0);

• Note the simpler/alternative code for determining true or false for the return.