Issues writing spinner values to SQLite


Last Updated: 2011-08-11 15:44:10
  1. sway1101

    sway1101 New Member

    Hey everyone, I have been working on an app for some time now and am stuck. This app is a mileage tracking app for my personal use at my work. I have 2 spinners set up with locations so that I can choose a beginning location and an ending location for my trip. The spinners populate their values from a SQLite database and this works fine.

    The problem I am having is writing the spinner items that have been selected to a new table in the database. No matter what I do only the 2nd spinner value is recorded. Let me give an example;

    Spinner Configuration1:

    Spinner1 = Liberty
    Spinner2 = Freedom

    Database Output
    Beginning Location Ending Location
    Freedom Freedom

    Spinner Configuration2:

    Spinner1 = Westar
    Spinner2 = Rainbow

    Database Output
    Beginning Location Ending Location
    Rainbow Rainbow


    So as you can see no matter what is chosen in the first spinner the end result is that the database is populated with the 2nd spinner's value in both the beginning and ending location fields.

    Here is my code. Please help!


    Code (Text):
    1. package com.shane.projects;
    2.  
    3. import android.database.Cursor;
    4. import android.os.Bundle;
    5. import android.text.Editable;
    6. import android.view.View;
    7. import android.widget.AdapterView;
    8. import android.widget.Button;
    9. import android.widget.DatePicker;
    10. import android.widget.EditText;
    11. import android.widget.SimpleCursorAdapter;
    12. import android.widget.Spinner;
    13. import android.widget.TimePicker;
    14.  
    15. public class MileageTrackerLogATripActivity extends MileageTrackerActivity {
    16.     /** Called when the activity is first created. */
    17.  
    18.     public MileageDbAdapter mDbHelper;
    19.      
    20.    
    21.     @Override
    22.     public void onCreate(Bundle savedInstanceState) {
    23.         super.onCreate(savedInstanceState);
    24.         setContentView(R.layout.log_a_trip);
    25.         mDbHelper = new MileageDbAdapter(this);
    26.         mDbHelper.open();
    27.         fillData();
    28.      
    29.    
    30.        
    31.        
    32.   }
    33.     //Fills the locations spinners with locations from the location table
    34.     private void fillData(){
    35.         Cursor locationsCursor = mDbHelper.fetchAllLocations();
    36.         startManagingCursor(locationsCursor);
    37.         String[] from = new String[]{MileageDbAdapter.LOCATIONS_COLUMN1};
    38.         int[] to = new int[]{android.R.id.text1};
    39.         SimpleCursorAdapter locations =
    40.             new SimpleCursorAdapter(this, android.R.layout.simple_spinner_item,
    41.                     locationsCursor, from, to);
    42.         locations.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item );
    43.         Spinner origins = (Spinner) findViewById(R.id.originSpinner);
    44.         Spinner destinations = (Spinner) findViewById(R.id.destinationSpinner);
    45.         destinations.setAdapter(locations);
    46.         origins.setAdapter(locations);
    47.        
    48.          
    49.        
    50.        
    51.    
    52.     //When the Log Trip button is pressed, settings are saved to DB
    53.     final Button logTripBtn = (Button) findViewById(R.id.logTripBTN);
    54.       logTripBtn.setOnClickListener(new View.OnClickListener() {
    55.           public void onClick(View v) {
    56.              
    57.               DatePicker pickdate = (DatePicker) findViewById(R.id.datePicker1);
    58.               final int monthPicked = pickdate.getMonth();
    59.               final int dayPicked = pickdate.getDayOfMonth();
    60.              
    61.               TimePicker pickTime = (TimePicker) findViewById(R.id.timePicker1);
    62.               final int hourPicked = pickTime.getCurrentHour();
    63.               final int minutePicked = pickTime.getCurrentMinute();
    64.              
    65.               final EditText startingMileage = (EditText) findViewById(R.id.beginningMileageET);
    66.               final String startMiles = startingMileage.getText().toString();
    67.              
    68.               final EditText endingMileage = (EditText) findViewById(R.id.endingMileageET);
    69.               final String endMiles = (String) endingMileage.getText().toString();
    70.              
    71.               EditText tripComments = (EditText) findViewById(R.id.commentsET);
    72.               final String comment = (String) tripComments.getText().toString();
    73.              
    74.            //Gets and stores the selected spinner values
    75.             Spinner originLocationSpinner = (Spinner) findViewById(R.id.originSpinner);
    76.             Spinner destinationLocationSpinner = (Spinner) findViewById(R.id.destinationSpinner);
    77.             final String originSpinnerSelected;
    78.             final String destinationSpinnerSelected;
    79.             Cursor c1 = (Cursor)(originLocationSpinner.getSelectedItem());
    80.             Cursor c2 = (Cursor)(destinationLocationSpinner.getSelectedItem());
    81.             if ((c1 != null) && (c2 != null)) {
    82.                 originSpinnerSelected = c1.getString(c1.getColumnIndex(mDbHelper.LOCATIONS_COLUMN1));
    83.                 destinationSpinnerSelected = c2.getString(c2.getColumnIndex(mDbHelper.LOCATIONS_COLUMN1));
    84.              
    85.               //Calls method in mileagDbAdapter.java that inserts the trip information into the database
    86.               mDbHelper.logTripInfo(originSpinnerSelected, destinationSpinnerSelected, startMiles, endMiles, monthPicked, dayPicked, hourPicked, minutePicked, comment);
    87.          
    88.             }}
    89.       });
    90.  
    91. }
    92. }
    93.  
    94.    
    95.    
    96.  
    Here is my dbadapter class

    Code (Text):
    1. package com.shane.projects;
    2.  
    3. import android.content.Context;
    4. import android.database.Cursor;
    5. import android.database.sqlite.SQLiteDatabase;
    6. import android.database.sqlite.SQLiteOpenHelper;
    7.  
    8.  
    9. public class MileageDbAdapter {
    10.     //Declare database constants
    11.     private static final String DATABASE_NAME = "MileageTrackerDB";
    12.     private static final String DATABASE_TABLE1 = "locations";
    13.     private static final String DATABASE_TABLE2 = "tripLog";
    14.     private static final int DATABASE_VERSION = 1;
    15.    
    16.     //Declare locations table constants
    17.     public static final String LOCATIONS_COLUMN1 = "location";
    18.     public static final String LOCATIONS_ROWID = "_id";
    19.    
    20.     //Declare tripLog table constants
    21.     public static final String TRIPLOG_COLUMN1 = "originLocation";
    22.     public static final String TRIPLOG_COLUMN2 = "destinationLocation";
    23.     public static final String TRIPLOG_COLUMN3 = "startingMileage";
    24.     public static final String TRIPLOG_COLUMN4 = "endingMileage";
    25.     public static final String TRIPLOG_COLUMN5 = "tripDate";
    26.     public static final String TRIPLOG_COLUMN6 = "tripTime";
    27.     public static final String TRIPLOG_COLUMN7 = "comments";
    28.     public static final String TRIPLOG_ROWID = "_id";
    29.    
    30.     private DatabaseHelper mDbHelper;
    31.     private SQLiteDatabase mDb;
    32.    
    33.     //Query to create Locations table stored in constant
    34.     private static final String LOCATIONS_TABLE_CREATE = "create table " + DATABASE_TABLE1  + " ("
    35.     + LOCATIONS_ROWID + " integer primary key autoincrement, "
    36.     + LOCATIONS_COLUMN1 + " text not null);";
    37.      
    38.    
    39.    
    40.     //Query to create TripLog table stored in constant
    41.     private static final String TRIPLOG_TABLE_CREATE = "create table " + DATABASE_TABLE2  + " ("
    42.     + TRIPLOG_ROWID + " integer primary key autoincrement, "
    43.     + TRIPLOG_COLUMN1 + " text, "
    44.     + TRIPLOG_COLUMN2 + " text, "
    45.     + TRIPLOG_COLUMN3 + " text, "
    46.     + TRIPLOG_COLUMN4 + " text, "
    47.     + TRIPLOG_COLUMN5 + " integer, "
    48.     + TRIPLOG_COLUMN6 + " integer, "
    49.     + TRIPLOG_COLUMN7 + " text);";
    50.                            
    51.     private final Context mCtx;
    52.     public MileageDbAdapter(Context ctx) {
    53.         this.mCtx = ctx;
    54.     }
    55.     //Start Nested Class to create Database
    56.     private static class DatabaseHelper extends SQLiteOpenHelper{
    57.         DatabaseHelper(Context context) {
    58.             super(context, DATABASE_NAME, null, DATABASE_VERSION);
    59.            
    60.         }
    61.         //Create Tables and populate starting values
    62.         @Override
    63.         public void onCreate(SQLiteDatabase db) {
    64.             db.execSQL(LOCATIONS_TABLE_CREATE);
    65.             db.execSQL(TRIPLOG_TABLE_CREATE);
    66.             db.execSQL("INSERT INTO locations (location) VALUES (\'Freedom\')");
    67.             db.execSQL("INSERT INTO locations (location) VALUES (\'Liberty\')");
    68.             db.execSQL("INSERT INTO locations (location) VALUES (\'Estrella\')");
    69.             db.execSQL("INSERT INTO locations (location) VALUES (\'Westar\')");
    70.             db.execSQL("INSERT INTO locations (location) VALUES (\'Rainbow\')");
    71.            
    72.         }
    73.         @Override
    74.         public void onUpgrade(SQLiteDatabase db, int oldVersion,
    75.                 int newVersion) {
    76.            
    77.         }
    78.        
    79.     }
    80.  
    81. public MileageDbAdapter open() throws android.database.SQLException{
    82.     mDbHelper = new DatabaseHelper(mCtx);
    83.     mDb = mDbHelper.getWritableDatabase();
    84.     return this;
    85. }
    86. public void close(){
    87.     mDbHelper.close();
    88. }
    89. public Cursor fetchAllLocations() {
    90.     return mDb.query(DATABASE_TABLE1, new String[] {LOCATIONS_COLUMN1, LOCATIONS_ROWID}, null, null, null, null, null);
    91. }
    92.    
    93. public void logTripInfo(String originSpinnerSelected, String destinationSpinnerSelected, String startMiles, String endMiles, int month, int day, int hour, int minute, String com){
    94.     mDb.execSQL("INSERT into tripLog (" + TRIPLOG_COLUMN1 + ", " + TRIPLOG_COLUMN2 + ", " + TRIPLOG_COLUMN3 + ", "
    95.             +  TRIPLOG_COLUMN4 + ", " + TRIPLOG_COLUMN5 + ", " + TRIPLOG_COLUMN6 + ", " + TRIPLOG_COLUMN7 + ") " +
    96.                     "VALUES (\'" + originSpinnerSelected + "\', \'" + destinationSpinnerSelected + "\', \'" + startMiles + "\', \'" +
    97.                     endMiles + "\', \'" + month + "," + day + "\', \'" + hour + ":" + minute + "\', \'" + com + "\')");  
    98. }                    
    99.  
    100. }
    101.  

    Advertisement
  2. mills2533

    mills2533 Well-Known Member

    Have you tried creating two cursors and two simplecursoradapters instead of using "locations" for both spinners?

Share This Page