Simple spinner from database

opy001 · by **opy001** » Tue Sep 14, 2010 6:37 pm

I spent countless hours (well a few hours at least lol) researching this and finally figured it out after reading about 50 different ways that didnt work. Basically I wanted to pull a list of items from one table and link them to new items in another. So in table one I manually input data like car, bus, truck (just examples). This list will grow along with the spinner. In table two I manually enter names and want to choose if they drive a car, bus or truck etc. What I do is query table one passing the results to an ArrayList<String>. I then use that arraylist to fill the spinner. When the item is selected it adds the selection to my new form and when I add the data it appends the selected item to the new row in table 2. You wouldnt think this was so hard but for me and several others it was a bit tricky. I have seen alot of people asking about this but none of the solutions worked that well for me. Here is what I did.

I put this in my DatabaseQuery.java file. My DatabaseQuery.java creates the table and columns and also holds the formats for all my queries to simplify things in activities. It uses to the DBAdapter to create the database itself and have the background code in it (best way i can describe it).

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

                // this sets the format for my query I can use multiple times
                // without haveing to create the loop every time I want to query
        public ArrayList<String> getlist(String table, String[] keys, String selection, String[] 
        selectionArgs, String groupBy, String having, String sortBy, String sortOption){
 
                                // creates a new arraylist for the results
                ArrayList<String> list = new ArrayList<String>(); 
 
        // calls the DBAdapter for the cursor (again, best way I can describe it)
        Cursor results = database.getAllEntries(table, keys, selection, 
            selectionArgs, groupBy, having, sortBy, sortOption);
 
            // loop to get all rows
                if ( results!= null)
                {
                    if (results.moveToFirst())
                    {
                        do
                        {
                            String[] names = results.getColumnNames();
                            int length = names.length;
                            for(int i = length-1; i >= 0; i--) {
                            list.add(results.getString(i));
                        }
                    } while (results.moveToNext()); // move to next row
                } // if
            } // if
 
                    // adds the results to the array
            return list;
        }
 
Parsed in 0.035 seconds,  using GeSHi 1.0.8.4

This is my actual query in the activity to get my wanted data

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

        // opens the database
        DatabaseQuery query = new DatabaseQuery(this);
 
        // specific query to load one column from the table into an array for the spinner
        ArrayList<String> spinnerArray = query.getlist("table", new String[] {"column"}, null, 
                        null, null, null, "column", "ASC");
 
        // closes the database
        try {
                    query.destroy();
            } catch (Throwable e) {
                    e.printStackTrace();
            }
        }
 
Parsed in 0.032 seconds,  using GeSHi 1.0.8.4

This is how I populate the spinner with the query results

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

        // creates new spinner
        Spinner spinner = new Spinner(this);
 
        // creates new array adapter for the spinner 
        // and loads the query results into the simple_spinner_item layout
        ArrayAdapter<String> spinnerArrayAdapter = new ArrayAdapter<String>(
                this, android.R.layout.simple_spinner_item, spinnerArray);
 
        // loads the selected item into the spinner view in the layout
        spinnerArrayAdapter.setDropDownViewResource( android.R.layout.simple_spinner_dropdown_item );
 
        // links the spinner adapter to the spinner (best way I can describe it)
        spinner.setAdapter(spinnerArrayAdapter);
 
        // shows the spinner in the layout
        spinner = (Spinner) findViewById( R.id.spinnerview );
 
Parsed in 0.034 seconds,  using GeSHi 1.0.8.4

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

                                // send the selected spinner item to a string to append to database
                Item = spinner.getSelectedItem().toString();
 
Parsed in 0.034 seconds,  using GeSHi 1.0.8.4

Once the selected item is in its own string I then append it to be added to the database with the rest of the data I just manually entered.

I tried using the simplecursoradapter and it filled in the spinner fine but once I added it to the database it showed the address of the selected item instead of the item itself. So it showed up as android.database.sqlite.SQLiteCursor@43b8a9b0 instead of "car".

If I screwed up describing any of the steps please let me know and I will edit it. I am not the best at this as you can tell but I try my best

.

Thanks for reading my tutorial and I hope it helps someone out there.

Simple spinner from database

Simple spinner from database

Who is online