SimpleCursorAdapter to get Data from Database to ListView

Question

I have to create an app and for this I need to create a database. I'm able to create new entries, but I don't know how to read the database to get the stored data and put it in a ListView.

public class DatabaseHelper extends SQLiteOpenHelper {

private static final String DATABASE_NAME="students.db";
private static final String TABLE_NAME="student_details";
private static final String COL_1="ID";
private static final String COL_2="NAME";
private static final String COL_3="SURNAME";
private static final String COL_4="MARKS";
private static final int DATABASE_VERSION=1;

public DatabaseHelper (Context context)
{
    super(context, DATABASE_NAME, null, DATABASE_VERSION);

}



@Override
public void onCreate(SQLiteDatabase db) {


db.execSQL("create table " + TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT,SURNAME TEXT,MARKS INTEGER)");

    Log.d("info", "TABLE WAS CREATED");


}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL("DROP TABLE IF EXIST " + TABLE_NAME);
    onCreate(db);
    Log.d("Info","TABLE WAS UPDATED");

}

public boolean insertdata(String name,String surname,String marks)
{

    SQLiteDatabase db= this.getWritableDatabase();

    ContentValues contentValues= new ContentValues();
    contentValues.put(COL_2,name);
    contentValues.put(COL_3, surname);
    contentValues.put(COL_4, marks);

    long result=db.insert(TABLE_NAME,null,contentValues);
    if (result==-1)
        return false;
    else
        return true;

}
public Cursor getallData()
{
    SQLiteDatabase db= this.getWritableDatabase();

    Cursor res=db.rawQuery("SELECT * FROM "+TABLE_NAME,null);
    return res;

}
public boolean updateData(String id,String name,String surname,String marks) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues contentValues = new ContentValues();
   // contentValues.put(COL_1, id);
           contentValues.put(COL_2, name);
    contentValues.put(COL_3, surname);
    contentValues.put(COL_4, marks);

    db.update(TABLE_NAME, contentValues, COL_1 + " =? ", new String[]{id});
    return true;

}
public Cursor getData(int id){
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res =  db.rawQuery( "select * from student_details where ID="+id+"", null );
    if(res != null && res.moveToFirst()) {
        return res;
    }
    return res;
}
public int deleteData(String id)
{
    SQLiteDatabase db = this.getWritableDatabase();
    return db.delete(TABLE_NAME, COL_1 + " =?", new String[]{id});

}

 public class DisplayData extends Activity {

    DatabaseHelper myDB;
    ListView list;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.data_display);
        list= (ListView) findViewById(R.id.listView);
        myDB= new DatabaseHelper(this);
        //Cursor res = myDB.getallData();
        String[] from={"ID","NAME","SURNAME","MARKS"};

        int[] to={R.id.textView11,R.id.textView13,R.id.textView15 };
        Cursor cursor=myDB.getallData();
       // allData();
        SimpleCursorAdapter adapter= new SimpleCursorAdapter(this,R.layout.custom_data_display,cursor,from,to);
        list.setAdapter(adapter);
        // adapter= new ArrayAdapter(this,R.layout.custom_data_display,)


    }

This is the error I get:

com.example.ky.studentmarks E/AndroidRuntime﹕ FATAL EXCEPTION: main
        Process: com.example.aeiltech.studentmarks, PID: 23469
        java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.ky.studentmarks/com.example.aeiltech.studentmarks.DisplayData}: java.lang.IllegalArgumentException: column '_id' does not exist
                at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2436)
                at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2498)
                at android.app.ActivityThread.access$900(ActivityThread.java:179)
                at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1324)
                at android.os.Handler.dispatchMessage(Handler.java:102)
                at android.os.Looper.loop(Looper.java:146)
                at android.app.ActivityThread.main(ActivityThread.java:5641)
                at java.lang.reflect.Method.invokeNative(Native Method)
                at java.lang.reflect.Method.invoke(Method.java:515)
                at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1288)
                at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1104)
                at dalvik.system.NativeStart.main(Native Method)

score 0 · Answer 1 · edited May 23 '17 at 12:03

You should first try a tutorial before jumping into coding that you don't understand.
Official:
1. http://developer.android.com/training/basics/data-storage/databases.html.
Similar:
2. Android column '_id' does not exist?.
Good tutorial:
3. http://www.vogella.com/tutorials/AndroidSQLite/article.html.
Your problem seem to be that you don't have that column("_id") that the SimpleCursor needs in the database. Check the (2) for this problem that you have and also in order to understand how to fix the problem take a look at (3).
Happy coding.

score 0 · Answer 2 · answered Sep 29 '15 at 12:47

0

Change id to _id in your table. Also, you need to declare SimpleCursorAdapter adapter as a field of the DisplayData class not as a local variable within onCreate.

answered Sep 29 '15 at 12:47

saeed khalafinejad

1,139
9
22

Still Have a Problem @saeed – Karthick Sep 29 '15 at 12:59
The error message says that the _id column does not exist. The first column of your table should be named _id. Also, declare it as `_id integer primary key autoincrement`. – saeed khalafinejad Sep 30 '15 at 14:29

score -1 · Answer 3 · answered Sep 29 '15 at 12:55

-1

Try creating your database like this:

 private static final String DATABASE_CREATE = "create table "
        + TABLE_NAME + "(" + COL_1 +
        " integer primary key autoincrement, "  +
        COL_2 + " text not null, "  + COL_3 + " text not null, " + COL_4 + " integer);";

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(DATABASE_CREATE);
}

Also in public Cursor getallData() , make sure to put res.moveToFirst() , before you return res.

Take a look at voggela tutorial because you need to have a DAO class to acces data from the database without using it directly.

answered Sep 29 '15 at 12:55

mariuss

1,177
2
14
30

Still I Have A SAme PRoblem – Karthick Sep 29 '15 at 12:59
In your insert data() function you don't put ID – mariuss Sep 29 '15 at 13:21
Something like : long insertId = db.insert(DatabaseHelper.TABLE_NAME, null, contentValues); Cursor cursor = db.query(DatabaseHelper.TABLE_NAME, allColumns, DatabaseHelper.COL_1 + " = " + insertId, null, null, null, null); – mariuss Sep 29 '15 at 13:23

SimpleCursorAdapter to get Data from Database to ListView

3 Answers3