No such table: (code 1) while compiling: SELECT * FROM event

android android-listview onclicklistener

21,767

Solution 1

Finally after some chat in the chat section on stackoverflow, I found my answer. The table wasn't creating in my local database file in the app. Not the server. So I added db=openOrCreateDatabase("StudentDB", Context.MODE_PRIVATE, null); db.execSQL("CREATE TABLE IF NOT EXISTS student(eid VARCHAR,ename VARCHAR,esdate VARCHAR);"); to the onCreate method and it works fine now :)

Solution 2

You need a space here:

KEY_ID + "INTEGER PRIMARY KEY,"

KEY_ID + " INTEGER PRIMARY KEY,"

Also, I'm not sure why do you execute this other line as well:

db.execSQL("PRAGMA foreign_keys=ON;");

21,767

Author by

Yohan Blake

Beginner at coding but ready to learn :)

Updated on August 19, 2020

Comments

Yohan Blake over 3 years

First of all, please do NOT mark this question as duplicate because I have searched through EVERY question that has been asked about this error on stackoverflow, but still nothing helps me. I tried increasing the database version, uninstalling and reinstalling the app, trying different emulators and even tried on an actual phone.

This is my logcat:

04-26 13:43:43.970: E/SQLiteLog(2331): (1) no such table: event
04-26 13:43:43.987: D/AndroidRuntime(2331): Shutting down VM
04-26 13:43:44.007: E/AndroidRuntime(2331): FATAL EXCEPTION: main
04-26 13:43:44.007: E/AndroidRuntime(2331): Process: com.example.shareity, PID: 2331
04-26 13:43:44.007: E/AndroidRuntime(2331): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.shareity/com.example.shareity.NewEvent}: android.database.sqlite.SQLiteException: no such table: event (code 1): , while compiling: SELECT  * FROM event
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2298)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2360)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.app.ActivityThread.access$800(ActivityThread.java:144)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1278)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.os.Handler.dispatchMessage(Handler.java:102)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.os.Looper.loop(Looper.java:135)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.app.ActivityThread.main(ActivityThread.java:5221)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at java.lang.reflect.Method.invoke(Native Method)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at java.lang.reflect.Method.invoke(Method.java:372)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:899)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:694)
04-26 13:43:44.007: E/AndroidRuntime(2331): Caused by: android.database.sqlite.SQLiteException: no such table: event (code 1): , while compiling: SELECT  * FROM event
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at com.example.shareity.library.DBHandlerEvent.getEventDetails(DBHandlerEvent.java:97)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at com.example.shareity.NewEvent.onCreate(NewEvent.java:100)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.app.Activity.performCreate(Activity.java:5933)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1105)
04-26 13:43:44.007: E/AndroidRuntime(2331):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2251)
04-26 13:43:44.007: E/AndroidRuntime(2331):     ... 10 more
04-26 13:43:47.066: I/Process(2331): Sending signal. PID: 2331 SIG: 9

The error occurs when I click on a list item in my ListView.

public class ListNew extends Activity {
ListView list;
TextView eid;
TextView ename; 
TextView edesc;
//Button Btngetdata;
ArrayList<HashMap<String, String>> oslist = new ArrayList<HashMap<String, String>>();

//URL to get JSON Array
private static String url = "http://<mylink>/test.php/";

//JSON Node Names 
private static final String TAG_OS = "events";
private static final String TAG_EID = "eid";
private static final String TAG_ENAME = "ename";
private static final String TAG_ETYPE = "etype";
private static final String TAG_EDESC = "edesc";
private static final String TAG_ESDATE = "esdate";
private static final String TAG_EEDATE = "eedate";
private static final String TAG_ESTIME = "estime";
private static final String TAG_EETIME = "eetime";
private static final String TAG_LOCATION = "location";
private static final String TAG_CREATED_AT = "created_at";
private static final String TAG_EDITED_AT = "edited_at";
private static final String TAG_CREATEDBY = "createdby";
private static final String TAG_APPROVED = "approved";

JSONArray events = null;




@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    setContentView(R.layout.listnew);
    oslist = new ArrayList<HashMap<String, String>>();

    new JSONParse().execute();


    /*
    Btngetdata = (Button)findViewById(R.id.getdata);
    Btngetdata.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View view) {
             new JSONParse().execute();


        }
    });
    */

}



private class JSONParse extends AsyncTask<String, String, JSONObject> {
     private ProgressDialog pDialog;
    @Override
    protected void onPreExecute() {
        super.onPreExecute();
         eid = (TextView)findViewById(R.id.vers);
         ename = (TextView)findViewById(R.id.name);
         edesc = (TextView)findViewById(R.id.api);
        pDialog = new ProgressDialog(ListNew.this);
        pDialog.setMessage("Getting Data ...");
        pDialog.setIndeterminate(false);
        pDialog.setCancelable(true);
        pDialog.show();



    }

    @Override
    protected JSONObject doInBackground(String... args) {

        JSONParser jParser = new JSONParser();

        // Getting JSON from URL
        JSONObject json = jParser.getJSONFromUrl2(url);
        return json;
    }
     @Override
     protected void onPostExecute(JSONObject json) {
         pDialog.dismiss();
         try {
                // Getting JSON Array from URL
                events = json.getJSONArray(TAG_OS);
                for(int i = 0; i < events.length(); i++){
                JSONObject c = events.getJSONObject(i);

                // Storing  JSON item in a Variable
                String eid = c.getString(TAG_EID);
                String ename = c.getString(TAG_ENAME);
                String etype = c.getString(TAG_ETYPE);
                String edesc = c.getString(TAG_EDESC);
                String esdate = c.getString(TAG_ESDATE);
                String eedate = c.getString(TAG_EEDATE);
                String estime = c.getString(TAG_ESTIME);
                String eetime = c.getString(TAG_EETIME);
                String location = c.getString(TAG_LOCATION);
                String created_at = c.getString(TAG_CREATED_AT);
                String edited_at = c.getString(TAG_EDITED_AT);
                String createdby = c.getString(TAG_CREATEDBY);
                String approved = c.getString(TAG_APPROVED);




                // Adding value HashMap key => value




                HashMap<String, String> map = new HashMap<String, String>();

                map.put(TAG_EID, eid);
                map.put(TAG_ENAME, ename);
                map.put(TAG_ETYPE, etype);
                map.put(TAG_EDESC, edesc);
                map.put(TAG_ESDATE, esdate);
                map.put(TAG_EEDATE, eedate);
                map.put(TAG_ESTIME, estime);
                map.put(TAG_EETIME, eetime);
                map.put(TAG_LOCATION, location);
                map.put(TAG_CREATED_AT, created_at);
                map.put(TAG_EDITED_AT, edited_at);
                map.put(TAG_CREATEDBY, createdby);
                map.put(TAG_APPROVED, approved);


                oslist.add(map);
                list=(ListView)findViewById(R.id.list);

                //Log.d("TAG_is d first one", TAG_APPROVED + "," + approved);



                ListAdapter adapter = new SimpleAdapter(ListNew.this, oslist,
                        R.layout.list_v,
                        new String[] { TAG_ENAME,TAG_CREATEDBY, TAG_CREATED_AT }, new int[] {
                                R.id.vers,R.id.name, R.id.api});


                list.setAdapter(adapter);

                //Log.d("after setting adapter", TAG_EID + "," + TAG_ENAME);
                list.setOnItemClickListener(new AdapterView.OnItemClickListener() {

                    @Override
                    public void onItemClick(AdapterView<?> parent, View view,
                                            int position, long id) {
                        //Toast.makeText(MainActivity.this, "You Clicked at "+oslist.get(+position).get("ename"), Toast.LENGTH_SHORT).show();
                        Intent i = new Intent(ListNew.this, NewEvent.class);
                        i.putExtra("eid", oslist.get(+position).get("eid") );
                        //i.putExtra("tel", tels.get(position) );
                        i.putExtra("ename", oslist.get(+position).get("ename") );
                        i.putExtra("etype", oslist.get(+position).get("etype") );
                        i.putExtra("edesc", oslist.get(+position).get("edesc") );
                        i.putExtra("esdate", oslist.get(+position).get("esdate") );
                        i.putExtra("eedate", oslist.get(+position).get("eedate") );
                        i.putExtra("estime", oslist.get(+position).get("estime") );
                        i.putExtra("eetime", oslist.get(+position).get("eetime") );
                        i.putExtra("location", oslist.get(+position).get("location") );
                        i.putExtra("created_at", oslist.get(+position).get("created_at") );
                        i.putExtra("edited_at", oslist.get(+position).get("edited_at") );
                        i.putExtra("createdby", oslist.get(+position).get("createdby") );
                        i.putExtra("approved", oslist.get(+position).get("approved") );
                        startActivity(i);

                    }
                });


            }//end if
        } catch (JSONException e) {
            e.printStackTrace();
        }


     }
}

}

This is my next intent which the data should go to when a list item is clicked.

public class NewEvent extends Activity {


Button bk, newact;
String ename;
String eid;



@Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.event);
    //DBHandlerEvent db = new DBHandlerEvent(getApplicationContext());
    //HashMap<String, String> event = new HashMap<String, String>();
    //event = db.getEventDetails();



    bk = (Button) findViewById(R.id.btnbackne);
    newact = (Button) findViewById(R.id.btnnewact);



    final TextView en = (TextView) findViewById(R.id.tvename);
    final TextView des = (TextView) findViewById(R.id.tvdescription);
    final TextView sd = (TextView) findViewById(R.id.tvsdate);
    final TextView ed = (TextView) findViewById(R.id.tvedate);
    final TextView location = (TextView) findViewById(R.id.tvgoal);
    final TextView reg = (TextView) findViewById(R.id.tveventregdate);



    Intent intent= getIntent();
       eid = intent.getStringExtra("eid");
       ename = intent.getStringExtra("ename");
       String edesc = intent.getStringExtra("edesc");
       String esdate = intent.getStringExtra("esdate");
       String eedate = intent.getStringExtra("eedate");
       String estime = intent.getStringExtra("estime");
       String eetime = intent.getStringExtra("eetime");
       String loc = intent.getStringExtra("location");
       String createdby= intent.getStringExtra("createdby");



       //en.setText(eid);
       en.setText(ename);
       des.setText(edesc);
       sd.setText(esdate);
       ed.setText(eedate);
       //en.setText(estime);
       //en.setText(eetime);
       location.setText(loc);
       reg.setText(createdby);





    bk.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            Intent myIntent = new Intent(v.getContext(), ListNew.class);
            startActivityForResult(myIntent, 0);
            finish();
        }
    });

    DatabaseHandler db = new DatabaseHandler(getApplicationContext());
    HashMap<String,String> user = new HashMap<String, String>();
    user = db.getUserDetails();

    DBHandlerEvent dbe = new DBHandlerEvent(getApplicationContext());
    HashMap<String,String> event = new HashMap<String, String>();
    event = dbe.getEventDetails();


    String logd = user.get("fname");
    String ev = event.get("createdBy");
    if (logd.equals(ev)){
        newact.setVisibility(View.VISIBLE);
    }else{
        newact.setVisibility(View.GONE);
    }

    newact.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            /*
            Intent myIntent2 = new Intent(v.getContext(), CreateActivity.class);
            startActivityForResult(myIntent2, 0);
            */
               Intent send = new Intent(NewEvent.this, Donate.class);
               send.putExtra("eid", eid);
               send.putExtra("ename", ename.toString());
               startActivity(send);

               finish();
        }
    });





    final TextView createdBy = (TextView) findViewById(R.id.tvCreatedBy);
    //createdBy.setText("Event created by:  "+user.get("fname"));



}

}

Edit: added DB helper class

public void onCreate(SQLiteDatabase db) {
    String CREATE_EVENT_TABLE = "CREATE TABLE " + TABLE_EVENT + "(" 
            + KEY_ID + "INTEGER PRIMARY KEY,"
            + KEY_ENAME + " TEXT,"
            + KEY_ETYPE + " TEXT,"
            + KEY_EDES + " TEXT,"
            + KEY_ESDATE + " TEXT,"
            + KEY_EEDATE + " TEXT,"
            + KEY_ESTIME + " TEXT,"
            + KEY_EETIME + " TEXT,"
            + KEY_LOCATION + " TEXT,"
            + KEY_CREATED_AT + " TEXT,"
            + KEY_EDITED_AT + " TEXT,"
            + KEY_CREATED_BY + " TEXT " + ")";
    db.execSQL(CREATE_EVENT_TABLE);
    db.execSQL("PRAGMA foreign_keys=ON;");
}

 // Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENT);
    // Create tables again
    onCreate(db);
}

Yohan Blake about 9 years

Yes, the table exists. And I have my db functions. I have added it to the question now too
Yohan Blake about 9 years

I did both what you said. Still same issue
Yohannes Ejigu Ademe about 9 years

@Der Golem 's answer is right there should be space in here KEY_ID + " INTEGER PRIMARY KEY,"
Yohannes Ejigu Ademe about 9 years

are you sure variable TABLE_EVENT is representing the string "event". If not, you may be crating a table with different name.
Yohan Blake about 9 years

yes. public static final String TABLE_EVENT = "event";
Yohan Blake about 9 years

I did. I tried on different emulators too. And on a real phone too. Same problem
Yohannes Ejigu Ademe about 9 years

TABLE_EVENT + "(" this on should also have space right before the brackets like this TABLE_EVENT + " ("
Phantômaxx about 9 years

@YohannesEjiguAdeme No, a space near a bracket is completely ininfluent.
Phantômaxx about 9 years

... Is TABLE_EVENT defined somewhere and assigned as = "event"?
Yohan Blake about 9 years

yes. public static final String TABLE_EVENT = "event";
Phantômaxx about 9 years

Did you change your database original structure? I.e.: renamed the table from an older name, or altered its structure by adding, removing or renaming columns?
Yohan Blake about 9 years

yes, I have changed it a couple of times. Then I have increased the database version
Phantômaxx about 9 years

So, now you have final static int DATABASE_VERSION = 3;?
Yohan Blake about 9 years

I have version 19, haha
Yohan Blake about 9 years

private static final int DATABASE_VERSION = 19;
Phantômaxx about 9 years

Obviously I'm expecting you to create the DB helper class, whatever is called, by using the new keyword: myDB = new myDBHelperClass(myContext);
Yohan Blake about 9 years

um, I'm confused, where should I make this change?
Yohan Blake about 9 years

Let us continue this discussion in chat.