0

I tried to get values from a column called LocationCode to define whether to display the shop names. However, there is an error said that there is no such column. There is no mistake in spellings.

And this is the code:

public List<String> getQuotes() {
    List<String> list = new ArrayList<>();
    Cursor cursor = database.rawQuery("SELECT ShopName,LocationCode FROM WhereToEat WHERE LocationCode = 'WhiteFarm' ", null);
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        list.add(cursor.getString(0));
        cursor.moveToNext();
    }
    cursor.close();
    return list;
}

Thank you.

Error log

FATAL EXCEPTION: main Process: com.projects.randomcreation.wheretoeat, PID: 2122 java.lang.RuntimeException: Unable to start activity ComponentInfo{com.projects.randomcreation.wheretoeat/com.projects.randomcreation.wheretoeat.databases.DatabaseGrabber}: android.database.sqlite.SQLiteException: no such column: ShopName (code 1): , while compiling: SELECT ShopName,LocationCode FROM WhereToEat WHERE LocationCode = 'WhiteFarm' at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2416) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476) at android.app.ActivityThread.-wrap11(ActivityThread.java) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344) at android.os.Handler.dispatchMessage(Handler.java:102) 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) Caused by: android.database.sqlite.SQLiteException: no such column: ShopName (code 1): , while compiling: SELECT ShopName,LocationCode FROM WhereToEat WHERE LocationCode = 'WhiteFarm' 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.projects.randomcreation.wheretoeat.databases.DatabaseAccess.getQuotes(DatabaseAccess.java:65) at com.projects.randomcreation.wheretoeat.databases.DatabaseGrabber.onCreate(DatabaseGrabber.java:27) at android.app.Activity.performCreate(Activity.java:6237) at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1107) at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2369) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476)  at android.app.ActivityThread.-wrap11(ActivityThread.java)  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344)  at android.os.Handler.dispatchMessage(Handler.java:102)  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)

Full code of DatabaseOpenHelper

public class DatabaseAccess {
private SQLiteOpenHelper openHelper;
private SQLiteDatabase database;
private static DatabaseAccess instance;

/**
 * Private constructor to aboid object creation from outside classes.
 *
 * @param context
 */
public DatabaseAccess(Context context) {
    this.openHelper = new DatabaseOpenHelper(context);
}

/**
 * Return a singleton instance of DatabaseAccess.
 *
 * @param context the Context
 * @return the instance of DabaseAccess
 */
public static DatabaseAccess getInstance(DatabaseGrabber context) {
    if (instance == null) {
        instance = new DatabaseAccess(context);
    }
    return instance;
}

/**
 * Open the database connection.
 */
public void open() {
    this.database = openHelper.getWritableDatabase();
}

/**
 * Close the database connection.
 */
public void close() {
    if (database != null) {
        this.database.close();
    }
}

/**
 * Read all quotes from the database.
 *
 * @return a List of quotes
 */
public List<String> getQuotes() {
    List<String> list = new ArrayList<>();
    Cursor cursor = database.rawQuery("SELECT ShopName,LocationCode FROM WhereToEat WHERE LocationCode = '+WhiteFarm+' ", null);
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        list.add(cursor.getString(0));
        cursor.moveToNext();
    }
    cursor.close();
    return list;
}
}

Update: 

CREATE TABLE "WhereToEat" (
`ShopName`  TEXT,
`Location`  TEXT,
`LocationCode`  TEXT,
`Photo` BLOB);

I believe the column exists.

hrmck
  • 83
  • 3
  • 9

1 Answers1

0

Try this. Probably you need to cover "WhiteFarm" between single quotes ('WhiteFarm '). It can be achieved by doing this : 

    Cursor cursor = database.rawQuery("SELECT ShopName,LocationCode FROM WhereToEat WHERE LocationCode = " + "'" + WhiteFarm + "'", null);

I agree with Harshad, you should use +WhiteFarm+ only in case when column type is int.

You can find some sample queries similar to what you are doing here

Рахул Маквана
  • 944
  • 2
  • 15
  • 38