Not able to create table in android

Question

My application is unable to create a simple table. Please see the below code,

DatabaseHandler.java

public class DatabaseHandler extends SQLiteOpenHelper {

private static final int DB_VERSION = 1;
private static final String DB_NAME = "PASSENGER_INFO_DB";
private static final String TABLE_NAME = "PASSENGER_INFO";
private static final String TABLE_COLUMN_SEAT_NO = "SEAT_NO";
private static final String TABLE_COLUMN_NAME = "NAME";
private static final String TABLE_COLUMN_AGE = "AGE";
private static final String TABLE_COLUMN_GENDER = "GENDER";
private static final String TABLE_COLUMN_FOOD_PREF = "FOOD_PREF";

private static final String CREATE_PASSENGER_INFO_TABLE = "CREATE TABLE IF NOT EXISTS "
        + TABLE_NAME + " ( " + TABLE_COLUMN_SEAT_NO
        + " TEXT PRIMARY KEY, " + TABLE_COLUMN_NAME + " TEXT, "
        + TABLE_COLUMN_AGE + " INTEGER, " + TABLE_COLUMN_GENDER
        + " INTEGER, " + TABLE_COLUMN_FOOD_PREF + " TEXT" + ")";

public DatabaseHandler(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_PASSENGER_INFO_TABLE);
}
public void addPassengerInfo(PassengerInfo passInfo) {
    SQLiteDatabase addPass = this.getWritableDatabase();

    ContentValues values = new ContentValues();

    values.put(TABLE_COLUMN_SEAT_NO, passInfo.getM_seatNo());
    values.put(TABLE_COLUMN_NAME, passInfo.getM_name());
    values.put(TABLE_COLUMN_AGE, passInfo.getM_age());
    values.put(TABLE_COLUMN_GENDER, passInfo.getM_gender());
    values.put(TABLE_COLUMN_FOOD_PREF, passInfo.getM_foodPref());

    addPass.insert(TABLE_NAME, null, values);
    addPass.close();
}

in MainActivity.java

DatabaseHandler dbhandler = new DatabaseHandler(this);
addSeat = "1A";
passName = "NA";
passAge = 0;
passGender = -1;
passFoodPref = "NA";
dbhandler.addPassengerInfo(new PassengerInfo(addSeat, passName, passAge, passGender, passFoodPref));

(PassengerInfo.class has getter and setter methods for the fields)

And when I run this I'm getting the following error in logcat,

01-23 19:09:18.311: I/Database(1303): sqlite returned: error code = 1, msg = table PASSENGER_INFO has no column named SEAT_NO
01-23 19:09:18.370: E/Database(1303): Error inserting FOOD_PREF=NA NAME=NA AGE=0 GENDER=-1 SEAT_NO=1A

I have also checked the tables created in the database through adb shell, there's no table with name PASSENGER_INFO. Please help !

there must be a table named PASSENGER_INFO as the error message clearly states. where are you looking for the DB? — David M, Jan 23 '13 at 13:53
see http://stackoverflow.com/questions/10349337/creating-tables-in-sqlite-database-on-android — Rachel Gallen, Jan 23 '13 at 13:55
@DavidM No there's no table with name PASSENGER_INFO, I have searched it in /data/data/android.sample.com/databases/PASSENGER_INFO_DB.db. — Srujan Simha, Jan 23 '13 at 17:37
the error message `msg = table PASSENGER_INFO has no column named SEAT_NO` tells me that the table DOES exist...that the column is missing. otherwise the error message would be something like `table PASSENGER_INFO does not exist`. Question: did you originally have the table defined WITHOUT the SEAT_NO column...then add that column later??? — David M, Jan 23 '13 at 17:41
so: when you ran `sqlite3 PASSENGER_INFO_DB.db` you then ran `select * from sqlite_master where tbl_name='PASSENGER_INFO'`? i'd like to see the output of that query. Or even `select tbl_name from sqlite_master` just to prove (or not) the table exists. — David M, Jan 23 '13 at 18:03
there may be one of the reason, you create table statement like: "CREATE TABLE IF NOT EXISTS..." means it won't create table again if table already exist(Before you may create the table without column "SEAT_NO"). — Krishna Prasad, Jan 24 '13 at 05:47

score 0 · Answer 1 · answered Jan 23 '13 at 13:55

0

You are missing a semicon at last in create table

+ " TEXT" + ");";

answered Jan 23 '13 at 13:55

sheetal

3,014
2
31
45

score 0 · Answer 2 · answered Jan 23 '13 at 14:06

A semicolon after the ");" at the and of query is missing. P.s the way you are inserting data into database is not encouraged you should use Contentvalues for it

     ContentValues row = new ContentValues();
     row.put("SEAT_NO",tid);
     row.put("NAME", sid);
     row.put("AGE", rsubject);
     row.put("GENDER",rdescr);
     row.put("FOOD_PREF", day);


     long chk = db.insert("PASSENGER_INFO",null, row);

Moreover a proper query would be like

  db.execSQL(" CREATE TABLE " + DATABASE_MARKSTABLE + " (" +
                "SEAT_NO" + " TEXT PRIMARY KEY, " +
                "NAME" + " TEXT NOT NULL, " +
                "AGE" + " INTEGER NOT NULL, " +
                "GENDER" + " INTEGER NOT NULL, " +
                "FOOD_PREF" + " TEXT);"
        );

score 0 · Answer 3 · answered Jan 23 '13 at 17:52

i assume you created the table (PASSENGER_INFO) without the SEAT_NO column adding it at a later time. what you need to do is to increment the DB_VERSION and re-run the application. what will cause DatabaseHandler's onUpdate() to run. You didn't show that code, but hopefully it drops the table, then calls onCreate() to re-create the table (now with the SEAT_NO column).

Side Note: i would change the primary key of that table to _id as the way you have it you'll only be able to store one flight's worth of seats...of course the design is up to you.

score 0 · Accepted Answer · answered Jan 23 '13 at 17:58

Have you tried increasing your database number? Also, try changing this line

+ TABLE_COLUMN_SEAT_NO
    + " TEXT PRIMARY KEY, "

to

+ TABLE_COLUMN_SEAT_NO
    + " TEXT, "

Add a separate column that will represent the rowId, update your database version number and re-run.

Not able to create table in android

4 Answers4