Getting SQLiteException: no such column

Question

public class MyDBHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "winkelskortrijk.db";
    private static final String TABLE_USERS = "users";
    private static final String TABLE_FAVOURITES = "favorieten";

    public static final String COLUMN_ID = "id";
    public static final String COLUMN_USERNAME = "username";
    public static final String COLUMN_PASSWORD = "password";

    public static final String COLUMN_ID2 = "id2";
    public static final String COLUMN_NAME = "naam";
    public static final String COLUMN_STRAAT = "straat";
    public static final String COLUMN_HUISNR = "nr";
    public static final String COLUMN_POSTCODE = "postcode";
    public static final String COLUMN_DEELGEMEENTE = "deelgemeente";
    public static final String COLUMN_GEMEENTE = "gemeente";
    public static final String COLUMN_ADRES = "adres";

    public MyDBHandler(Context context, String name,
                       SQLiteDatabase.CursorFactory factory, int version) {
        super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        String CREATE_USERS_TABLE = "CREATE TABLE " +
                TABLE_USERS + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_USERNAME + " TEXT,"
                + COLUMN_PASSWORD + " TEXT"
                + ")";
        db.execSQL(CREATE_USERS_TABLE);

        String CREATE_FAVOURITES_TABLE = "CREATE TABLE " +
                TABLE_FAVOURITES + "("
                + COLUMN_ID2 + " INTEGER PRIMARY KEY,"
                + COLUMN_NAME + " TEXT,"
                + COLUMN_STRAAT + " TEXT,"
                + COLUMN_HUISNR + " INTEGER,"
                + COLUMN_POSTCODE + " INTEGER,"
                + COLUMN_DEELGEMEENTE + " TEXT,"
                + COLUMN_GEMEENTE + " TEXT,"
                + COLUMN_ADRES + " TEXT,"
                + COLUMN_ID + " INT, "
                + "FOREIGN KEY(" + COLUMN_ID + ") REFERENCES "
                + TABLE_USERS + "(id) " + ")";
        db.execSQL(CREATE_FAVOURITES_TABLE);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion,
                          int newVersion) {

    }


    public User findUser(User user) {

        String selectQuery = "SELECT  * FROM " + TABLE_USERS + " WHERE "
                + COLUMN_USERNAME + " = " + user.getUsername();

        SQLiteDatabase db = this.getWritableDatabase();

        Cursor cursor = db.rawQuery(selectQuery, null);

        User findUser = new User();

        if (cursor.moveToFirst()) {
            cursor.moveToFirst();
            findUser.setUserName(cursor.getString(1));
            findUser.setPassword(cursor.getString(2));
            cursor.close();
        } else {
            findUser = null;
        }
        db.close();
        return findUser;

    }

}

When I try to run the findUser(User user) method, I get an SQLException no such column: Gebruikersnaam (note: Gebruikersnaam is the variable in user.getUserName(), I want this function to return the user if it is found or null when it isn't.

What am I doing wrong and is there a better way to use queries?

Crash log:

07-25 15:22:42.345 32264-32264/com.example.hoofdgebruiker.winkelskortrijk E/AndroidRuntime: FATAL EXCEPTION: main Process: com.example.hoofdgebruiker.winkelskortrijk, PID: 32264 android.database.sqlite.SQLiteException: no such column: Gebruikersnaam (code 1): , while compiling: SELECT * FROM users WHERE username = Gebruikersnaam at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316) at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255) at com.example.hoofdgebruiker.winkelskortrijk.Database.MyDBHandler.findUser(MyDBHandler.java:78) at com.example.hoofdgebruiker.winkelskortrijk.Homepage.MainActivity.register(MainActivity.java:49) at com.example.hoofdgebruiker.winkelskortrijk.Homepage.Register$1.onClick(Register.java:47) at android.view.View.performClick(View.java:5198) at android.view.View$PerformClick.run(View.java:21147) at android.os.Handler.handleCallback(Handler.java:739) at android.os.Handler.dispatchMessage(Handler.java:95) at android.os.Looper.loop(Looper.java:148) at android.app.ActivityThread.main(ActivityThread.java:5417) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)

Answer 1

Just update query

String selectQuery = "SELECT  * FROM " + TABLE_USERS + " WHERE "
            + COLUMN_USERNAME + " = " + user.getUsername();

to

String selectQuery = "SELECT  * FROM " + TABLE_USERS + " WHERE "
            + COLUMN_USERNAME + " = '" + user.getUsername()+"'";

Enclose your user.getUsername() value to single quotes because you define COLUMN_USERNAME as text. If your string already contain quotes then it not work. Before adding user.getUsername() to query remove quotes from username as below

user.getUsername().replace("\'","''");

Answer 2

Your problem is here:

String selectQuery = "SELECT  * FROM " + TABLE_USERS + " WHERE "
        + COLUMN_USERNAME + " = " + user.getUsername();

As user.getUsername() is text, you have to do the following:

String selectQuery = "SELECT  * FROM " + TABLE_USERS + " WHERE "
        + COLUMN_USERNAME + " = '" + user.getUsername() + "'";

Cheers