Android Stuido sqlite database no such table error

I'm trying to use sq lite database in android studio, but I get no such table error

I created an sqlite database and I was waiting for the database I created to assign data to the recycler view and for the tasks I created to appear in the recycler view. but I got no such table error and nothing showed up in the recycler view

private void saveWorkRecord() {
    
    String workName = editTextTaskName.getText().toString();
    int workingHours = 0; 
    int logoId = selectedLogoResourceId; 
    Date date = new Date(); 
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy", Locale.getDefault());
    String formattedDate = dateFormat.format(date); 

    try {
        database.execSQL("CREATE TABLE IF NOT EXISTS workifydatabase(id INTEGER PRIMARY KEY,workname VARCHAR,logoid INTEGER,year VARCHAR,workinghours INTEGER) ");
        String sqlString = "INSERT INTO workifydatabase(workname, logoid, year, workinghours) VALUES(?,?,?,?)";
        SQLiteStatement sqLiteStatement = database.compileStatement(sqlString);
        sqLiteStatement.bindString(1, workName);
        sqLiteStatement.bindLong(2, logoId); 
        sqLiteStatement.bindString(3, formattedDate);
        sqLiteStatement.bindLong(4, workingHours); 
        sqLiteStatement.executeInsert();
    } catch (Exception e) {
        e.printStackTrace();
    }

    database.close();
    finish();
}

and this is the usage part:

private void getData() {

    try {
        SQLiteDatabase sqLiteDatabase = requireContext().openOrCreateDatabase("workifyDatabase",MODE_PRIVATE,null);
        Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM workifydatabase",null);
        int nameIx = cursor.getColumnIndex("workname");
        int idIx = cursor.getColumnIndex("id");
        int hourIx = cursor.getColumnIndex("workinghours");
        int yearIx = cursor.getColumnIndex("year");

        while (cursor.moveToNext()){
            String name = cursor.getString(nameIx);
            int id= cursor.getInt(idIx);
            String year = cursor.getString(yearIx);
            int hour = cursor.getInt(hourIx);
            Task task = new Task(name,id,hour,year);
            TaskArrayList.add(task);
        }

        taskAdapter.notifyDataSetChanged();
        cursor.close();
    } catch(Exception e) {
        e.printStackTrace();
    }
}

Solution

I believe that your issue is that the getData method is dependant upon the saveWorkRecord method (as this appears to create the database and then save to a non existent table) and that the saveWorkRecord method is dependant upon the getData method (as this appears to require that the database exists).

I would suggest that you consider the following demo that utilises the SQLiteOpenHelper class that helps you to open the database and passes control, when the database is first opened, to the onCreate method.

The demo also uses a singleton for the database.

Working Demo

First the DBOpenHelper class that is an extension of the SQLiteOpenHelper class:-

class DBOpenHelper extends SQLiteOpenHelper {

   public static final String DATABASE_NAME = "workifyDatabase";
   public static final int DATABASE_VERSION = 1;

   /* Constructor  for the DBOpenHelper class, private to force use of singleton */
   private DBOpenHelper(Context context) {
      super(context,DATABASE_NAME,null,DATABASE_VERSION);
   }

   /* Singleton approach (i.e. only 1 instance of DBOpenHelper exists)*/
   private static volatile DBOpenHelper instance;
   public static DBOpenHelper getInstance(Context context) {
      if (instance==null) {
         instance = new DBOpenHelper(context);
      }
      return instance;
   }

   /* If the database does not exist, then this method will be called */
   /* ideal place to create the table(s) */
   @Override
   public void onCreate(SQLiteDatabase db) {
      db.execSQL("CREATE TABLE IF NOT EXISTS workifydatabase(id INTEGER PRIMARY KEY,workname VARCHAR,logoid INTEGER,year VARCHAR,workinghours INTEGER) ");
   }

   /* If the database version is increased, then this method will be invoked */
   @Override
   public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
   }

   /* Method to insert a row, using the convenience insert method */
   /* this generates the underlying SQL and furthermore returns the id of the inserted row or -1 if the row was not inserted */
   public long saveWorkRecord(String workname,int logoid, String year,int workinghours) {
      ContentValues cv = new ContentValues();
      cv.put("workname",workname);
      cv.put("logoid",logoid);
      cv.put("year",year);
      cv.put("workinghours",workinghours);
      return this.getWritableDatabase().insert("workifydatabase",null,cv);
   }

   /* Example of retrieving data (might want to adapt this to return a TaskArrayList rather than a Cursor) */
   public Cursor getAllDataAsCursor() {
      return this.getWritableDatabase().query("workifydatabase",null /* all columns */,null /* all rows*/,null /* no selection args */, null,null,null);
   }
}

Note the two methods for accessing the database, saveWorkRecord for saving a row of data (returning the id of the row or -1 if the row could not be inserted and getAllData to return a Cursor (note the comment adapting to return a TaskArrayList). i.e. the demo is more about circumventing the table not found issue than handling the display of the list

To actually demonstrate some activity code that will save two records and then extract them from the database into a Cursor and then using the DatabaseUtils dumpCursor method to write the data in the cursor to the log:-

public class MainActivity extends AppCompatActivity {

    DBOpenHelper db;
    /*NOTE BAD IDEA TO STORE DATES in dd-mm-yyyy format
    * 1) date is only sortable indirectly
    * 2) the date, not being an SQLite recognised format, excludes simple use of SQLite's build in date functions
    *  */
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy", Locale.getDefault());
    SimpleDateFormat beterDateFormat = new SimpleDateFormat("yyyy-MM-dd",Locale.getDefault());

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = DBOpenHelper.getInstance(this); /* get an instance of the DBOpenHelper*/
        /* Note that the database will not have been opened/created at this stage */

        /* Try to get data from the database (noting no data has been added) */
        /* This will result in the database being created (opened if it exists) and therefore
            the onCreate method of the DBOpenHelper instance being called, thus creating the table */
        Cursor csr = db.getAllDataAsCursor();
        DatabaseUtils.dumpCursor(csr); /* Dumpy the cursor (will be empty first time the app is run) */

        /* Add two records */
        db.saveWorkRecord("My First Work Record",1,dateFormat.format(new Date()),0);
        db.saveWorkRecord("My Other First Work Record",2,beterDateFormat.format(new Date()),10);

        /* Extract the data and dump the cursor to the log */
        csr = db.getAllDataAsCursor();
        DatabaseUtils.dumpCursor(csr);
    }
}

When run for the first time then the log includes:-

2023-08-17 08:19:59.993 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@98fd0f5
2023-08-17 08:19:59.993 I/System.out: <<<<<



2023-08-17 08:19:59.995 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@664ce8a
2023-08-17 08:19:59.996 I/System.out: 0 {
2023-08-17 08:19:59.996 I/System.out:    id=1
2023-08-17 08:19:59.996 I/System.out:    workname=My First Work Record
2023-08-17 08:19:59.996 I/System.out:    logoid=1
2023-08-17 08:19:59.996 I/System.out:    year=17-08-2023
2023-08-17 08:19:59.996 I/System.out:    workinghours=0
2023-08-17 08:19:59.996 I/System.out: }
2023-08-17 08:19:59.996 I/System.out: 1 {
2023-08-17 08:19:59.996 I/System.out:    id=2
2023-08-17 08:19:59.996 I/System.out:    workname=My Other First Work Record
2023-08-17 08:19:59.996 I/System.out:    logoid=2
2023-08-17 08:19:59.996 I/System.out:    year=2023-08-17
2023-08-17 08:19:59.997 I/System.out:    workinghours=10
2023-08-17 08:19:59.997 I/System.out: }
2023-08-17 08:19:59.997 I/System.out: <<<<<

the first dump (first few lines) shows that there is no data in the cursor.
the second dump confirms that the data has been inserted and successfully extracted.

Using Android Studio's App Inspection :-