Trouble Populating SQLite database with spinner values pls hlp

1//When the Log Trip button is pressed, settings are saved to DB
2 final Button logTripBtn = (Button) findViewById(R.id.logTripBTN);
3 logTripBtn.setOnClickListener(new View.OnClickListener() {
4 public void onClick(View v) {
5
6 DatePicker pickdate = (DatePicker) findViewById(R.id.datePicker1);
7 final int monthPicked = pickdate.getMonth();
8 final int dayPicked = pickdate.getDayOfMonth();
9
10 TimePicker pickTime = (TimePicker) findViewById(R.id.timePicker1);
11 final int hourPicked = pickTime.getCurrentHour();
12 final int minutePicked = pickTime.getCurrentMinute();
13
14 final EditText startingMileage = (EditText) findViewById(R.id.beginningMileageET);
15 final String startMiles = startingMileage.getText().toString();
16
17 final EditText endingMileage = (EditText) findViewById(R.id.endingMileageET);
18 final String endMiles = (String) endingMileage.getText().toString();
19
20 EditText tripComments = (EditText) findViewById(R.id.commentsET);
21 final String comment = (String) tripComments.getText().toString();
22
23 //Gets and stores the selected spinner values
24 Spinner originLocationSpinner = (Spinner) findViewById(R.id.originSpinner);
25 Spinner destinationLocationSpinner = (Spinner) findViewById(R.id.destinationSpinner);
26 final String originSpinnerSelected;
27 final String destinationSpinnerSelected;
28 Cursor c1 = (Cursor)(originLocationSpinner.getSelectedItem());
29 Cursor c2 = (Cursor)(destinationLocationSpinner.getSelectedItem());
30 if ((c1 != null) && (c2 != null)) {
31 originSpinnerSelected = c1.getString(c1.getColumnIndex(mDbHelper.LOCATIONS_COLUMN1));
32 destinationSpinnerSelected = c2.getString(c2.getColumnIndex(mDbHelper.LOCATIONS_COLUMN1));
33
34 //Calls method in mileagDbAdapter.java that inserts the trip information into the database
35 mDbHelper.logTripInfo(originSpinnerSelected, destinationSpinnerSelected, startMiles, endMiles, monthPicked, dayPicked, hourPicked, minutePicked, comment);
36
37 }}
38 });

1package com.shane.projects;
2
3import android.content.Context;
4import android.database.Cursor;
5import android.database.sqlite.SQLiteDatabase;
6import android.database.sqlite.SQLiteOpenHelper;
7
8
9public 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
81public MileageDbAdapter open() throws android.database.SQLException{
82 mDbHelper = new DatabaseHelper(mCtx);
83 mDb = mDbHelper.getWritableDatabase();
84 return this;
85}
86public void close(){
87 mDbHelper.close();
88}
89public Cursor fetchAllLocations() {
90 return mDb.query(DATABASE_TABLE1, new String[] {LOCATIONS_COLUMN1, LOCATIONS_ROWID}, null, null, null, null, null);
91}
92
93public 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}