There is no such column error while fetching data from sqlite in Android

Question

           Cursor m= a.getData("Akshay");
            StringBuffer sb= new StringBuffer();
            if(m!=null&&m.getCount()>0)
            {while (m.moveToNext())
            {
                sb.append(m.getString(m.getColumnIndex("Name")));


            }

                nametext.setText(sb.toString());

            }


       //Mydbhelper class//

       public Cursor getData(String ii) {


                SQLiteDatabase db = this.getReadableDatabase();

                Cursor res = db.rawQuery("select * from myTable where 
                 Email="+ii,null);

                return res;
      }

//LOG

FATAL EXCEPTION: main Process: com.example.user3.security, PID: 15620 java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.user3.security/com.example.user3.security.ProfileActivity}: android.database.sqlite.SQLiteException: no such column: Akshay (code 1): , while compiling: select * from myTable where Email=Akshay at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2449) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2509) at android.app.ActivityThread.access$1000(ActivityThread.java:153) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1373) at android.os.Handler.dispatchMessage(Handler.java:102) at android.os.Looper.loop(Looper.java:154) at android.app.ActivityThread.main(ActivityThread.java:5529) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:739) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:629) Caused by: android.database.sqlite.SQLiteException: no such column: Akshay (code 1): , while compiling: select * from myTable where Email=Akshay 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.user3.security.myDbHelper.getData(myDbHelper.java:101) at com.example.user3.security.ProfileActivity.onCreate(ProfileActivity.java:56) at android.app.Activity.performCreate(Activity.java:6303) at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1108) at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2402) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2509) at android.app.ActivityThread.access$1000(ActivityThread.java:153) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1373) at android.os.Handler.dispatchMessage(Handler.java:102) at android.os.Looper.loop(Looper.java:154) at android.app.ActivityThread.main(ActivityThread.java:5529) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:739) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:629) 11-27 12:01:24.886 15620-15620/com.example.user3.security E/MQSEventManagerDelegate: failed to get MQSService.

You had changed your table schema so just update database version in helper class constructor. — Abhishek Singh, Nov 27 '17 at 06:35
so change it to 3. if problem persists then check that you are creating table with `Email` field. P.S. may be you created field with `email` not `Email` — Abhishek Singh, Nov 27 '17 at 06:37
change your query to `....where Email='"+ii+"'"` String values should be in single quote in where clause — Abhishek Singh, Nov 27 '17 at 06:42

score 2 · Answer 1 · answered Nov 27 '17 at 06:43

Your query has an issue. You are running the following literal query:

select * from myTable where Email = Akshay;

If you read the error message closely, you will see complaints about not being able to find the column "Akshay" which of course was not intended to be a column, but rather a string literal in the final query.

The fix here is to bind the string literal in your query correctly. Consider the following refactor of getData():

public Cursor getData(String ii) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res = db.rawQuery("select * from myTable where Email = ?",
        new String[] { ii }
    );

    return res;
}

score 0 · Answer 2 · answered Nov 27 '17 at 06:40

There's a problem with your SQL query. The current query is looking for another column named "Akshay". You need to modify your code so that your query has leading and trailing single quote with the parameter value.

Cursor res = db.rawQuery("select * from myTable where 
             Email='"+ii+"'",null);

There is no such column error while fetching data from sqlite in Android

2 Answers2