android.database.sqlite.SQLiteException: table XXX has no column named XXX (code 1)

Question

I know the question has been asked many times, but I just can not find my mistake. I think I did everything right when creating the table.

The error Message is:

android.database.sqlite.SQLiteException: table GPSLocation has no column named Number (code 1):

I checked blanks and punctuation several times. Please help me to find the mistake.

Here is my Database Class:

private GPSDatabase(Context context) {
    super(context, DB_NAME, null, VERSION);
}


public static GPSDatabase getInstance (final Context context){
    if (INSTANCE == null){
        INSTANCE = new GPSDatabase(context);
    }
    return INSTANCE;
}

Here I create the table

@Override
public void onCreate(final SQLiteDatabase db) {

    String createQuery =
            "CREATE TABLE " + TABLE_NAME + " (" + ID_COLUMN + " INTEGER PRIMARY KEY, " + NUMBER_COLUMN + " INTEGER DEFAULT NULL, " + DISTANCE_COLUMN + " INTEGER DEFAULT NULL, " + LATITUDE_COLUMN + " REAL DEFAULT NULL, " + LONGITUDE_COLUMN + " REAL DEFAULT NULL)";

    db.execSQL(createQuery);
    Log.d(TAG, "Table created");
}

The onUpgrade() Method destroys the existing table

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String dropTable = "DROP TABLE IF EXISTS " + TABLE_NAME;
    db.execSQL(dropTable);

    Log.d(TAG,"Table deleted");
    onCreate(db);
}

Create a new database entry

public GPSLocation createGPSLocation (final GPSLocation loc){

    long newID = -1;

    try{
        Log.d(TAG,"createGPSLocation begin");
        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put(NUMBER_COLUMN, loc.getNumber());
        values.put(DISTANCE_COLUMN, loc.getDistance());
        values.put(LATITUDE_COLUMN, loc.getLatitude());
        values.put(LONGITUDE_COLUMN, loc.getLongitude());

        newID = db.insert(TABLE_NAME, null, values);

        db.close();
    }catch (SQLiteException e){
        Log.e(TAG,"insert");
    }finally {
        Log.d(TAG,"insert with ID " + newID);
    }


    Log.d(TAG, "createGPSLocation successful");
    return readGPSLocation(newID);
}

public GPSLocation readGPSLocation(final long id) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(TABLE_NAME, new String[]{ID_COLUMN, NUMBER_COLUMN, DISTANCE_COLUMN,
            LATITUDE_COLUMN, LONGITUDE_COLUMN}, ID_COLUMN + " = ?", new String[]{String.valueOf(id)}, null, null, null);

    GPSLocation loc = null;

    if (cursor != null && cursor.getCount() > 0) {
        cursor.moveToFirst();

        loc = new GPSLocation(cursor.getInt(cursor.getColumnIndex(NUMBER_COLUMN)));

        loc.setId(cursor.getLong(cursor.getColumnIndex(ID_COLUMN)));
        loc.setDistance(cursor.getInt(cursor.getColumnIndex(DISTANCE_COLUMN)));
        loc.setLatitude(cursor.getDouble(cursor.getColumnIndex(LATITUDE_COLUMN)));
        loc.setLongitude(cursor.getDouble(cursor.getColumnIndex(LATITUDE_COLUMN)));

    }

    db.close();

    Log.d(TAG, "read");
    return loc;
}
}

Sorry, what line is this crashing on? `db.insert(TABLE_NAME, null, values);`? And as @FirozMemon says, if you've changed the Schema have you updated the database version? — Michael Dodd, Nov 24 '17 at 12:34

IntelliJ Amiya · Answer 1 · 2017-11-24T13:02:10.873

1

android.database.sqlite.SQLiteException: table GPSLocation has no column named Number (code

You should check column named Number is added or not.

If you add column name Number newly then override onUpgrade method. Increase DB version and use ALTER TABLE_NAME ADD statement.

 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
                   // ALTER TABLE concept respect to VERSION.
            if (newVersion > oldVersion) {
            db.execSQL("ALTER TABLE _TABLE_NAME ADD COLUMN new_column ");
            }
            onCreate(db);
        }
    }

Read onUpgrade Example

edited Nov 24 '17 at 13:02

answered Nov 24 '17 at 12:35

IntelliJ Amiya

74,896
15
165
198

Other than formatting, what's the difference between that `CREATE TABLE` statement and OP's? – Michael Dodd Nov 24 '17 at 12:38
2

Not enough. You'll also have to increase the value of the DATABASE_VERSION constant. – Phantômaxx Nov 24 '17 at 12:40
But OP's `CREATE TABLE` statement seems to have correct spacing in the right places already. Usually this problem happens by leaving out the space before the data type, but OP's got it right. AFAIK extra spaces (i.e. between table name and the opening bracket) are ignored. – Michael Dodd Nov 24 '17 at 12:42
@MichaelDodd for safe approach, I add `onUpgrade` – IntelliJ Amiya Nov 24 '17 at 12:44
1

That still doesn't explain the `Rectify CREATE TABLE` part of your answer when there's nothing wrong with the `CREATE TABLE` statement. – Michael Dodd Nov 24 '17 at 12:45

Pankaj Mundra · Accepted Answer · 2017-11-25T05:11:32.193

Below it the updated code:

public class GPSDatabase extends SQLiteOpenHelper {
    public static final String DB_NAME = "GPSDatabase";
        public static final int VERSION = 1;
        public static final String TABLE_NAME = "GPSLocation";
        public static final String ID_COLUMN = "id";
        public static final String NUMBER_COLUMN = "number";
        public static final String DISTANCE_COLUMN = "distance";
        public static final String LATITUDE_COLUMN = "latitude";
        public static final String LONGITUDE_COLUMN = "longitude";
        public static final String TAG = "GPSDatabase";


        public GPSDatabase(Context context) {
            super(context, DB_NAME, null, VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            String createQuery =
                    "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + ID_COLUMN + " INTEGER PRIMARY KEY, " + NUMBER_COLUMN + " INTEGER DEFAULT NULL, " + DISTANCE_COLUMN + " INTEGER DEFAULT NULL, " + LATITUDE_COLUMN + " REAL DEFAULT NULL, " + LONGITUDE_COLUMN + " REAL DEFAULT NULL)";

            db.execSQL(createQuery);
            Log.d(TAG, "Table created");

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int i, int i1) {

            String dropTable = "DROP TABLE IF EXISTS " + TABLE_NAME;
            db.execSQL(dropTable);

            Log.d(TAG, "Table deleted");
            onCreate(db);

        }

        public GPSLocation createGpsLocation(GPSLocation loc) {

            SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
            long newID = -1;

            try {
                Log.d(TAG, "createGPSLocation begin");

                ContentValues values = new ContentValues();

                values.put(NUMBER_COLUMN, loc.getNumber());
                values.put(DISTANCE_COLUMN, loc.getDistance());
                values.put(LATITUDE_COLUMN, loc.getLatitude());
                values.put(LONGITUDE_COLUMN, loc.getLongitude());

                newID = sqLiteDatabase.insert(TABLE_NAME, null, values);

                sqLiteDatabase.close();
            } catch (SQLiteException e) {
                Log.e(TAG, "insert");
            } finally {
                Log.d(TAG, "insert with ID " + newID);
            }
            Log.d(TAG, "createGPSLocation successful");
            return loc;
        }

        public GPSLocation readGPSLocation(final long id) {
            SQLiteDatabase db = this.getReadableDatabase();
            GPSLocation loc = null;
            String query = "SELECT * FROM " + TABLE_NAME + " where id= " + ID_COLUMN;
            Cursor cursor = db.rawQuery(query, null);
            if (cursor != null && cursor.getCount() > 0) {
                cursor.moveToFirst();
                do {
                    loc = new GPSLocation();
                    loc.setNumber(cursor.getInt(cursor.getColumnIndex(NUMBER_COLUMN)));
                    loc.setDistance(cursor.getInt(cursor.getColumnIndex(DISTANCE_COLUMN)));
                    loc.setLatitude(cursor.getDouble(cursor.getColumnIndex(LATITUDE_COLUMN)));
                    loc.setLongitude(cursor.getDouble(cursor.getColumnIndex(LATITUDE_COLUMN)));
                } while (cursor.moveToNext());
                db.close();

                Log.d(TAG, "read");
            }
            return loc;
        }
    }

Your bean class:

public class GPSLocation {

    int number, distance;
    double latitude, longitude;

    public GPSLocation(int number, int distance, double latitude, double longitude) {
        this.number = number;
        this.distance = distance;
        this.latitude = latitude;
        this.longitude = longitude;
    }

    public GPSLocation() {
    }

    public int getNumber() {
        return number;
    }

    public void setNumber(int number) {
        this.number = number;
    }

    public int getDistance() {
        return distance;
    }

    public void setDistance(int distance) {
        this.distance = distance;
    }

    public double getLatitude() {
        return latitude;
    }

    public void setLatitude(double latitude) {
        this.latitude = latitude;
    }

    public double getLongitude() {
        return longitude;
    }

    public void setLongitude(double longitude) {
        this.longitude = longitude;
    }
}

Activity:

public class GPSData extends AppCompatActivity {

    GPSDatabase gpsDatabase;
    GPSLocation gpsLocation;
    Button saveDataD, fetchDataD;

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

        saveDataD = findViewById(R.id.saveDataD);
        fetchDataD = findViewById(R.id.fetchDataD);

        gpsDatabase = new GPSDatabase(this);

        saveDataD.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                insertDataInDB();

            }
        });

        fetchDataD.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                fetchDataFromDB(1);
            }
        });

    }

    private void fetchDataFromDB(long i) {

        try {
            gpsLocation = gpsDatabase.readGPSLocation(i);
            Log.e("gpsLocation", "" + gpsLocation.getNumber());
            Log.e("gpsLocation", "" + gpsLocation.getDistance());
            Log.e("gpsLocation", "" + gpsLocation.getLatitude());
            Log.e("gpsLocation", "" + gpsLocation.getLongitude());

        } catch (Exception e) {
            Log.e("insertDataInDB", e.getMessage());
        }

    }

    private void insertDataInDB() {
        try {
            gpsLocation = new GPSLocation(1, 10, 10.5, 10.5);
            gpsDatabase.createGpsLocation(gpsLocation);
        } catch (Exception e) {
            Log.e("insertDataInDB", e.getMessage());
        }


    }

}

Main.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.taxsmart.greendoaex.GPSData">

    <Button
        android:id="@+id/saveDataD"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="saveDataD" />

    <Button
        android:id="@+id/fetchDataD"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="fetchDataD" />

</LinearLayout>

Database:

Output from Reading Database:

@RobinRode you can check in device explorer is your database created or not, if created right click on it and choose to save, save in a location. now open SQLite manager in Firefox and open your database by looking its location and you will see your database with all tables and data. — Pankaj Mundra, Nov 27 '17 at 09:57

android.database.sqlite.SQLiteException: table XXX has no column named XXX (code 1)

2 Answers2