user3227070 user3227070 - 4 months ago 26
Java Question

Sqlliteexception no such column

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.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(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

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("\'","''");