2

I am using a number of Spinners for user to input data which is then saved into SQLite database. Each Spinner is filled with data from the database tables.

I want to control the order in which the data is displayed - for example, below is the 'quantity' Spinner code:

//          QUANTITY SPINNER

    Cursor quantityCursor = rmDbHelper.fetchAllQuantities();
    startManagingCursor(quantityCursor);

    // create an array to specify which fields we want to display
    String[] from6 = new String[]{RMDbAdapter.QUANTITY_FORM_TEXT};
    int[] to6 = new int[]{android.R.id.text1};

    // create simple cursor adapter
    SimpleCursorAdapter quantitySpinnerAdapter = new SimpleCursorAdapter(this, android.R.layout.simple_spinner_item, quantityCursor, from6, to6 );

    quantitySpinnerAdapter.setDropDownViewResource( android.R.layout.simple_spinner_dropdown_item );
    // get reference to our spinner
    quantitySpinner.setAdapter(quantitySpinnerAdapter);

    if (damagedComponentId > 0) { // Set spinner to saved data

        int spinnerPosition = 0; 

        for (int i = 0; i < quantitySpinner.getCount(); i++)  
        { 
             Cursor cur = (Cursor)(quantitySpinner.getItemAtPosition(i)); 

             //--When your bind you data to the spinner to begin with, whatever columns you 
             //--used you will need to reference it in the cursors getString() method... 

             //--Since "getString()" returns the value of the requested column as a String--  
             //--(In my case) the 4th column of my spinner contained all of my text values  
             //--hence why I set the index of "getString()" method to "getString(3)" 

             int quantitySpinnerItem = cur.getInt(1); 

             if(quantitySpinnerItem == quantitySpinnerData) 
             { 
                //--get the spinner position-- 
                spinnerPosition = i; 
                break; 
              } 
         }       
        quantitySpinner.setSelection(spinnerPosition); 
    }

    else { // Set spinner to default

        int spinnerPosition = 0; 

        for (int i = 0; i < quantitySpinner.getCount(); i++)  
        { 
            Cursor cur = (Cursor)(quantitySpinner.getItemAtPosition(i));

            int quantitySpinnerItem = cur.getInt(1);

            if(quantitySpinnerItem == 1)
            { 
                //--get the spinner position-- 
                spinnerPosition = i;
                break; 
            } 
         }       
        positionSpinner.setSelection(spinnerPosition);
    }

Now this works fine except that it sorts the data as follows: 1, 10, 11, 12, 13, 14 ,15 ,16 ,17 ,18 ,19, 2, 20, 21 etc. Obviously I want it to show 1, 2, 3, etc.

Now I know I could use 'NumberPicker' (instead of Spinner) or I could use 01, 02, 03 to get round the default sorting, but is there a way to change the order the Spinner displays the data?

Two options I have seen which may work (but I can't see where to add to my code):

1) Use Collections.sort(SourceArray) as indicated here: Spinner data sorting in Android. However, can't make this work with my Spinner.

or maybe this:

2) set "order by id" when querying the database as advised here: Rearrange list view items in SimpleCursorAdapter. But don't see where you would do this..

Any advice much appreciated..

EDIT - below is the code from my Database Helper class as requested:

public Cursor fetchAllQuantities() {
    return rmDb.query(QUANTITY_TABLE, new String[] {
            QUANTITY_ID,
            QUANTITY_FORM_TEXT},
            null, null, null, null, QUANTITY_ID);
}

I have now included the ORDER BY statement (QUANTITY_ID), but this has no effect on the order that the Spinner shows the data..

EDIT 2 - Scratch my last statement, it has ordered it correctly, however when the database is first created, it inputs the numbers in a default order. So below is where I input the data:

        db.execSQL("INSERT INTO " + QUANTITY_TABLE + "(" + QUANTITY_FORM_TEXT + ")" + 
                " SELECT '1' AS " + QUANTITY_FORM_TEXT +
                " UNION SELECT '2' " +
                " UNION SELECT '3' " +
                " UNION SELECT '4' " +
                " UNION SELECT '5' " +
                " UNION SELECT '6' " +
                " UNION SELECT '7' " +
                " UNION SELECT '8' " +
                " UNION SELECT '9' " +
                " UNION SELECT '10' " +
                " UNION SELECT '11' " +
                " UNION SELECT '12' " +
                " UNION SELECT '13' " +  etc.

But when I look at the database table in SQLite program it looks like this:

_id | quantity_form_text
 1  |  1
 2  |  10
 3  |  11
 4  |  12
 5  |  13
 6  |  14 etc.

So I guess my question now is how do you control the data getting put into the database in the first place??

Community
  • 1
  • 1
Scamparelli
  • 756
  • 1
  • 12
  • 28
  • 2
    Just add an ORDER BY to whatever `rmDbHelper.fetchAllQuantities()` does – David Snabel-Caunt Dec 02 '12 at 13:56
  • i want to help you do option 2, can we see your `fetchAllQuantities();` method? the order by is the last parameter if you're using the android query method. – mango Dec 02 '12 at 14:10
  • Hi David, Mango. Thanks for your response. I have added statement to ORDER BY bit of the query, however the Spinner still orders the data incorrectly. Am I missing something? – Scamparelli Dec 02 '12 at 18:59
  • if your cursor is in right order you should place it in cycle. Not quantitySpinner any more. – Danil Onyanov Dec 02 '12 at 19:14
  • Hi Danil, not sure I follow you. Is this in response to my second edit above? – Scamparelli Dec 02 '12 at 19:16

1 Answers1

0

Ok so I seem to have sorted it.

I have two spinners: one is 'Quantity Spinner' which is populated by database table that just contains numbers (as shown above) and the other is 'Position Spinner' which is populated by table with numbers 1-20 and text.

So with the quantity Spinner, I changed the Union Select statement (see above) to say 01, 02, 03 (rather than 1, 2, 3). As this is being entered into a column which only accepts integers, once it is populated onCreate, the database table shows 1, 2, 3 etc. and this is how it displays in the Spinner. Very good!

As the Position Spinner includes text, the column is set up to accept text. Again the Union Select statement was written using 01, 02, 03 etc, but this remains the same in the database (and when it fills the Spinner it shows 01, 02, 03 etc - this is fine for my needs).

In both cases, the solution both David and Mango suggested worked and it now pulls the data ordered by the incremental id and which gives the correct order for the numbers etc.

Hope this makes sense! Thanks, Dave.

Scamparelli
  • 756
  • 1
  • 12
  • 28