How to Create ListView From SQLite Database using cursor adapter

Question

I have created a database which contains ID, Name, Family, Age, Student ID, Student Phone Number. Now, I'm trying to retrieve the Name and Family of students by searching the ID and then display the retrieved data in a custom list view using cursor adapter, but my code doesn't work. Please tell me how to display retrieved data in a list view

here I post my code:

DatabaseManager.java

package com.example.tempcursoradapter;


import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseManager extends SQLiteOpenHelper{
    public DatabaseManager(Context context)
    {
        super(context,"myDB",null,1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

        String query= "CREATE TABLE tbl_student ( "+
                " ID            INT            PRIMARY KEY"+
                " UNIQUE,"+
                " Name          VARCHAR( 50 ),"+
                " Family        VARCHAR( 50 ),"+
                " Age           INT,"+
                " StudentNumber VARCHAR( 11 ),"+
                " Tel           VARCHAR( 15 )"+ 
                " );";
        db.execSQL(query);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
        // TODO Auto-generated method stub

        db.execSQL("DROP TABLE IF EXISTS tbl_student");
        onCreate(db);
    }


    public boolean addStudent (Student student)
    {
        boolean result;
        try{
        String query="INSERT INTO tbl_Student (ID,Name,Family,Age,StudentNumber,Tel) " +
                "VALUES ("+student.ID+",'"+student.Name+"','"+student.Family+"',"+27+",'"
                +student.stdNum+"','"+student.Tel+"')";
        SQLiteDatabase db=this.getWritableDatabase();
        db.execSQL(query);
        db.close();
        result = true;
        }
        catch(Exception e){
        result=false;
        }

        return result;
        }


    public boolean updataStudent (Student student)
    {
        boolean result;
        try{
        String query ="UPDATE tbl_Student  SET Name='"+student.Name+"',Family='"+student.Family+
                "',Age="+student.age+",StudentNumber='"+student.stdNum+"',Tel='"+student.Tel+"'" +
                " where ID = "+student.ID+"";
        SQLiteDatabase db =this.getWritableDatabase();
        db.execSQL(query);
        db.close();
        result =true;
        }
        catch (Exception e){
            result= false;
        }
        return result;
        }

    public Cursor getStudent (int id)
    {

        String query ="SELECT Name,Family FROM tbl_Student";
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor =db.rawQuery(query, null);

        return cursor;
    }

    public int getStudentCount()
    {
        int result=0;
        String query="SELECT * FROM tbl_student";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor=db.rawQuery(query, null);
        result=cursor.getCount();
        db.close();
        return result;
    }

    public boolean deleteStudent (int id)
    {
        boolean result;
        try{
        String query ="DELETE FROM tbl_student WHERE ID="+id;
        SQLiteDatabase db=this.getWritableDatabase();
        db.execSQL(query);
        db.close();
        result=true;
        }
        catch(Exception e)
        {
            result=false;
        }
        return result;
    }


}

Student.java

package com.example.tempcursoradapter;

public class Student {
    public int ID,age;
    public String Name,Family,stdNum,Tel;


}

TodoCursorAdapter.java

package com.example.tempcursoradapter;

import android.content.Context;
import android.database.Cursor;
import android.support.v4.widget.CursorAdapter;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;

public class TodoCursorAdapter extends CursorAdapter {
      public TodoCursorAdapter(Context context, Cursor cursor, int flags) {
          super(context, cursor, 0);
      }


      @Override
      public View newView(Context context, Cursor cursor, ViewGroup parent) {
          return LayoutInflater.from(context).inflate(R.layout.item_todo, parent, false);
      }


      @Override
      public void bindView(View view, Context context, Cursor cursor) {
          // Find fields to populate in inflated template
          TextView tvName = (TextView) view.findViewById(R.id.tvname);
          TextView tvFamily = (TextView) view.findViewById(R.id.tvfamily);
          // Extract properties from cursor
          String body = cursor.getString(cursor.getColumnIndexOrThrow("Name"));
          String priority = cursor.getString(cursor.getColumnIndexOrThrow("Family"));
          // Populate fields with extracted properties
          tvName.setText(body);
          tvFamily.setText(String.valueOf(priority));
      }


    }

MainActivity.java

package com.example.tempcursoradapter;



import android.R.integer;
import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;

public class MainActivity extends Activity {

    ListView lv;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        lv=(ListView) findViewById(R.id.listView1);
        // add 10 sample data to database
        final DatabaseManager db = new DatabaseManager(this);
        for (int j = 0; j < 10; j++) {
             Student student = new Student();
            student.ID=j;
            student.Name="name_"+String.valueOf(j);
            student.Family="family_"+String.valueOf(j);
            student.age=j;
            student.stdNum="stdNum_"+String.valueOf(j);
            student.Tel="telNum_"+String.valueOf(j);
             db.addStudent(student);
        }
// search for ID=1 
// I didn't used a Edit Text for input for simplicity and I only search for ID=1 
     Cursor todoCursor = db.getStudent(1);
// I know this line is my code's problem. my code crash at this line!
     TodoCursorAdapter todoAdapter = new TodoCursorAdapter(MainActivity.this, todoCursor,0);

     lv.setAdapter(todoAdapter);


    }


    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

}

This is outputy LogCat

02-27 12:28:54.129: E/Trace(6837): error opening trace file: No such file or directory (2)
02-27 12:28:55.258: D/dalvikvm(6837): GC_FOR_ALLOC freed 44K, 7% free 2555K/2720K, paused 44ms, total 50ms
02-27 12:28:55.268: I/dalvikvm-heap(6837): Grow heap (frag case) to 3.216MB for 635812-byte allocation
02-27 12:28:55.328: D/dalvikvm(6837): GC_FOR_ALLOC freed 2K, 6% free 3173K/3344K, paused 56ms, total 56ms
02-27 12:28:55.458: D/dalvikvm(6837): GC_CONCURRENT freed <1K, 5% free 3190K/3344K, paused 7ms+19ms, total 130ms
02-27 12:28:55.958: D/AndroidRuntime(6837): Shutting down VM
02-27 12:28:55.958: W/dalvikvm(6837): threadid=1: thread exiting with uncaught exception (group=0x40a71930)
02-27 12:28:55.978: E/AndroidRuntime(6837): FATAL EXCEPTION: main
02-27 12:28:55.978: E/AndroidRuntime(6837): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.tempcursoradapter/com.example.tempcursoradapter.MainActivity}: java.lang.IllegalArgumentException: column '_id' does not exist
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2180)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.app.ActivityThread.access$600(ActivityThread.java:141)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.os.Handler.dispatchMessage(Handler.java:99)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.os.Looper.loop(Looper.java:137)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.app.ActivityThread.main(ActivityThread.java:5041)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at java.lang.reflect.Method.invokeNative(Native Method)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at java.lang.reflect.Method.invoke(Method.java:511)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at dalvik.system.NativeStart.main(Native Method)
02-27 12:28:55.978: E/AndroidRuntime(6837): Caused by: java.lang.IllegalArgumentException: column '_id' does not exist
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:303)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.support.v4.widget.CursorAdapter.init(CursorAdapter.java:174)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.support.v4.widget.CursorAdapter.<init>(CursorAdapter.java:151)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at com.example.tempcursoradapter.TodoCursorAdapter.<init>(TodoCursorAdapter.java:13)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at com.example.tempcursoradapter.MainActivity.onCreate(MainActivity.java:42)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.app.Activity.performCreate(Activity.java:5104)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
02-27 12:28:55.978: E/AndroidRuntime(6837):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
02-27 12:28:55.978: E/AndroidRuntime(6837):     ... 11 more

Thank you for your kindness

I have changed my table as below, yet it still crashes.

String query= "CREATE TABLE tbl_student ( "+
                " _id integer PRIMARY KEY autoincrement," +
                " ID          INT            "+
                " UNIQUE,"+
                " Name          VARCHAR( 50 ),"+
                " Family        VARCHAR( 50 ),"+
                " Age           INT,"+
                " StudentNumber VARCHAR( 11 ),"+
                " Tel           VARCHAR( 15 )"+ 
                " );";
        db.execSQL(query);

output LogCat

02-27 13:55:40.529: W/dalvikvm(9220): threadid=1: thread exiting with uncaught exception (group=0x40a71930)
02-27 13:55:40.538: E/AndroidRuntime(9220): FATAL EXCEPTION: main
02-27 13:55:40.538: E/AndroidRuntime(9220): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.tempcursoradapter/com.example.tempcursoradapter.MainActivity}: java.lang.IllegalArgumentException: column '_id' does not exist
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2180)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.app.ActivityThread.access$600(ActivityThread.java:141)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.os.Handler.dispatchMessage(Handler.java:99)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.os.Looper.loop(Looper.java:137)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.app.ActivityThread.main(ActivityThread.java:5041)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at java.lang.reflect.Method.invokeNative(Native Method)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at java.lang.reflect.Method.invoke(Method.java:511)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at dalvik.system.NativeStart.main(Native Method)
02-27 13:55:40.538: E/AndroidRuntime(9220): Caused by: java.lang.IllegalArgumentException: column '_id' does not exist
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:303)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.support.v4.widget.CursorAdapter.init(CursorAdapter.java:174)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.support.v4.widget.CursorAdapter.<init>(CursorAdapter.java:122)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at com.example.tempcursoradapter.TodoCursorAdapter.<init>(TodoCursorAdapter.java:13)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at com.example.tempcursoradapter.MainActivity.onCreate(MainActivity.java:42)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.app.Activity.performCreate(Activity.java:5104)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
02-27 13:55:40.538: E/AndroidRuntime(9220):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
02-27 13:55:40.538: E/AndroidRuntime(9220):     ... 11 more
02-27 14:00:40.689: I/Process(9220): Sending signal. PID: 9220 SIG: 9

I also used this commands but my problem is the same

String query= "CREATE TABLE tbl_student ( "+
        " _id            INT            PRIMARY KEY"+
        " UNIQUE,"+
        " Name          VARCHAR( 50 ),"+
        " Family        VARCHAR( 50 ),"+
        " Age           INT,"+
        " StudentNumber VARCHAR( 11 ),"+
        " Tel           VARCHAR( 15 )"+ 
        " );";
db.execSQL(query);

@ShadabAnsari My code crashes and display this : "Unfortunately, TempCursorAdapter has stopped" — Maziar Shoaei
– Maziar Shoaei, Commented Mar 29, 2016 at 18:36
Does the message column '_id' does not exist mean anything? — OneCricketeer
– OneCricketeer, Commented Mar 29, 2016 at 18:48

OneCricketeer · Accepted Answer · 2016-03-29 18:52:05Z

3

Just read the documentation for CursorAdapter

The Cursor must include a column named "_id" or this class will not work.

Basically, just rename ID to _id everywhere you use it.

answered Mar 29, 2016 at 18:52

OneCricketeer

193k20 gold badges146 silver badges276 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OneCricketeer Over a year ago

@NikKober You are welcome to provide your own answer.

Shadab Ansari · Accepted Answer · 2016-03-29 18:53:55Z

1

The problem is that you don't have a column "_id" in your table tbl_student

To use CursorAdapter, you must have "_id" column in your table. Change -

@Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

        String query= "CREATE TABLE tbl_student ( "+
                " _id            INT            PRIMARY KEY"+
                " UNIQUE,"+
                " Name          VARCHAR( 50 ),"+
                " Family        VARCHAR( 50 ),"+
                " Age           INT,"+
                " StudentNumber VARCHAR( 11 ),"+
                " Tel           VARCHAR( 15 )"+ 
                " );";
        db.execSQL(query);

    }

Read this.

answered Mar 29, 2016 at 18:53

Shadab Ansari

7,0902 gold badges30 silver badges46 bronze badges

Comments

Nik Kober · Accepted Answer · 2016-03-30 06:58:09Z

Besides renaming of ID column to _id, you should rewrite your DatabaseManager methods:

public boolean addStudent(Student student) {    
    boolean result; 
    try {   
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();  
        contentValues.put("Name", student.Name);
        contentValues.put("Family", student.Family);    
        // etc  
        db.insert("tbl_Student", null, contentValues);  
        db.close(); 
        result = true;  
    } catch (Exception e) { 
        result = false;
    }       
    return result;  
}



public Cursor getStudent(int id) {  
    SQLiteDatabase db = this.getReadableDatabase(); 
    return db.query("tbl_Student", null, "_id = ?", new String[]{String.valueOf(id)}, null, null, null);
}

You can find the useful guide here

Ispam · Accepted Answer · 2017-02-03 15:49:19Z

1

Late to the party, but heres ur problem:

public Cursor getStudent (int id)
{

    String query ="SELECT Name,Family FROM tbl_Student";
    SQLiteDatabase db=this.getReadableDatabase();
    Cursor cursor =db.rawQuery(query, null);

    return cursor;
}

CHANGE TO:

public Cursor getStudent (int id)
{

    String query ="SELECT _id, Name, Family FROM tbl_Student";
    SQLiteDatabase db=this.getReadableDatabase();
    Cursor cursor =db.rawQuery(query, null);
    return cursor;
}

answered Feb 3, 2017 at 15:49

Ispam

6159 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to Create ListView From SQLite Database using cursor adapter

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related