SQLite error when trying to Insert Values

Question

I'm new to android and trying to learn how to create and insert stuff into a database. I managed to create it and the app runs but I get this error whenever I try to insert stuff in:

Error inserting trip type=Hi title=Beach destination=Gotham
android.database.sqlite.SQLiteException near "type": synthax error(code 1):, while
compiling: INSERT INTO trips(trip type, title, destination) VALUES (?,?,?)

This is my PlanTrip activity database part:

private EditText editText1;
private EditText editText2;
private EditText editText3;

DBAdapter db = new DBAdapter(this);

try {           
    String destPath = "/data/data/" + getPackageName() + "/databases/Trips.db";
    File f = new File(destPath);            
    if (!f.exists()) {          
        CopyDB( getBaseContext().getAssets().open("db"), 
        new FileOutputStream(destPath));
    }
} catch (FileNotFoundException e) { 
     e.printStackTrace();
} catch (IOException e) { 
     e.printStackTrace();
    }

EditText titleTxt = (EditText)findViewById(R.id.editText1);
EditText destinationTxt = (EditText)findViewById(R.id.editText2);
EditText tripTypeTxt = (EditText)findViewById(R.id.editText3);

db.open();
long id = db.insertRecord(titleTxt.getText().toString(), 
destinationTxt.getText().toString(), tripTypeTxt.getText().toString());
db.close();

And my DBAdapter:

public class DBAdapter {

public static final String KEY_TRIPID = "id";
public static final String KEY_TITLE = "title";
public static final String KEY_DESTINATION = "destination";
public static final String KEY_TRIP_TYPE = "trip type";
public static final String KEY_STARTDATE = "start date";
public static final String KEY_ENDDATE = "end date";
private static final String TAG = "DBAdapter";

private static final String DATABASE_NAME = "Trips.db";
private static final String DATABASE_TABLE = "trips";
private static final int DATABASE_VERSION = 1;

// Database creation sql statement
/*private static final String DATABASE_CREATE = 
      "create table if not exists trips (trip_id integer primary key   autoincrement, " 
    + "title VARCHAR not null, destination VARCHAR not null, triptype VARCHAR not null,"
    + " startdate VARCHAR, enddate VARCHAR);";*/
private static final String DATABASE_CREATE = 
      "CREATE table if not exists " + DATABASE_TABLE + " (" + KEY_TRIPID
        + " integer PRIMARY KEY autoincrement," + KEY_TITLE + ","
        + KEY_DESTINATION + ", " + KEY_TRIP_TYPE + ");";

private final Context context;

private DatabaseHelper DBHelper;
private SQLiteDatabase db;


public DBAdapter(Context ctx) {
this.context = ctx;
DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper
{
  DatabaseHelper(Context context)
  {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  public void onCreate(SQLiteDatabase db) 
  {
      try
      {
          db.execSQL(DATABASE_CREATE);
      }
      catch (SQLException e)
      {
          e.printStackTrace();
      }
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
  {
      Log.w(TAG, "Upgrading database from version " + oldVersion + 
             " to " + newVersion + ", which will destroy all old data");
      db.execSQL("DROP TABLE IF EXISTS trips" );
      onCreate(db);
  }
}

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

//close db
  public void close()
{
  DBHelper.close();
}

  //insert record into db
  public long insertRecord(String title, String destination, String tripType)
{
  ContentValues initialValues = new ContentValues();
  initialValues.put(KEY_TITLE, title);
  initialValues.put(KEY_DESTINATION, destination);
  initialValues.put(KEY_TRIP_TYPE, tripType);
  Toast.makeText(context, "Record Added", Toast.LENGTH_SHORT).show();
  return db.insert(DATABASE_TABLE, null, initialValues);
}

//delete record
public boolean deleteRecord(long rowId)
{
  return db.delete(DATABASE_TABLE, KEY_TRIPID + "=" + rowId, null) > 0;
}

//get all records
public Cursor getAllRecords()
{
  return db.query(DATABASE_TABLE, new String[] {KEY_TRIPID, KEY_TITLE, KEY_DESTINATION, KEY_TRIP_TYPE},
          null, null, null, null, null);
}

//get specific record
public Cursor getRecord(long rowId) throws SQLException
{
  Cursor mCursor = 
        db.query(true, DATABASE_TABLE, new String[] {KEY_TRIPID, KEY_TITLE, KEY_DESTINATION, KEY_TRIP_TYPE},
                KEY_TRIPID + "=" + rowId, null, null, null, null, null);
  if (mCursor != null)
      mCursor.moveToFirst();
return mCursor; 
}

//update record
public boolean UpdateRecord (long rowId, String title, String destination, String tripType)
    {
  ContentValues args = new ContentValues();
  args.put(KEY_TITLE, title);
  args.put(KEY_DESTINATION, destination);
  args.put(KEY_TRIP_TYPE, tripType);
  return db.update(DATABASE_TABLE, args, KEY_TRIPID + "=" + rowId, null) > 0;
    }
} 

Answer 1

change this :

public static final String KEY_TRIPID = "_id";
public static final String KEY_TITLE = "title";
public static final String KEY_DESTINATION = "destination";
public static final String KEY_TRIP_TYPE = "trip_type";
public static final String KEY_STARTDATE = "start_date";
public static final String KEY_ENDDATE = "end_date";

for more take look here. And also you need to update the table structure using database version upgrade.

Answer 2

public static final String KEY_TRIP_TYPE = "trip type";

instead of

public static final String KEY_TRIP_TYPE = "trip_type";

because space is not allow in database field in sqlite.

and if you already created database table in assets db folder then don't create it in java file.and if you do not create table externally then use this:

private static final String DATABASE_CREATE = 
      "create table if not exists trips (trip_id integer primary key   autoincrement, " 
    + "title VARCHAR not null, destination VARCHAR not null, triptype VARCHAR not null,"
    + " startdate VARCHAR, enddate VARCHAR);

if this ans is useful to you,please vote up.

Answer 3

Your column name trip type has a space inbetween it. It means you are going to have to use ` around it. E.g. `trip type`. Or better yet, instead of spaces, replace the spaces with underscores.

E.g. public static final String KEY_TRIP_TYPE = "trip_type";

You can do this with your other columns as well.

Note: As you have changed the column names, you may have toclear data, or uninstall then reinstall the app.

Answer 4

It seems you have spaces in your db column names, use '_' instead:

public static final String KEY_TRIP_TYPE = "trip_type";