sqlite query failing - no such column

Question

I have a db that is created like this...

public class DataBaseManager extends SQLiteOpenHelper{

    Context mContext;
    private static final String TAG = "DataBaseManager";
    private static final int dbVersion = 1;// ++ for DB  changes

    static final String dbName ="LCInstore";
    //Table Names
    static final String allIcons = "Icons";
    static final String allScreens = "Screens";
    static final String isLookUp = "LookUp";

    //Column Names - LookUp
    static final String colIconID = "IconID";
    static final String colScreenID = "ScreenID";
    static final String colRank = "Rank"; // order

    //Column Names shared by tables: Screens and Icons
    static final String colID = "ID";
    static final String colType = "Type";
    static final String colName = "Name";

    //Column Names - Icons
    static final String colImage = "Image";
    static final String colLabel = "Label";
    static final String colIntent = "Intent";
    static final String colIParams = "Params";


    public DataBaseManager(Context context) {
        super(context, dbName, null, dbVersion); 
        mContext = context;
        Log.v(TAG, "Initaited");

        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        Log.v(TAG, "on create called");

        // Create Icon Table if does not exist
         db.execSQL("CREATE TABLE "+ allIcons +"" +
                "("+colID + " INTEGER PRIMARY KEY AUTOINCREMENT, "+
                    colName + " TEXT," +
                    colImage + " BLOB," +
                    colLabel + " TEXT," +
                    colIntent + " TEXT," +
                    colType + " TEXT)");

        // Create Screens Table if does not exist
         db.execSQL("CREATE TABLE IF NOT EXISTS " + allScreens +"" +
                 "("+colID + " INTEGER PRIMARY KEY AUTOINCREMENT, "+
                    colName + " TEXT," +
                    colType + " TEXT)");

        //Create LookUp Table if does not exist
         db.execSQL("CREATE TABLE IF NOT EXISTS " + isLookUp +"" +
                 "("+colIconID + " INTEGER, "+
                    colScreenID + " INTEGER," +
                    colRank + " INTEGER)");

         InsertInitData(db);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        Log.v(TAG, "on upgrade called");
        //db.execSQL("DROP TABLE IF EXISTS "+allIcons);




    }


    private void InsertInitData(SQLiteDatabase db) {
        //screens
        ContentValues cv2=new ContentValues();
            cv2.put(colName, "DR_Home");
            cv2.put(colType, "dr_home");
            db.insert(allScreens, colID, cv2);
            cv2.put(colName, "DR_Sub");
            cv2.put(colType, "dr_sub");
            db.insert(allScreens, colID, cv2);

            //all Icons
        ContentValues cv=new ContentValues();

           cv.put(colName, "Icon1");
           cv.put(colImage, getBlobFromRes(R.drawable.dr_tablet_icon));
           cv.put(colLabel, "MY ICON 1");
           cv.put(colIntent, "someIntent");
           cv.put(colType, "generic");
           db.insert(allIcons, colID, cv);

           cv.put(colName, "Icon2");
           cv.put(colImage, getBlobFromRes(R.drawable.dr_tablet_icon));
           cv.put(colLabel, "MY ICON 2");
           cv.put(colIntent, "someIntent");
           cv.put(colType, "generic");
           db.insert(allIcons, colID, cv);

           cv.put(colName, "Icon3");
           cv.put(colImage, getBlobFromRes(R.drawable.dr_tablet_icon));
           cv.put(colLabel, "MY ICON 3");
           cv.put(colIntent, "someIntent");
           cv.put(colType, "generic");
           db.insert(allIcons, colID, cv);

           cv.put(colName, "Icon4");
           cv.put(colImage, getBlobFromRes(R.drawable.dr_tablet_icon));
           cv.put(colLabel, "MY ICON 4");
           cv.put(colIntent, "someIntent");
           cv.put(colType, "generic");
           db.insert(allIcons, colID, cv);

           cv.put(colName, "Icon5");
           cv.put(colImage, getBlobFromRes(R.drawable.dr_tablet_icon));
           cv.put(colLabel, "MY ICON 5");
           cv.put(colIntent, "someIntent");
           cv.put(colType, "generic");
           db.insert(allIcons, colID, cv);

           cv.put(colName, "Icon6");
           cv.put(colImage, getBlobFromRes(R.drawable.dr_tablet_icon));
           cv.put(colLabel, "MY ICON 6");
           cv.put(colIntent, "someIntent");
           cv.put(colType, "generic");
           db.insert(allIcons, colID, cv);

           cv.put(colName, "Icon7");
           cv.put(colImage, getBlobFromRes(R.drawable.dr_tablet_icon));
           cv.put(colLabel, "MY ICON 7");
           cv.put(colIntent, "someIntent");
           cv.put(colType, "generic");
           db.insert(allIcons, colID, cv);



        // icon screen lookups
        ContentValues cv3=new ContentValues();
            cv3.put(colIconID, 1);
            cv3.put(colScreenID, 2);
            cv3.put(colRank, 1);
            db.insert(isLookUp, colID, cv3);

            cv3.put(colIconID, 2);
            cv3.put(colScreenID, 2);
            cv3.put(colRank, 2);
            db.insert(isLookUp, colID, cv3);

            cv3.put(colIconID, 3);
            cv3.put(colScreenID, 2);
            cv3.put(colRank, 3);
            db.insert(isLookUp, colID, cv3);

            cv3.put(colIconID, 4);
            cv3.put(colScreenID, 2);
            cv3.put(colRank, 4);
            db.insert(isLookUp, colID, cv3);

            cv3.put(colIconID, 5);
            cv3.put(colScreenID, 2);
            cv3.put(colRank, 5);
            db.insert(isLookUp, colID, cv3);

            cv3.put(colIconID, 6);
            cv3.put(colScreenID, 2);
            cv3.put(colRank, 6);
            db.insert(isLookUp, colID, cv3);

            cv3.put(colIconID, 7);
            cv3.put(colScreenID, 2);
            cv3.put(colRank, 7);
            db.insert(isLookUp, colID, cv3);

    }

you'll notice that one table, 'allScreens' or "Screens" has one row that in the Name column i am putting "DR_Home"

i do a query later to find out the value of the ID column of that row using this method and passing DR_Home to it:

public int getScreenID(String name){
        SQLiteDatabase db=this.getReadableDatabase();
        int sID;
        String selectQuery = "SELECT "+colID+ " FROM " + allScreens + " WHERE " + colName + "=" +name;
        Cursor c = db.rawQuery(selectQuery, null);
        sID = c.getInt(0);
        c.close();
        return sID;
    }

should return the value of the ID column but i am getting an error:

03-22 06:34:35.830: E/AndroidRuntime(29864): Caused by: android.database.sqlite.SQLiteException: no such column: DR_Home: , while compiling: select * from Screens WHERE Name=DR_Home

No such Column? im not asking for a column called DR_Home i am asking for the value of the ID column in the row where the Name column = DR_home.. at least thats what i thought.. i have messed up the syntax somewhere.. please help

Answer 1

Your query is wrong, as WHERE column = string will not work.

String selectQuery = "SELECT "+colID+ " FROM " + allScreens + " WHERE " + colName + "=?";
Cursor c = db.rawQuery(selectQuery, new String[] {name});

Use ? which will be replaced by the second rawQuery() parameter. Strongly recommended.

Not recommended: WHERE column = "string"

Answer 2

Change your old query "SELECT "+colID+ " FROM " + allScreens + " WHERE " + colName + "=" +name; to

 "SELECT "+colID+ " FROM " + allScreens + " WHERE " + colName + "='" +name+"'";

Answer 3

Do it like this:

String selectQuery = String.format("SELECT %s FROM %s WHERE %s = ?",
    colID, allScreens, colName);

Cursor c = db.rawQuery(selectQuery, new String[]{name});

Answer 4

Your query String is wrong

String selectQuery = "SELECT "+colID+ " FROM " + allScreens + " WHERE " + colName + " = '" +name + "'";

the name you give in where should be in single quotes

Answer 5

name = DR_home means "column name equals column DR_home. You probably want to quote it if you want to string search (which I surmise you do, but I haven't peeked your code in depth since it's too long): name = 'DR_Home'.