android.database.sqlite.SQLiteException: no such column: place (code 1 SQLITE_ERROR)

Question

when I run my application it shows no such column. I want to store and display an event in my application. I have added a new data (place) to the previous one I had which worked. But with this new entry, everything collapse. How can I please solve it. Thanks in advance

public class DbOpenHelper extends SQLiteOpenHelper {

private static final String CREATE_EVENTS_TABLE = "create table " + DBStructure.EVENT_TABLE_NAME + "( " + DBStructure.ID + "INTEGER PRIMARY KEY AUTOINCREMENT, " + DBStructure.EVENT+ " TEXT, " + DBStructure.TIME + " TEXT, " + DBStructure.DATE+ " TEXT, " + DBStructure.MONTH+ " TEXT, " + DBStructure.PLACE + " TEXT NOT NULL, " + DBStructure.YEAR+ " TEXT);"; private static final String DROP_EVENTS_TABLE = "DROP TABLE IF EXISTS " + DBStructure.EVENT_TABLE_NAME;


public DbOpenHelper(@Nullable Context context) {
    super(context, DBStructure.DB_NAME, null, DBStructure.DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {

    db.execSQL(CREATE_EVENTS_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    //if(newVersion>oldVersion)
        db.execSQL(DROP_EVENTS_TABLE);
    onCreate(db);


}

public void SaveEvent (String event, String place, String time, String date, String month, String year, SQLiteDatabase database) {
    ContentValues contentValues = new ContentValues();
    contentValues.put(DBStructure.EVENT, event);
    contentValues.put(DBStructure.PLACE, place);
    contentValues.put(DBStructure.TIME, time);
    contentValues.put(DBStructure.DATE, date);
    contentValues.put(DBStructure.MONTH, month);
    contentValues.put(DBStructure.YEAR, year);
    database.insert(DBStructure.EVENT_TABLE_NAME, null, contentValues);

}

public Cursor ReadEvents (String date, SQLiteDatabase database){
    String [] Projections = {DBStructure.EVENT, DBStructure.PLACE, DBStructure.TIME, DBStructure.DATE, DBStructure.MONTH, DBStructure.YEAR};
    String Selection = DBStructure.DATE + "=?";
    String [] SelectionArgs = {date};
    return database.query(DBStructure.EVENT_TABLE_NAME, Projections, Selection, SelectionArgs, null, null, null );
}

public Cursor ReadEventsPerMonth (String month, String year, SQLiteDatabase database){
    String [] Projections = {DBStructure.EVENT, DBStructure.PLACE, DBStructure.TIME, DBStructure.DATE, DBStructure.MONTH, DBStructure.YEAR};
    String Selection = DBStructure.MONTH + "=? and " + DBStructure.YEAR + "=?";
    String [] SelectionArgs = {month, year};
    return database.query(DBStructure.EVENT_TABLE_NAME, Projections, Selection, SelectionArgs, null, null, null );
}
}

public class DBStructure {
    public static final String DB_NAME = "EVENTS_DB";
    public static final int DB_VERSION = 4;
    public static final String EVENT_TABLE_NAME = "eventsTable";
    public static final String EVENT = "event";
    public static final String PLACE = "place";
    public static final String TIME = "time";
    public static final String DATE = "date";
    public static final String MONTH = "month";
    public static final String YEAR = "year";
    public static final String ID = "ID";
}

public class CustomCalendarView extends LinearLayout {
    ImageButton PreviousButton, NextButton;
    TextView CurrentDate;
    GridView gridView;
    private static final int MAX_CALENDAR_DAYS=42;
    Calendar calendar = Calendar.getInstance(Locale.ENGLISH);
    Context context;
    SimpleDateFormat dateFormat = new SimpleDateFormat("MMM yyy", Locale.ENGLISH);
    SimpleDateFormat monthFormat = new SimpleDateFormat("MMM", Locale.ENGLISH);
    SimpleDateFormat yearFormat = new SimpleDateFormat("yyy", Locale.ENGLISH);
    SimpleDateFormat eventDateFormat = new SimpleDateFormat("yyy-MM-dd", Locale.ENGLISH);

    MyGridAdapter myGridAdapter;
    AlertDialog alertDialog;
    List <Date> dates = new ArrayList<>();
    List<Events> eventsList = new ArrayList<>();

    DbOpenHelper dbOpenHelper;


    public CustomCalendarView(Context context) {
        super(context);
    }

    public CustomCalendarView(Context context, @Nullable AttributeSet attrs) {
        super(context, attrs);
        this.context = context;
        InitializeLayout ();
        setUpCalendar();

        PreviousButton.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                calendar.add(Calendar.MONTH, -1);
                setUpCalendar();
            }
        });

        NextButton.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                calendar.add(Calendar.MONTH, 1);
                setUpCalendar();
            }
        });

// seems to set the clicked day
        gridView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                AlertDialog.Builder builder = new AlertDialog.Builder(context);
                builder.setCancelable(true);
                View addView = LayoutInflater.from(parent.getContext()).inflate(R.layout.add_new_event_layout, null);
                EditText EventName = addView.findViewById(R.id.event_name);
                EditText WhenWhere = addView.findViewById(R.id.when_where);
                EditText Place = addView.findViewById(R.id.place);
                TextView EvenTime = addView.findViewById(R.id.event_time);
                ImageButton SetTime = addView.findViewById(R.id.set_event_time);
                Button AddEvent = addView.findViewById(R.id.add_event);

                // this set the time. ie: thi is where the person can choose the time in term of hour...
                SetTime.setOnClickListener(new OnClickListener() {
                    @Override
                    public void onClick(View v) {
                        Calendar calendar = Calendar.getInstance();
                        int hours = calendar.get(Calendar.HOUR_OF_DAY);
                        int minutes = calendar.get(Calendar.MINUTE);
                        TimePickerDialog timePickerDialog =  new TimePickerDialog(addView.getContext(), androidx.appcompat.R.style.Theme_AppCompat_Dialog, new TimePickerDialog.OnTimeSetListener() {
                            @Override
                            public void onTimeSet(TimePicker view, int hourOfDay, int minute) {
                                Calendar c = Calendar.getInstance();
                                c.set(Calendar.HOUR_OF_DAY, hourOfDay);
                                c.set(Calendar.MINUTE, minute);
                                c.setTimeZone(TimeZone.getDefault());
                                SimpleDateFormat hFormat = new SimpleDateFormat("K:mm a", Locale.ENGLISH);
                                String event_Time = hFormat.format(c.getTime());
                                EvenTime.setText(event_Time);
                            }
                        }, hours, minutes, false );

                        timePickerDialog.show();
                    }
                });

                final String date = eventDateFormat.format(dates.get(position));
                final String month = monthFormat.format(dates.get(position));
                final String year = yearFormat.format(dates.get(position));

                // this add an event. Notice here in the onclick the SaveEvent constructor which helps save the event added.
                //that SaveEvent can be find set up down there.
                AddEvent.setOnClickListener(new OnClickListener() {
                    @Override
                    public void onClick(View v) {
                        SaveEvent (EventName.getText().toString(), Place.getText().toString(), EvenTime.getText().toString(), date, month, year );
                        setUpCalendar();
                        alertDialog.dismiss();
                    }
                });

                builder.setView(addView);
                alertDialog = builder.create();
                alertDialog.show();
            }
        });
        // here the event saved gets call and display
        gridView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {

                String date = eventDateFormat.format(dates.get(position));

                AlertDialog.Builder builder = new AlertDialog.Builder(context);
                builder.setCancelable(true);
                View showView = LayoutInflater.from(parent.getContext()).inflate(R.layout.show_events_layout, null);

                RecyclerView recyclerView = showView.findViewById(R.id.eventsRv);
                RecyclerView.LayoutManager layoutManager = new LinearLayoutManager(showView.getContext());
                recyclerView.setLayoutManager(layoutManager);
                recyclerView.setHasFixedSize(true);
                EventRecyclerAdapter eventRecyclerAdapter = new EventRecyclerAdapter(showView.getContext(), CollectEventByDate(date));
                recyclerView.setAdapter(eventRecyclerAdapter);
                eventRecyclerAdapter.notifyDataSetChanged();

                builder.setView(showView);
                alertDialog = builder.create();
                alertDialog.show();


                return true;
            }
        });


    }
    private ArrayList<Events> CollectEventByDate (String date){
        ArrayList<Events> arrayList = new ArrayList<>();
        dbOpenHelper = new DbOpenHelper(context);
        SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
        Cursor cursor = dbOpenHelper.ReadEvents(date, database);

        while (cursor.moveToNext()){
            String event = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.EVENT));
            String place = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.PLACE));
            String time = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.TIME));
            String Date = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.DATE));
            String month = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.MONTH));
            String Year = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.YEAR));
            Events events = new Events(event, place, time, Date, month, Year);
            arrayList.add(events);
        }
        cursor.close();
        dbOpenHelper.close();
        return arrayList;
    }



    public CustomCalendarView(Context context, @Nullable AttributeSet attrs, int defStyleAttr) {
        super(context, attrs, defStyleAttr);

    }

    // this, save the event and display text saying that the event is saved.
    private void SaveEvent ( String event, String place, String time, String date, String month, String year){
        dbOpenHelper = new DbOpenHelper(context);
        SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
        dbOpenHelper.SaveEvent(event, place, time, date, month, year, database);
        dbOpenHelper.close();
        Toast.makeText(context, "Events Saved", Toast.LENGTH_SHORT).show();

    }

    private void InitializeLayout (){
        LayoutInflater inflater = (LayoutInflater)context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
        View view = inflater.inflate(R.layout.calendar_layout, this);
        NextButton = view.findViewById(R.id.nextBtn);
        PreviousButton = view.findViewById(R.id.previousBtn);
        CurrentDate = view.findViewById(R.id.current_date);
        gridView = view.findViewById(R.id.gridView);
    }

    private void setUpCalendar(){
        String currentDate = dateFormat.format(calendar.getTime());
        CurrentDate.setText(currentDate);
        dates.clear();
        Calendar monthCalendar = (Calendar) calendar.clone();
        monthCalendar.set(Calendar.DAY_OF_MONTH,1);
        int FirstDaysOfMonth = monthCalendar.get(Calendar.DAY_OF_WEEK)-1;
        monthCalendar.add(Calendar.DAY_OF_MONTH, -FirstDaysOfMonth);
        CollectEventsPerMonth(monthFormat.format(calendar.getTime()), yearFormat.format(calendar.getTime()));

        while (dates.size() < MAX_CALENDAR_DAYS){
            dates.add(monthCalendar.getTime());
            monthCalendar.add(Calendar.DAY_OF_MONTH, 1);

        }

        myGridAdapter = new MyGridAdapter(context, dates, calendar, eventsList);
        gridView.setAdapter(myGridAdapter);
    }


        //Here we gave the constructor that reads from our database
        //after setting it here, the CollectEventsPerMonth is put in setUpCalendar

    private void CollectEventsPerMonth (String Month, String year){
        eventsList.clear();
        dbOpenHelper = new DbOpenHelper(context);
        Cursor cursor;
        SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
         cursor = dbOpenHelper.ReadEventsPerMonth(Month, year, database);

        while (cursor.moveToNext()){
            String event = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.EVENT));
            String place = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.PLACE));
            String time = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.TIME));
            String date = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.DATE));
            String month = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.MONTH));
            String Year = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.YEAR));
            Events events = new Events(event, place, time, date, month, Year);
            eventsList.add(events);
        }
        cursor.close();
        dbOpenHelper.close();
    }
}

and this is the error I am getting:

2023-01-10 15:29:27.408 4603-4603/? E/AndroidRuntime: Caused by: android.database.sqlite.SQLiteException: no such column: place (code 1 SQLITE_ERROR): , while compiling: SELECT event, place, time, date, month, year FROM eventsTable WHERE month=? and year=? at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1045) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:652) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:61) at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1545) at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1392) at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1263) at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1431) at com.example.android.calendarpoubelle.DbOpenHelper.ReadEventsPerMonth(DbOpenHelper.java:60) at com.example.android.calendarpoubelle.CustomCalendarView.CollectEventsPerMonth(CustomCalendarView.java:243) at com.example.android.calendarpoubelle.CustomCalendarView.setUpCalendar(CustomCalendarView.java:222) at com.example.android.calendarpoubelle.CustomCalendarView.(CustomCalendarView.java:61) ... 28 more 2023-01-10 15:29:30.676 14877-15754/system_process E/TaskPersister: File error accessing recents directory (directory doesn't exist?). 2023-01-10 15:29:40.483 476-476/? E/netmgr: qemu_pipe_open_ns:62: Could not connect to the 'pipe:qemud:network' service: Invalid argument 2023-01-10 15:29:40.483 476-476/? E/netmgr: Failed to open QEMU pipe 'qemud:network': Invalid argument 2023-01-10 15:29:42.603 3276-4261/com.google.android.gms E/WakeLock: GCM_HB_ALARM release without a matched acquire! 2023-01-10 15:29:57.838 214-613/? E/android.system.suspend@1.0-service: Error opening kernel wakelock stats for: wakeup34: Permission denied 2023-01-10 15:29:57.844 214-613/? E/android.system.suspend@1.0-service: Error opening kernel wakelock stats for: wakeup35: Permission denied 2023-01-10 15:30:16.639 483-483/? E/wifi_forwarder: qemu_pipe_open_ns:62: Could not connect to the 'pipe:qemud:wififorward' service: Invalid argument 2023-01-10 15:30:16.639 483-483/? E/wifi_forwarder: RemoteConnection failed to initialize: RemoteConnection failed to open pipe 2023-01-10 15:30:40.517 476-476/? E/netmgr: qemu_pipe_open_ns:62: Could not connect to the 'pipe:qemud:network' service: Invalid argument 2023-01-10 15:30:40.517 476-476/? E/netmgr: Failed to open QEMU pipe 'qemud:network': Invalid argument.....

Answer 1

You have omitted a space between ID and INTEGER the type thus the column name becomes IDINTEGER, which in turn results in the table not being created as AUTOINCREMENT must be for a column that has INTEGER PRIMARY KEY.

Correcting that error, for a fresh install then the ReadEventsPerMonth method works fine.

As such the issue, as you are not seeing the error:-

Caused by: android.database.sqlite.SQLiteException: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY (code 1 SQLITE_ERROR): , while compiling: create table eventsTable( IDINTEGER PRIMARY KEY AUTOINCREMENT, event TEXT, time TEXT, date TEXT, month TEXT, place TEXT NOT NULL, year TEXT);

is virtually certainly because the table exists in it's old form and that the place column does not exist.

The reason is very likely that you have added the place column (and perhaps others) and amended the CREATE_EVENTS_TABLE value accordingly. However, this on it's own will not introduce the changes as the onCreate method only runs once in the lifetime of the database (an it appears to have been created already).

How to Fix

1. Use (to add the required space between ID and INTEGER)

:-

private static final String CREATE_EVENTS_TABLE = "create table " + DBStructure.EVENT_TABLE_NAME + "( " + DBStructure.ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + DBStructure.EVENT+ " TEXT, " + DBStructure.TIME + " TEXT, " + DBStructure.DATE+ " TEXT, " + DBStructure.MONTH+ " TEXT, " + DBStructure.PLACE + " TEXT NOT NULL, " + DBStructure.YEAR+ " TEXT);"; private static final String DROP_EVENTS_TABLE = "DROP TABLE IF EXISTS " + DBStructure.EVENT_TABLE_NAME;

2. If there is no critical data in the database then you can simply uninstall the database and rerun the App. The data in the database will be wiped.

3. (instead of 2). If you do have critical data then you can use the onUpdate method to keep the existing data but adding the new column(s) (perhaps with a default value (not needed as they would be null as you do not have the NOT NULL constraint)). The following is an example (see the comments)

:-

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if(newVersion>oldVersion && oldVersion == 4) {
        /* The columns in the original table before the added columns */
        String existing_columns = DBStructure.ID + "," + DBStructure.EVENT + "," + DBStructure.TIME + "," + DBStructure.DATE + "," + DBStructure.MONTH + "," + DBStructure.YEAR;
        /* a new name for the original table so that the new table can be named with the name to be used */
        String original = "original_" + DBStructure.EVENT_TABLE_NAME;
        db.execSQL("ALTER TABLE " + DBStructure.EVENT_TABLE_NAME + " RENAME TO " + original); /* rename the original table */
        onCreate(db); /* call the onCreate method to create the new table */
        db.execSQL("INSERT OR IGNORE INTO " + DBStructure.EVENT_TABLE_NAME +
                /* The columns to be inserted i.e. original columns (existing_columns) and the new column(s) being added */
                "(" + existing_columns + "," + DBStructure.PLACE + ") " +
                /* Using an INSERT SELECT to get the existing data */
                "SELECT " +
                /* The existing columns */
                existing_columns +
                /* and literal value(s) for the added column(s) (just the one in this example ) */
                /*comma required before each new column and a suitable value for the new column */
                 ",'new place' FROM " + original);
        db.execSQL("DROP TABLE IF EXISTS " + original); /* should have no need for the original (renamed) table so drop it */
    }
}

• NOTE in conjunction with using public static final int DB_VERSION = 5; (i.e. from version 4 to version 5)
• NOTE The above, as it is, is ONLY for adding the place column, it would have to be amended to suit another column or other columns.