App crashes when using Database

Question

I'm creating an app for use as coursework. I'm still very new to both Android development and using SQLite. The idea of the app is to allow the user to insert details of a series they a watching or reading, like title, episodes watched/aired, seasons etc.

Everything was going fine until I started to use SQLite. The app crashes at two separate points the first is on the list view on the home class, the second is when a button is pressed which calls the 'insert_tv' class.

DatabaseHandler with the createTable, insert and cursor methods.

public class DatabaseHandler extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;

private static final String DATABASE_NAME = "SeriesTracker";

private static final String TABLE_TV = "tv";
private static final String COLUMN_KEY ="ID";
private static final String COLUMN_TITLE = "title";
private static final String COLUMN_EPISODES_WATCHED = "episodes_watched";
private static final String COLUMN_EPISODES_AIRED = "episodes_aired";
private static final String COLUMN_SEASONS_WATCHED = "seasons_watched";
private static final String COLUMN_SEASONS_AIRED = "season_aired";
private static final String COLUMN_RATING = "rating";
private static final String COLUMN_DESCRIPTION = "description";

public DatabaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
 public void onCreate(SQLiteDatabase db){
     String CREATE_tv_TABLE = "CREATE TABLE " + TABLE_TV +"("
             + COLUMN_KEY + "INTEGER AUTOINCREMENT" + COLUMN_TITLE + " VARCHAR PRIMARY KEY, " + COLUMN_EPISODES_WATCHED +
             " TINYINT," + COLUMN_EPISODES_AIRED + " TINYINT," + COLUMN_SEASONS_WATCHED + " TINYINT,"
             + COLUMN_SEASONS_AIRED + " TINYINT," + COLUMN_RATING + " TINYINT," + COLUMN_DESCRIPTION
             + " VARCHAR," + ")";
     db.execSQL(CREATE_tv_TABLE);
 }
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL("DROP TABLE IF EXISTS " + TABLE_TV);

    onCreate(db);

}

public boolean insertTv(String title, int episodes_watched, int episodes_aired, int seasons_watched, int seasons_aired, int rating, String description){
    SQLiteDatabase db = getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_TITLE, title );
    contentValues.put(COLUMN_EPISODES_WATCHED, episodes_watched );
    contentValues.put(COLUMN_EPISODES_AIRED, episodes_aired);
    contentValues.put(COLUMN_SEASONS_WATCHED, seasons_watched );
    contentValues.put(COLUMN_SEASONS_AIRED, seasons_aired);
    contentValues.put(COLUMN_RATING, rating);
    contentValues.put(COLUMN_DESCRIPTION, description);
    db.insert(TABLE_TV, null, contentValues);
    return true;
}

public Cursor getTv() {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res = db.rawQuery("SELECT * FROM TABLE_TV", null );
    return res;
}

first crash

public class Home extends ListActivity {

DatabaseHandler dbHelper;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    dbHelper = new DatabaseHandler(this);
    String[] type={"TV", "Film", "Book"};//declares the array list for each of the sections
    setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, type));

}

protected void onListItemClick(ListView i, View v, int position, long id){
    switch(position){
        case 0: //statement which is executed on the first list item
            startActivity(new Intent(Home.this, tv.class));
            DatabaseHandler result = new DatabaseHandler(getApplicationContext());
            result.getTv();
            break;
        case 1:
            startActivity(new Intent(Home.this, film.class));
            break;
        case 2:
            startActivity(new Intent(Home.this, book.class));
            break;
        default:
    }
}

second crash

public class tv extends Activity {

DatabaseHandler dbHelper;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_tv);
    dbHelper = new DatabaseHandler(this);
    Button b = (Button) findViewById(R.id.add_tv);
    b.setOnClickListener(new View.OnClickListener() {

        public void onClick(View v) {
            startActivity(new Intent(tv.this, insert_tv.class));
        }
    });
}

the class being called via the button

public class insert_tv extends Activity {

DatabaseHandler dbHelper;

final EditText input1 = (EditText) findViewById(R.id.titletxt);
final String title = input1.getText().toString();

final EditText input2 = (EditText) findViewById(R.id.epswatched);
final String episodes_watched1 = input2.getText().toString();
int episodes_watched = Integer.parseInt(episodes_watched1);

final EditText input3 = (EditText) findViewById(R.id.eps_aired);
final String episodes_aired1 = input3.getText().toString();
int episodes_aired = Integer.parseInt(episodes_aired1);

final EditText input4 = (EditText) findViewById(R.id.seasons_watched);
final String seasons_watched1 = input4.getText().toString();
int seasons_watched = Integer.parseInt(seasons_watched1);

final EditText input5 = (EditText) findViewById(R.id.seasons_aired);
final String seasons_aired1 = input5.getText().toString();
int seasons_aired = Integer.parseInt(seasons_aired1);

final EditText input6 = (EditText) findViewById(R.id.ratetxt);
final String rating1 = input6.getText().toString();
int rating = Integer.parseInt(rating1);

final EditText input7 = (EditText) findViewById(R.id.destxt);
final String description = input7.getText().toString();

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_insert_tv);
    dbHelper = new DatabaseHandler(this);
    Button b=(Button)findViewById(R.id.btnsave_tv);
    b.setOnClickListener(new View.OnClickListener() {

        public void onClick(View v) {
            DatabaseHandler values = new DatabaseHandler(getApplicationContext());
            values.insertTv(title, episodes_watched, episodes_aired, seasons_watched, seasons_aired, rating, description);
        }
    });
}

}

Any help to fix the issue would be appreciated

EDIT here is the logcat for the first crash, this happens when the first switch case "TV" is pressed.

03-25 19:27:01.484 6356-6356/ljmu.ac.uk.seriestracker E/AndroidRuntime: FATAL EXCEPTION: main    
    Process: ljmu.ac.uk.seriestracker, PID: 6356
    android.database.sqlite.SQLiteException: near ")": syntax error (code 1): , while compiling: CREATE TABLE tv(IDINTEGER AUTOINCREMENTtitle VARCHAR PRIMARY KEY, episodes_watched INT,episodes_aired INT,seasons_watched INT,season_aired INT,rating INT,description VARCHAR,)
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1676)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1607)
    at ljmu.ac.uk.seriestracker.DatabaseHandler.onCreate(DatabaseHandler.java:37)
    at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
    at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
    at ljmu.ac.uk.seriestracker.DatabaseHandler.getTv(DatabaseHandler.java:62)
    at ljmu.ac.uk.seriestracker.Home.onListItemClick(Home.java:35)
    at android.app.ListActivity$2.onItemClick(ListActivity.java:319)
    at android.widget.AdapterView.performItemClick(AdapterView.java:300)
    at android.widget.AbsListView.performItemClick(AbsListView.java:1143)
    at android.widget.AbsListView$PerformClick.run(AbsListView.java:3044)
    at android.widget.AbsListView$3.run(AbsListView.java:3860)
    at android.os.Handler.handleCallback(Handler.java:739)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:135)
    at android.app.ActivityThread.main(ActivityThread.java:5255)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:652)

What's the issue. **Doesn't work** is not enough to solve it. Post your logcat and mark the lines on your code on which the exception occur. — Prerak Sola, Mar 25 '16 at 13:34

score 1 · Answer 1 · answered Mar 26 '16 at 12:04

android.database.sqlite.SQLiteException: near ")": syntax error (code 1): , while compiling: CREATE TABLE tv(IDINTEGER AUTOINCREMENTtitle VARCHAR PRIMARY KEY, episodes_watched INT,episodes_aired INT,seasons_watched INT,season_aired INT,rating INT,description VARCHAR,)

Syntax problems in your SQL:

Remove the , between the last column and closing ).
IDINTEGER should probably be ID INTEGER.
AUTOINCREMENT requires INTEGER PRIMARY KEY, e.g. ID INTEGER PRIMARY KEY AUTOINCREMENT. You can have only one PRIMARY KEY column so remove the PRIMARY KEY from the other column.
AUTOINCREMENTtitle you need a comma there.

score 0 · Answer 2 · answered Mar 25 '16 at 13:37

public class insert_tv extends Activity {
    final EditText input1 = (EditText) findViewById(R.id.titletxt);
    final String title = input1.getText().toString();

This is the wrong way to initialize variables

try with below one

public class insert_tv extends Activity {
    final EditText input1;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_insert_tv);
        input1 = (EditText) findViewById(R.id.titletxt);
        ..
        Button b=(Button)findViewById(R.id.btnsave_tv);
        b.setOnClickListener(new View.OnClickListener() {

            public void onClick(View v) {
                String title = = input1.getText().toString();
                // likewise get all text from EditText and
                // then pass it to database values
                DatabaseHandler values = new
                        DatabaseHandler(getApplicationContext());
                values.insertTv(title, episodes_watched, episodes_aired,
                    seasons_watched, seasons_aired, rating, description);
            }
        });
    }

do this for all EditText.

Hope it'll help

App crashes when using Database

2 Answers2