android and sqlite-has 5 columns but 4 values were supplied

Question

I copied an example of sqlite use from somewhere. They defined 5 columns and I change it to 4 columns. The relevant part is here:

    private final String DB_NAME = "mcustomerDbName";
    private final String TABLE_NAME = "mcustomeTableName";
    SQLiteDatabase customersDB = null;
    /** Called when the activity is first created. */


     ..........................................

This is the context of the table-4 columns

    private void mFillDbsTable(){
        try {

            customersDB.execSQL("INSERT INTO " +
                    TABLE_NAME + 
                    " Values ('ABARTH','500','Abarth','15.3846153846154' );");
            customersDB.execSQL("INSERT INTO " + TABLE_NAME + "Values ( 'ABARTH ', 'Grande Punto ', '1.4 T-Jet 155 ', '14.9253731343284 ' );");
            customersDB.execSQL("INSERT INTO " + TABLE_NAME + "Values ( 'ALFA ROMEO ', 'MiTo ', '1.4 16v TB 120  ', '16.3934426229508 ');");
            customersDB.execSQL("INSERT INTO " + TABLE_NAME + "Values ( 'ALFA ROMEO ', 'MiTo ', '1.3 JTDm 90 ', '22.2222222222222 ');");
            customersDB.execSQL("INSERT INTO " + TABLE_NAME + "Values ( 'ALFA ROMEO ', '147 ', '1.6 (120 bhp) ', '12.1951219512195 ');");
            customersDB.execSQL("INSERT INTO " + TABLE_NAME + "Values ( 'ALFA ROMEO ', '147 ', '2.0 ', '11.2359550561798 ');");

     ..........................................

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    /*setContentView(R.layout.activity_delek);**/
     ArrayList<String> results = new ArrayList<String>();
        try {
            customersDB =  this.openOrCreateDatabase(  DB_NAME, MODE_PRIVATE, null);
            customersDB.execSQL("CREATE TABLE IF NOT EXISTS " +
                    TABLE_NAME +
                    " (Name VARCHAR," +
                    " Street VARCHAR, Block VARCHAR, City VARCHAR);");
            mFillDbsTable();
             Cursor c = customersDB.rawQuery("SELECT Name, Street, Block, City  FROM "    +
                    TABLE_NAME +
                       " where Name == 'TOYOTA' or Name == 'Yosi' ", null);
            if (c != null ) {
                if  (c.moveToFirst()) {
                    do {
                        String name = c.getString(c.getColumnIndex("Name"));
                           String street = c.getString(c.getColumnIndex("Street"));

                              String city = c.getString(c.getColumnIndex("City"));

                            results.add(name + ", " + street + " "+ city );
                    }while (c.moveToNext());
                }
            }
            setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,results));
         } catch (SQLiteException se ) {
            Log.e(getClass().getSimpleName(), "create/Open the database problem");
        } finally {
            if (customersDB != null)
                customersDB.execSQL("DELETE FROM " +   TABLE_NAME);
                customersDB.close();
        }

I get this message in the log:

 10-01 21:59:59.434: I/Database(1067): sqlite returned: error code = 1, msg = table            mcustomeTableName has 5 columns but 4 values were supplied.

Why??I have 4 columns

Answer 1

The table probably already existed from a previous run without your changes and still has 5 columns in the database (CREATE TABLE IF NOT EXISTS). You can verify this by logging on to sqllite from ADP. Just drop the table before you run your app.

In addition, provide a column list with your insert statements. This is good practice regardless, because your applicaton will be more robust towards schema changes:

"INSERT INTO " + TABLE_NAME + " (Name, Street, Block, City) Values (" ...etc