SQLite problem of no such column when trying to insert

Question

I use Sqlite and I have the following DB , I am developing an application for women which it should contain a login and sign up . I had problems in the database like "no such column"

DataBase

public class DB_MJG extends SQLiteOpenHelper {
public static final String name ="dataB.db";
public static final int version =1;

//Les atts de la table FEMME
public static final String table_Femme ="Femme";
public static final String ID_F =  "id";
public static final String NOM_F ="nom";
public static final String PRENOM_F="prenom";
public static final String PSEUDO="pseudo";
public static final String MDP="mdp";
public static final String GRP_F="grpSang";
public static final String AGE_F="age";
public static final String POIDS="poids";

public DB_MJG( Context context) {
    super(context, name, null, version);
}

@Override
public void onCreate(SQLiteDatabase db) {

    String CREATE_TABLE_FEMME = "CREATE TABLE " + table_Femme + "( 
"+ID_F+" INTEGER PRIMARY KEY AUTOINCREMENT, "
                                +NOM_F+" TEXT, "+PRENOM_F+" TEXT " + ")";


    db.execSQL(CREATE_TABLE_FEMME);
    System.out.println("table femme crée");

}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    db.execSQL("DROP TABLE IF EXISTS "+table_Femme);
    db.execSQL("DROP TABLE IF EXISTS "+table_Enfant);

}

//insérer dans la table FEMME
public void insertFemme(Femme f)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues vals = new ContentValues();


    vals.put(NOM_F,f.getNom());
    vals.put(PRENOM_F,f.getPrenom());
     db.insert(table_Femme,null,vals);
     db.close();
}


public ArrayList getFemme()
{
    ArrayList<Femme> femmes = new ArrayList<>();
    SQLiteDatabase db = this.getWritableDatabase();
     Cursor c = db.rawQuery("SELECT * FROM " +table_Femme, null);
    while(c.moveToNext()){
        Femme f = new Femme(c.getString(1),c.getString(2));
        femmes.add(f);
    }
    return femmes;
}

}

Launcher Activity

 public class MainActivity extends AppCompatActivity {
 DB_MJG db = new DB_MJG(this);
 SQLiteDatabase database ;
 String s = "";
 private Button log,sign;
 @Override
 protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    database = openOrCreateDatabase(db.name, Context.MODE_PRIVATE, null) ;

    db.insertFemme(new Femme("sara","sara"));
    ArrayList<Femme> femmes = db.getFemme();

    TextView textView= (TextView) findViewById(R.id.textView13);
    for(Femme f : femmes){
        String ch = "Nom :" +f.getNom() + " Prenom : " 
+f.getPrenom()+"\n";
        s = s +ch;
    }
    textView.setText(s);
}

Error

E/SQLiteLog: (1) table Femme has no column named nom
E/SQLiteDatabase: Error inserting nom=sara prenom=sara
android.database.sqlite.SQLiteException: table Femme has no column named             
 nom (code 1 SQLITE_ERROR): , while compiling: INSERT INTO     
Femme(nom,prenom) VALUES (?,?)
    at 
 android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native 
 Method)

When I compile,it says no such column. I have defined the nom column. The database has the nom that I am trying to use for inserting but it doesn't work.

Answer 1

The primary issue you have is that the database helper's onCreate method is only called when the database is created, which is once in it's lifetime.

As such if any changes are made to the structure (schema) by amending the create table SQL, as used in the onCreate method, they will not be reflected unless the database is deleted or that the onCreate method is invoked some other way.

Often such changes, as least when developing the app, are applied by first doing one of the following :-

1. Deleting the App's data (this deletes the database, so onCreate is automatically called).
2. Uninstalling the App (and as a result deletes the App's data).
3. IF the onUpgrade method is written to drop the changed table(s) and recreate the tables (often/generally by calling the onCreate method)

After doing one of the above, rerunning the App will then result in the structure change being applied.

• Note the above will result in the loss of existing data and IS NOT SUITABLE for an App that has been deployed.

In your case if using option 3, the onUpgrade method needs to include the creation of the table(s) after they have been dropped as it only drops the tables.

You code in the MainActivity is also overly complex in that it utilises opening the database without using the SQLiteOpenHelper to open the database but rather opens it using the SQLiteDatabase openOrCreate method (which doesn't result in the SQLiteOpenHelper's (DB_MJG is a subclass of SQLiteOpenHelper) onCreate method being called). However, by a quirk/luck, when you do start to use the instance of DB_MJG, namely db, as the instance was created before openOrCreate method, it actually goes on to call the onCreat method.

However, it would be much simpler, to just use one method to open the database.

As such I'd suggest implementing using just the DB_MJG DatabseHelper.

Fix

The essential fix, is to introduce the changed structure. So one of the 3, above should be taken.

if using 3. then amending the onUpgrade method to call the onCreate method and then increasing the version number would be required. That is the onUpgrade method could be :-

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    db.execSQL("DROP TABLE IF EXISTS "+table_Femme);
    db.execSQL("DROP TABLE IF EXISTS "+table_Enfant);
    onCreate(db); //<<<<<<<<<< ADDED so that onUpgrade creates the tables after they have been dropped
}

Additional/Recommended

Close Cursor

in DB_MJG.java the getFemme method leaves the Cursor open. This can result in a too many open databases or cursors excpetion so it is suggested that the line :-

c.close();

is added to the getFemme method, so it becomes :-

public ArrayList getFemme()
{
    ArrayList<Femme> femmes = new ArrayList<>();
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = db.rawQuery("SELECT * FROM " +table_Femme, null);
    while(c.moveToNext()){
        femmes.add(new Femme(c.getString(c.getColumnIndex(NOM_F)),c.getString(c.getColumnIndex(PRENOM_F))));
    }
    c.close(); //<<<<<<<<<< should always close a cursor when finished with it
    return femmes;
}

• Note the above also does away with the need for the intermediate Femme object f.

Use DB_MJG to open the database

There is no need to openOrCreate the database when using a subclass of SQliteOpenHelper as it will do this. So MainActivity.java could be :-

public class MainActivity extends AppCompatActivity {

    DB_MJG db; // Only declare the DB_MJG object.
    private Button log,sign;

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

        db = new DB_MJG(this);
        db.insertFemme(new Femme("sara","sara"));
        ArrayList<Femme> femmes = db.getFemme();
        TextView textView= (TextView) findViewById(R.id.textView13);
        StringBuilder sb = new StringBuilder(); //<<<<<<<<<< ADDED - Use StringBuilder in a loop
        for(Femme f : femmes){
            sb.append("Nom: ").append(f.getNom()).append(" Prenom: ").append(f.getPrenom());
        }
        textView.setText(sb.toString());
    }
}

• Note that the above should be changed at the same time or after the fix has been applied.

• Instead of concatenating Strings in a loop a StringBuilder has been used. See -Why to use StringBuffer in Java instead of the string concatenation operator