How to save (and later retrieve) the selected item of a spinner to SQLite

Question

Alright... I've had enough.

I'm thoroughly frustrated.

So I'd rather ask for help instead of a new monitor.

...And those are VERY expensive here.

Long story short... I have a database. And a table.

private String DEFINE_PROP_TYPES = "CREATE TABLE " + TABLE_PROP_TYPES + "("
        + TABLE_ID + " INTEGER PRIMARY KEY, "
        + TABLE_PROP_TYPE_NAME + " TEXT NOT NULL"
        + ")";

With an 'Adapter' class thrown in for good measure to manage it.

public abstract class DBAdapter 
{
    static public final String C_COLUMN_ID = "_id";

    protected Context context;
    protected DBHelper dbHelper;
    protected SQLiteDatabase db;
    protected String managedTable;
    protected String[] columns;

    public String getTableManaged() 
    {
        return managedTable;
    }

    public void setTableManaged(String managedTable) 
    {
        this.managedTable = managedTable;
    }

    public void setColumns(String[] columns)
    {
        this.columns = columns;
    }

    public DBAdapter(Context context)
    {
        this.context = context;
    }

    public void close()
    {
        dbHelper.close();
    }

    public DBAdapter open() throws SQLException
    {
        dbHelper = new DBHelper(context);
        db = dbHelper.getWritableDatabase();
        return this;
    }

    public Cursor getList()
    {
        Cursor c = db.query(true, managedTable, columns, null, null, null, null, null, null);

        return c;       
    }

    public long insert(ContentValues reg)
    {
        return 0;
    }
}
public class PropTypesDBAdapter extends DBAdapter
{
    static public final String C_TABLE_PROP_TYPES = "PROP_TYPES";

    static public final String C_COLUMN_ID = "_id",
        C_COLUMN_PROP_TYPES_NAME = "re_prop_type";

    public PropTypesDBAdapter(Context context)
    {
        super(context);
        this.setTableManaged(C_TABLE_PROP_TYPES);
        this.setColumns(new String[] { C_COLUMN_ID,
            C_COLUMN_PROP_TYPES_NAME });
    }

    public long insert(ContentValues reg)
    {
        if (db == null)
        {
            open();
        }

        return db.insert(C_TABLE_PROP_TYPES, null, reg);
    }
}

And besides this pile of cute I have an activity class.

With spinners.

public class PropDetailActivity extends Activity implements LocationListener
{
    // insert here some blah-blah constants not needed by spinners

    private PropDBAdapter mHouses;
    private RatingsDBAdapter mRatings;
    private PropTypesDBAdapter mPropTypes;
    private Cursor mCursorHouses, 
        mCursorRatings,
        mCursorPropTypes;

    long mPropType;

    private long mPropId;

    private Spinner spinnerRating, spinnerType;
    AdapterView.OnItemSelectedListener spnLstPropType, spnLstRating;

    protected void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_house_detail);

        Intent intent = getIntent();
        Bundle extra = intent.getExtras();

        if (extra == null)
        {
            return;
        }

        // Figure all view widgets being retrieved here, including...

        spinnerRating = (Spinner) findViewById(R.id.spinnerRating);
        spinnerType = (Spinner) findViewById(R.id.spinnerType);

        // Create adapter and cursor-y things here

        mHouses = new PropDBAdapter(this);
        mHouses.open();

        // And now, for the juicy, deliciously irritating stuff:

        String[] from = new String[] { PropTypesDBAdapter.C_COLUMN_PROP_TYPES_NAME };

        int[] to = new int[] { android.R.id.text1 };

        mPropTypes = new PropTypesDBAdapter(this);
        mPropTypes.open();

        mCursorPropTypes = mPropTypes.getList();

        @SuppressWarnings("deprecation")
        SimpleCursorAdapter adapterPropTypes = new SimpleCursorAdapter(this, 
                android.R.layout.simple_spinner_item, 
                mCursorPropTypes, 
                from,       /*new String[] { RatingsDBAdapter.C_COLUMN_RATING_NAME }, */
                to);        /*new int[] { android.R.id.text1 } */

        adapterPropTypes.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);

        spinnerType.setAdapter(adapterPropTypes);

        spinnerRating.setSelection(pos);

        spnLstPropType = new AdapterView.OnItemSelectedListener()
        {

            @Override
            public void onItemSelected(AdapterView<?> parent, View view,
                    int pos, long id) 
            {
                mPropType = id;
            }

            @Override
            public void onNothingSelected(AdapterView<?> arg0) { }
        };
        spinnerType.setOnItemSelectedListener(spnLstPropType);

    private int getItemPositionById(Cursor c, long id, DBAdapter adapter)
    {
        for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext())
        {
            if (c.getLong(c.getColumnIndex(DBAdapter.C_COLUMN_ID)) == id)
            {
                return c.getPosition();
            }
        }

        return 0;
    } 

    private void query(long id) 
    {
        mCursorHouses = mHouses.getRecord(id);

        // Figure values being retrieved and set on their widgets instead of this comment... and now...

        mPropType = mCursorHouses.getInt(mCursorHouses.getColumnIndex(PropDBAdapter.C_PROP_TYPE_ID));

        spinnerType.setSelection(
            getItemPositionById(
                    mCursorRatings, 
                    mCursorHouses.getColumnIndex(PropDBAdapter.C_PROP_TYPE_ID),
                    mPropTypes
                )
            );

    private void save() 
    {
        ContentValues reg = new ContentValues();

        // Read: values being put into 'reg'... eventually it should reach this:

        reg.put(PropDBAdapter.C_PROP_TYPE_ID, mPropType);

        try
        {
            if (mFormMode == PropListActivity.C_CREATE)
            {
                mHouses.insert(reg);
                Toast.makeText(PropDetailActivity.this, R.string.house_create_notice, Toast.LENGTH_LONG).show();
            }
            else if (mFormMode == PropListActivity.C_EDIT)
            {
                Toast.makeText(PropDetailActivity.this, R.string.house_edit_notice, Toast.LENGTH_LONG).show();

                reg.put(PropDBAdapter.C_COLUMN_ID, mPropId);

                long resultCode = mHouses.update(reg);
                Log.i(this.getClass().toString(), "Database operation result code: " + resultCode);         
            }
        }
        catch(SQLException e)
        {
            Log.i(this.getClass().toString(), e.getMessage());
        }

        setResult(RESULT_OK);
        finish();
    }
}

Spinners are being bad boys. Lazy bad boys on top of that.

They do load up the data -a list of real estate property types- they are meant to display.

After some spanking, that is.

But, hoping them to save THE VALUE YOU SELECT to SQLite? And to show THAT EXACT VALUE when fetching stuff back from the database?

Oh, no, no way no how.

They stubbornly stick to displaying always the same value upon activity startup.

So... please... I must draw upon your collective wisdom to save my sorry excuse for a project...

Pleasepleaseplease? :)

(IF you feel like diving into the whole uncut code, here's a GIT repository for you: https://github.com/CruxMDQ/Quoterv3)

Amusing writing aside what is actually the problem? Are you getting an error message or does it just not do what you want? — Rarw, Aug 27 '13 at 16:36
They stubbornly display the same value. Each and every time. Thanks for raising the issue, I'll edit that now. — Emiliano De Santis, Aug 27 '13 at 16:38
Computers aren's stuborn. They are dumb. In fact they are so dumb that they will do exactly what you tell them to every single time (half stolen from Mehran Sahami). I'll take a look at this now. — Rarw, Aug 27 '13 at 16:47
I think in your call to `spinnerType.setSelection(getItemPositionById())` your passing a wrong value to `id` parameter, your sending the index of the column instead of the value of the column — Raúl Juárez, Aug 27 '13 at 17:30
@Raúl: tried what you suggested. No such luck. :( I'm going to make a wishful assumption and say that you may feel curious and want to take a look at the entire code... just added a link to the GIT repository for that. — Emiliano De Santis, Aug 27 '13 at 18:46
@EmilianoDeSantis In your code (github), you haven't change the lines of the `setSelection(getItemPositionById())` and I found another bug, It is now corrected and working in my device, I will publish the answer — Raúl Juárez, Aug 27 '13 at 21:29
True, didn't update it. I get a CursorIndexOutOfBounds exception now. (Some 'improvement', huh.) Wanted to get it done before updating. — Emiliano De Santis, Aug 28 '13 at 14:10

Raúl Juárez · Accepted Answer · 2013-08-28T21:41:53.683

2

Checking your code, I think I found the problem, change the following lines in your query method in PopDetailActivity.java.
For spinnerRating do:

spinnerRating.setSelection(
    getItemPositionById(
        mCursorRatings, 
        mCursorHouses.getInt(mCursorHouses.getColumnIndex(PropDBAdapter.C_PROP_RATING_ID)),
        mRatings
    )
);

and for spinnerType do:

spinnerType.setSelection(
    getItemPositionById(
        mCursorPropTypes, 
        mCursorHouses.getInt(mCursorHouses.getColumnIndex(PropDBAdapter.C_PROP_TYPE_ID)),
        mPropTypes
    )
);

EDIT:

In your query method, you initialize mPropTypeId, with the call to getItemPositionById() but in that call the first parameter should be mCursorPropTypes instead of mCursorHouses

edited Aug 28 '13 at 21:41

answered Aug 27 '13 at 21:33

Raúl Juárez

2,129
1
19
17

After adding your suggestions -and making a couple of changes on the save() and query(long) methods-, I got the Ratings spinner working. The PropType one keeps throwing a CursorIndexOutOfBound exception. – Emiliano De Santis Aug 28 '13 at 14:28
I installed your application in my device, and the code I posted worked for both spinners, Is the code you provided in github the same you're working? – Raúl Juárez Aug 28 '13 at 14:32
In order to work, I didn't update the `save` method, only the `query` method, can you post you changes to `save` to take a look? – Raúl Juárez Aug 28 '13 at 15:04
Old code: `mPropTypeId = Integer.parseInt(spinnerType.getSelectedItem().toString()); reg.put(PropDBAdapter.C_PROP_TYPE_ID, mPropTypeId);` New code: `reg.put(PropDBAdapter.C_PROP_TYPE_ID, mPropTypeId);` – Emiliano De Santis Aug 28 '13 at 15:10
In new code, where are you initializing `mProptypeId`, this variable doesn't exist in the code I downloaded from github – Raúl Juárez Aug 28 '13 at 16:00
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/36407/discussion-between-emiliano-de-santis-and-raul-juarez) – Emiliano De Santis Aug 28 '13 at 17:55

score 0 · Answer 2 · answered Aug 27 '13 at 17:33

0

A few things:

(1) I don't really see anywhere above where you actually create a SQLite database or use the SQLiteOpenHelper class to access that data. Take a look at this tutorial. It uses a simple single table set up to store data. Once you create the database it should be easy to read and write from it. Verify that you actually have a database created.

(2) Where are your SQL queries to return the data you're looking for? Even if data is being added you need to make sure you are getting the right data with your Cursor when you're done. If you're getting the same values each time is it possible that you are simply adding new data every time and retrieving the same value with your cursor - i.e. you're not telling the cursor to get the newly added data becuase you keep grabing the same index?

If you need to replace the data that's there you should be using update queries and not inserts.

answered Aug 27 '13 at 17:33

Rarw

7,645
3
28
46

I cut out a lot of code to make this marginally readable. Guess it came back to bite me in the rear. The code for SQLite database creation and queries exists; if it didn't I would not get the spinners loaded with the values they *can* display. I've went and uploaded the whole stuff over at https://github.com/CruxMDQ/Quoterv3 if you feel like taking a look. – Emiliano De Santis Aug 27 '13 at 18:24
Cool I will take a look later – Rarw Aug 27 '13 at 18:50
1

What value are you displaying every time? The only thing in there that looks weird is your getItemByPosition method. I wonder if you're always getting the first value. – Rarw Aug 28 '13 at 02:40
It only displayed 'average' and 'chalet' for the first and second spinner respectively. The ratings spinner works now, the other one is throwing an exception I didn't get before. – Emiliano De Santis Aug 28 '13 at 14:35
Don't you need to get position onItemSelected and not id? Id is not necessarialy the numerical order of the contents of the spinner. – Rarw Aug 28 '13 at 15:36
Just tried retrieving the position parameter of onItemSelected instead of the id parameter. Didn't work either. – Emiliano De Santis Aug 28 '13 at 19:01
can you [verify the information is saved](http://stackoverflow.com/questions/6097135/is-it-possible-to-access-the-sqlite-database-of-an-android-app-on-my-phone) by looking at the raw db file – Rarw Aug 28 '13 at 19:11

How to save (and later retrieve) the selected item of a spinner to SQLite

2 Answers2