CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Arshad Ali
Arshad Ali

Reputation: 3274

un-expected bug in Android SQLite

In my App, I was trying to save marks of student in SQLite Table, and show those in ListView, My App also have functionality of SORT orders, when I enter the marks of students 89, 78, 90, 100 respectivily and after this I want to sort the List in marks ascending order the name of student with 100 marks is at the top of the list and then the students having marks 78, 89, 90, apear in list one after other, why is this bug occuring? My DB schema is :

public static final String COL_ID = "_id";
public static final String COL_NAME = "name";
public static final String COL_ADDRESS = "address";
public static final String COL_MARKS = "marks";


protected static final String CREATE_QUERY = "CREATE TABLE " + FRIEND_TABLE +
        " (" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "   +
        COL_NAME + " TEXT, " + COL_ADDRESS + " TEXT," +
        COL_MARKS + " TEXT)";

the sorting logic is in a PrefrenceActivity that looks like :

<?xml version="1.0" encoding="utf-8"?>


<PreferenceScreen xmlns:android="http://schemas.android.com/apk/res/android" >
<ListPreference
        android:dialogTitle="Choose a sort order"
        android:key="sort_order"
        android:summary="Choose your preferred sort order"
        android:title="Sort Order" 
        android:entries="@array/sort_names"
        android:entryValues="@array/sort_clauses"/>

</PreferenceScreen>

the arrays are : 

<string-array name="sort_names">
    <item>By Name, Ascending</item>
    <item>By Name, Descending</item>
    <item>By Roll</item>
    <item>By Address, Ascending</item>
    <item>By Address, Descending</item>
</string-array>
<string-array name="sort_clauses">
    <item>name ASC</item>
    <item>name DESC</item>
    <item>marks ASC</item>
    <item>address ASC</item>
    <item>address DESC</item>
</string-array>

can any one find the solution of this mentioned bug please!

Upvotes: 1

Views: 102

Answers (1)

mu is too short
mu is too short

Reputation: 434616

This isn't a bug in SQLite or Android, this is a bug in your database schema. You've defined your marks column as TEXT and the string "100" sorts before the string "89" (in ascending order) even though the number 100 sorts after the number 89; the sorting looks backwards in descending order for the same reason. Your marks column should probably be an integer, then your sorting will work the way you expect it to.

Upvotes: 4

Related Questions