Creating two tables in SQLite database in Android application

Question

I am trying to create two tables in SQLite database in my Android application but it shows the following errors. Basically, the second table that I am trying to create won't be created. Any help would be appreciated.

This is the error that I got:

02-22 09:16:33.005 22404-22404/proed.hotelbooking E/SQLiteLog: (1) no such table: room 02-22 09:16:33.007 22404-22404/proed.hotelbooking
  E/SQLiteDatabase: Error inserting room_id=1 room_price=1000 room_type=Single hotel_id=1
  android.database.sqlite.SQLiteException: no such table: room (code 1): ,
    while compiling: INSERT INTO room(room_id,room_price,room_type,hotel_id) VALUES (?,?,?,?)
    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.insertWithOnConflict(SQLiteDatabase.java:1472)
    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1343)
    at proed.hotelbooking.HotelDatabaseHelper.insertRoom(HotelDatabaseHelper.java:70)
    at proed.hotelbooking.RoomInfoActivity.onRoomButtonClick(RoomInfoActivity.java:42)
    at java.lang.reflect.Method.invoke(Native Method)
    at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:288)
    at android.view.View.performClick(View.java:6256)
    at android.view.View$PerformClick.run(View.java:24701)
    at android.os.Handler.handleCallback(Handler.java:789)
    at android.os.Handler.dispatchMessage(Handler.java:98)
    at android.os.Looper.loop(Looper.java:164)
    at android.app.ActivityThread.main(ActivityThread.java:6541)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:767)

This is my database helper class. Am I missing something?

public class HotelDatabaseHelper extends SQLiteOpenHelper {

    private static int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "hotels";
    private static final String TABLE_NAME = "hotel";
    private static final String TABLE_ROOM = "room";
    private static final String col_id = "id";
    private static final String col_name = "hotelName";
    private static final String col_location = "location";
    private static final String room_id = "room_id";
    private static final String room_type = "room_type";
    private static final String room_price = "room_price";
    private static final String hotel_id = "hotel_id";
    private static final String hotel_table = "CREATE TABLE `hotel` ( `id` INTEGER primary key not null, `hotelName` VARCHAR not null, `location` VARCHAR not null )";
    private static final String room_table ="CREATE TABLE `room` ( `room_id` INTEGER primary key not null, `room_type` VARCHAR not null, `room_price` VARCHAR not null, `hotel_id` VARCHAR not null )";
    SQLiteDatabase db;
    private static final String info = "DATABASE";
    public HotelDatabaseHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(hotel_table);

        db.execSQL(room_table);
        Log.i(info, "Created!!!!!!!!!");
    }

    public void insertHotel(Hotels h)
    {
        db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(col_id, h.getId());
        values.put(col_name, h.getHotelName());
        values.put(col_location, h.getLocation());

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

    public void insertRoom(Hotels hotel)
    {
        db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(room_id, hotel.getRoom_id());
        values.put(room_price, hotel.getRoom_price());
        values.put(room_type, hotel.getRoom_type());
        values.put(hotel_id, hotel.getHotel_id());

        db.insert(TABLE_ROOM, null, values);
        db.close();
    }

    public Cursor getListContents()
    {
        db = this.getWritableDatabase();
        Cursor data = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        return data;
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ROOM);
        onCreate(db);
    }
}

This is my class to insert data:

public class RoomInfoActivity extends AppCompatActivity {
    HotelDatabaseHelper helper = new HotelDatabaseHelper(this);
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_room_info);
    }

    public void onRoomButtonClick(View v)
    {
        if (v.getId()==R.id.addRoom) {
            EditText id = findViewById(R.id.roomId);
            EditText type = findViewById(R.id.roomType);
            EditText price = findViewById(R.id.roomPrice);
            EditText hotel_id = findViewById(R.id.hotelID);

            String room_id = id.getText().toString();
            String room_type = type.getText().toString();
            String room_price = price.getText().toString();
            String hotelID = hotel_id.getText().toString();

            if (TextUtils.isEmpty(room_id) || TextUtils.isEmpty(room_type) || TextUtils.isEmpty(room_price) || TextUtils.isEmpty(hotelID)) {
                Toast.makeText(this, "Please do not leave any field blank! ", Toast.LENGTH_SHORT).show();
                return;
            } else {
                Hotels hotels = new Hotels();
                hotels.setRoom_id(room_id);
                hotels.setRoom_price(room_price);
                hotels.setRoom_type(room_type);
                hotels.setHotel_id(hotelID);

                helper.insertRoom(hotels);
                //Toast.makeText(this, "Data Added To The Database", Toast.LENGTH_SHORT).show();
            }
        }
    }

    public void onViewRoomButtonClick(View view)
    {
        if (view.getId()==R.id.viewRoom)
        {
            Intent intent = new Intent(RoomInfoActivity.this, ViewRoomContents.class);
            startActivity(intent);
        }
    }
}

SQLite does not support VARCHAR data type please change it to text — Ganesh Gudghe, Feb 22 '18 at 04:36
I changed it to TEXT but still not working. Although the datatype is VARCHAR, the first table "hotel" is created and works fine. The problem occurs when trying to create second table "room". — TesseractT.Shailab, Feb 22 '18 at 04:47
did you increment the DATABASE_VERSION after you made changes in your table? — johnguild, Feb 22 '18 at 05:15
@Ganesh It does. Lengths just aren't enforced. https://www.sqlite.org/faq.html#q9 — OneCricketeer, Feb 22 '18 at 05:20
Note that SQLiteOpenHepler is not recommended anymore for simple database access https://developer.android.com/training/data-storage/room/index.html — OneCricketeer, Feb 22 '18 at 05:26
@johnguild how do i increase the database_version. Can you elaborate? — TesseractT.Shailab, Feb 22 '18 at 05:42
yup everytime you make changes on your database structure you need to increase the version to trigger the update. — johnguild, Feb 22 '18 at 06:39
@TesseractT.Shailab - The problem is with statement HotelDatabaseHelper helper = new HotelDatabaseHelper(this); You have to move this in onCreate() after setContentView(). The context you are passing in HotelDatabaseHelper, is not yet attached with activity. — Vishu, Feb 22 '18 at 09:02

score 0 · Answer 1 · answered Feb 22 '18 at 05:18

0

If you modified the database, for example add/remove table/column, you need to uninstall the app from the emulator/device and then reinstall it.

If that not works, just go to File and click on Invalidate Caches/Restart

answered Feb 22 '18 at 05:18

armen

453
1
4
11

score -1 · Answer 2 · answered Feb 22 '18 at 04:10

-1

I've checked your DatabaseHelper and it has no problem. Could you show the code for init database and insert data? Maybe the issue come from initial steps.

answered Feb 22 '18 at 04:10

Raymond

39
3

I have added the code which inserts the data. Can you have a look and tell me what might be the problem? – TesseractT.Shailab Feb 22 '18 at 04:35
there is no problem in insert statement – Ganesh Gudghe Feb 22 '18 at 04:37
problem is varchar datatype – Ganesh Gudghe Feb 22 '18 at 04:38
varchar is not the problem. the code run well. I think you need to delete all application data and try again. If you are using android M or higher, add android:allowBackup=false to manifest and reinstall application – Raymond Feb 22 '18 at 06:01

Creating two tables in SQLite database in Android application

2 Answers2